Case/When in SQL

Hi all, I happened to see this beautiful SQL statement and executed with my IDS10 and got expected results (don't need to use temp tables as before :) ). My request is where can I find online documentation for those sql enhancements? My appreciation, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au ================================================================================ = "CURTIS CROWSON" <curtis@crowson1. To: ids@iiug.org com> cc: Sent by: Subject: Re: help with correlated subquery [11726] ids-bounces@iiug. org 28/03/2008 06:04 AM Please respond to ids This query I don't think is right based on your initial requirement that both values have to be not null. Let me give you an example: create table test1 (col1 int, col2 char(4), amount int); insert into test1 values (1,'dept',1); insert into test1 values (1,'dept',2); insert into test1 values (1,'dept',3); insert into test1 values (1,'check',0); insert into test1 values (1,'check',0); insert into test1 values (1,'check',0); insert into test1 values (1,'check',0); insert into test1 values (1,'with',-1); insert into test1 values (1,'with',-2); insert into test1 values (1,'with',-3); -- Your query returned -18 and 18 where it should return -6 and 6. So I changed the query a little and actually was able to get it without a join. SELECT t.col1, t.col2, sum(Case when t.amount < 0 then t.amount else 0 END ) neg_sum, sum(Case when t.amount > 0 then t.amount else 0 END) pos_sum FROM test1 t WHERE t.amount <> 0 and col2="dept" group by 1,2 ; Now you shouldn't get any records if I understood your requirements because there are no negative dept's but with your query you will get: 1 dept 0 6 The simplest way to do this with nine is to use my favorite whipping boy views. If you have to do it inline then so be it but I would just create a view: It doesn't violate your requirements because you only do it on the server side one time and then use it on the reporting side. And if you are using a limited capability reporting tool like this views can open up a world of possibilities that you aren't able to imagine today. create view my_sums( col1, col2, neg_sum,pos_sum ) as SELECT t.col1, t.col2, sum(Case when t.amount < 0 then t.amount END ) neg_sum, sum(Case when t.amount > 0 then t.amount END) pos_sum FROM test1 t WHERE t.amount <> 0 group by 1,2 ; select * from my_sums where col2 = "dept" and neg_sum is not null and pos_sum is not null ; will give you the appropriate result which is empty because we didn't make any negative deposits. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.

Hi, OnLine documentation can be found in infocenter: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp Just search "case when" and you will find the complete useage with samples. Regards, Gerd > > Hi all, > I happened to see this beautiful SQL statement and executed with my IDS10 > and got expected results (don't need to use temp tables as before :) ). > My request is where can I find online documentation for those sql > enhancements? > My appreciation, > Long Huy Nguyen > MIS(Analyst Programmer) > Ruralco Limited > P.O.Box 515 > Wentworthville NSW 2145 > (Ph) 02 9688 8528 (Fax) 02 9896 7763 > lnguyen@ruralco.com.au > > ================================================================================ = > > "CURTIS CROWSON" > > <curtis@crowson1. To: ids@iiug.org > > com> cc: > > Sent by: Subject: Re: help with correlated subquery [11726] > > ids-bounces@iiug. > > org > > 28/03/2008 06:04 > > AM > > Please respond to > > ids > > This query I don't think is right based on your initial requirement that > both > values have to be not null. Let me give you an example: > > create table test1 (col1 int, col2 char(4), amount int); > insert into test1 values (1,'dept',1); > insert into test1 values (1,'dept',2); > insert into test1 values (1,'dept',3); > insert into test1 values (1,'check',0); > insert into test1 values (1,'check',0); > insert into test1 values (1,'check',0); > insert into test1 values (1,'check',0); > insert into test1 values (1,'with',-1); > insert into test1 values (1,'with',-2); > insert into test1 values (1,'with',-3);> > -- Your query returned -18 and 18 where it should return -6 and 6. So I > changed the query a little and actually was able to get it without a join. > SELECT > t.col1, > t.col2, > sum(Case when t.amount < 0 > > then t.amount else 0 > > END > ) neg_sum, > > sum(Case when t.amount > 0 > > then t.amount else 0 > > END) pos_sum > FROM > test1 t > WHERE > t.amount <> 0 and col2="dept" > group by > 1,2 > ; > > Now you shouldn't get any records if I understood your requirements because > > there are no negative dept's but with your query you will get: > > 1 dept 0 6 > > The simplest way to do this with nine is to use my favorite whipping boy > views. If you have to do it inline then so be it but I would just create a > view: > > It doesn't violate your requirements because you only do it on the server > side > one time and then use it on the reporting side. And if you are using a > limited > capability reporting tool like this views can open up a world of > possibilities > that you aren't able to imagine today. > > create view my_sums( > col1, > col2, > neg_sum,> pos_sum > ) as > SELECT > t.col1, > t.col2, > sum(Case when t.amount < 0 > > then t.amount > > END > ) neg_sum, > > sum(Case when t.amount > 0 > > then t.amount > > END) pos_sum > FROM > test1 t > WHERE > t.amount <> 0 > group by > 1,2 > ; > > select > * > from > my_sums > where > col2 = "dept" and neg_sum is not null and pos_sum is not null > ; > > will give you the appropriate result which is empty because we didn't make > any > negative deposits. > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > Disclaimer: > This correspondence is for the named person's use only. It may contain > confidential or legally privileged information or both. No confidentiality > or privilege is waived or lost by any mistransmission. If you receive this > correspondence in error, please immediately delete it together with any > attachments from your system and notify the sender. You must not disclose, > copy or rely on any part of this correspondence if you are not the intended > recipient. > Any opinions expressed in this message are those of the individual sender, > except where the sender expressly, and with authority, states them to be > the opinions of Ruralco Holdings Limited or any of its subsidiaries > (collectively "Ruralco"). > Although all care has been taken to screen this communication for viruses, > neither the sender nor Ruralco warrants that any communication via the > Internet is free of errors, viruses, interception or interference. > Information is distributed without warranties of any kind. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > _______________________________________________________________ Schon gehört? Der neue WEB.DE MultiMessenger kann`s mit allen: http://www.produkte.web.de/messenger/?did=3016