Re: Case in Select Statement

Hi, thanks for answer, I have a CASE statement, becuase I need to check lengt of field5. In case of situation when field5 is shorter then 3 characters I have to get all string, but in onother case I have to cut from then only 2 characters. Best regards -- Janek Simmons, Keith napisał(a): > Janek > > Why bother with the case ? > > SELECT field1, field2, field3, field4, field5[1,3] > from table > where field1=22 AND field2 IS NULL > ORDER BY field2> > should run fine. > > Keith > > -> -----Original Message----- > -> From: JPS [mailto:j_p_s@poczta.onet.pl] > -> Sent: Monday, August 21, 2006 10:22 AM > -> To: informix-list@iiug.org > -> Subject: Case in Select Statement > -> > -> > -> Hi, > -> > -> I have SQL: > -> > -> In original application we have simple SQL: > -> > -> SELECT field1, field2, field3, field4, field5 > -> from table > -> where field1=22 AND field2 IS NULL > -> ORDER BY field2 > -> > -> Unfortunately, I have to change returned value of field5 in > -> this query. > -> I put CASE statement to do this, and got SQL: > -> > -> SELECT field1, field2, field3, field4, > -> case > -> when char_length(field5) > 3 then substr(field5,0,2) > -> else field5 > -> end case > -> > -> FROM table1 > -> > -> WHERE > -> field1=22 > -> AND field2 IS NULL > -> > -> ORDER BY field2 > -> > -> > -> In dbaccess this query works fine, but in application I've > -> got error, > -> that in this query field5 is not declared. > -> > -> I try to use "as name" statement after "case", but dbaccess return > -> syntax error. > -> > -> Question is how to name "case" statement id this query as "field5" ? > -> > -> Best regards > -> > -> > -> -- > -> Janek > -> _______________________________________________ > -> Informix-list mailing list > -> Informix-list@iiug.org > -> http://www.iiug.org/mailman/listinfo/informix-list > -> > > *********************************************************************************************** > This email, including any attachment, is confidential and may be legally privileged. If you are not the intended recipient or if you have received this email in error, please inform the sender immediately by reply and delete all copies from your system. Do not retain, copy, disclose, distribute or otherwise use any of its contents. > > Whilst we have taken reasonable precautions to ensure that this email has been swept for computer viruses, we cannot guarantee that this email does not contain such material and we therefore advise you to carry out your own virus checks. We do not accept liability for any damage or losses sustained as a result of such material. > > Please note that incoming and outgoing email communications passing through our IT systems may be monitored and/or intercepted by us solely to determine whether the content is business related and compliant with company standards. > ***********************************************************************************************