Re: Case in Select Statement

Try SQL Select .... case .. end case ... . END SQL Vasek Mike Aubury <iiug@aubit.com> Odeslal: informix-list-bounces@iiug.org 21.08.2006 11:38 Komu JPS <j_p_s@poczta.onet.pl> Kopie informix-list@iiug.org Předmět Re: Case in Select Statement I'm guessing that by 'but in application' - you mean in 4gl... 4GL doesn't accept all InformixSQL syntax (particularly if the versions differ) - the normal work around is to PREPARE the Sql first.. LET lv_str="select...." PREPARE p_somename FROM lv_str DECLARE c1 cursor FOR p_somename FOREACH c1 into ... That way - 4gl never really sees the SQL - it just passes it to the engine to be processed... On 21 Aug 2006, at 10:22, JPS wrote: > 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 > _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list