[dba-SQLServer]RE: Simple SQL question?

Geoff Lovis gl at exem-cs.co.uk
Thu Feb 6 15:06:16 CST 2003


I came across this problem last week and used the following:

Sum([Canx]*1)

where Canx is a bit field (Yes/No in Access).

Regards

Geoff
-----------
Geoff Lovis
Exem Computer Systems, Liss, Hants


From:           	"Susan Harkins" <harkins at iglou.com>
To:             	<dba-sqlserver at databaseadvisors.com>
Subject:        	Re: Re: [dba-SQLServer]RE: Simple SQL question?
Send reply to:  	dba-sqlserver at databaseadvisors.com
	<mailto:dba-sqlserver-
request at databaseadvisors.com?subject=subscribe>
	<mailto:dba-sqlserver-
request at databaseadvisors.com?subject=unsubscribe>
Date sent:      	Thu, 6 Feb 2003 12:06:41 -0500

> 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
> 
> 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 





More information about the dba-SQLServer mailing list