Arthur Fuller
artful at rogers.com
Fri Feb 7 17:12:46 CST 2003
Look up CASE in Books on Line (BOL). You can rewrite any IIF() statement using this construct. -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com] On Behalf Of Jim Lawrence (AccessD) Sent: February 7, 2003 5:56 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: Re: [dba-SQLServer]RE: Simple SQL question? To All: Thank you for your help. :-) I attempted to apply some of the suggests but SQL seemed to dislike iif --- <function not found>??? :-\ I will be working on the problem at the client's site come Monday...so if anyone has any further insights it would be greatly appreciated. |-) TIA Jim -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Geoff Lovis Sent: Thursday, February 06, 2003 1:06 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: Re: [dba-SQLServer]RE: Simple SQL question? 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 > _______________________________________________ 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