Susan Harkins
harkins at iglou.com
Thu Feb 6 11:06:41 CST 2003
Playing with it a bit -- apparently you can't use a Bit data type in a Sum
aggregate? I didn't know that.
Susan H.
...Hmmmm...very daring solution...but I don't believe it will fly :)
______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
? (210) 753-3148 - pager
-----Original Message-----
From: fernando.pires at gustavsberg.com [mailto:fernando.pires at gustavsberg.com]
Sent: Thursday, February 06, 2003 9:58 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Ang: Re: [dba-SQLServer]RE: Simple SQL question?
select iif(sum(active) > 0, true,false) from table where active = true
Fernando Pires IT dep.
Tel. + 46 857039367
Mob. + 46 702795858
"Susan Harkins"
<harkins at iglou.com> Till:
<dba-sqlserver at databaseadvisors.com>
Sänt av: Kopia:
dba-sqlserver-admin at databasea Ärende: Re:
[dba-SQLServer]RE: Simple SQL question?
dvisors.com
2003-02-06 16:53
Sänd svar till dba-sqlserver
Which returns true -- the subquery -- you want the subquery to return just
one true? I don't think you're going to get what you need with a simple
SELECT or subquery.
Susan H.
> Hi Suzan:
>
> There may be a number of records, all may be inactive, some may be
active,
> all may be active but if a minimum of one record is active it needs to
> return a TRUE otherwise FALSE... I am obviously missing something.
>
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-admin at databaseadvisors.com
> [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Susan
> Harkins
> Sent: Thursday, February 06, 2003 6:59 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer]RE: Simple SQL question?
>
>
> Well, off the top of my head, can't you just return the records Boolean
> field instead of the employee number and last name field?
>
> Susan H.
>
> > Hi All:
> >
> > My mind has just gone blank.
> >
> > I want to check a table for all records for a specific
'EmployeeNumber'.
> > There might be one record and there might be a many as 10 records, in
this
> > table.
> > All the records have a boolean flag that marks them as either being
Acive
> or
> > Inactive.
> >
> > I would like to return ONE value of TRUE if any records are active or
> > FALSE...
> > and this process would be a subquery of another query.
> >
> > Example:
> >
> > select distinct e.EmployeeNumber as [Employee Number], e.Surname as
> > [Lastname],
> > Status = (select count(e2.active)as [Status Count] from
> EmployeeTransaction
> > as e2 where Active = TRUE and e2.EmployeeNumber = e.EmployeeNumber)
> > from EmployeeTransaction as e
> >
> > The above example doesn't work of course, because a number is returned
but
> > if the imbedded subquery would return ONE result of TRUE or FALSE, it
> would.
> > (In the example above a value of greater than one would be TRUE result
and
> > zero would be FALSE result.) I am sure it is simple but at four in the
> > morning, here, it is not.
> >
> > TIA
> > Jim
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> >
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com