Please Help! Decode() in Informix??

Hi, I am currently working with an OLAP tool (BusinessObjects) to create a 'dynamic' SQL statement allowing to pick from a FISCAL_MONTH column (SMALLINT datatype) a value corresponding to the current calendar month. For my client, the fiscal year started in October 1998 (which has the value 19901 for FISCAL_MONTH) and ends in September of this year (FISCAL_MONTH equal to 199912). Because of the limitation of the OLAP software I have to setup this 'algorithm' in SQL: If Current Month (Current Date) Between 10 /*October*/ And 12 /*December*/ Then Fiscal Year = Current Year(Current Date) + 1 And Fiscal Month = Fiscal Year & "01" /*October*/ Or "02" /*November*/ Or "03" /*December*/ ElseIf Current Month(Current Date) Between 01 /*January*/ And 09 /*September*/ Then Fiscal Year = Current Year(Current Date) And Fiscal Month = Fiscal Year & "04" /*January*/ Or "05" /*February*/ Or "06" /*March*/ Or "07" /*April*/ Or "08" /*May*/ Or "09" /*June*/ Or "10" /*July*/ Or "11" /*August*/ Or "12" /*September*/ In Oracle, there is a function called Decode() which allows the simulation of an "If...Then...Else" statement. My knowledge of Informix is unfortunately limited and the SQL reference books (Informix 7.1) I got make no mention of such function. Does anyone here knows of a way to produce such a result without having to write code in SPL? Any help will GREATLY be appreciated. Thanks in advance!!! -- Alain C. Bonnemaison. (Remove NOSPAM to reply to me directly). --------------------------- Senior Project Manager Email: abonnemaison@NOSPAM.profoundsolutions.com Phone: 800-411-2755 Fax: 317-579-6279 http://www.profoundsolutions.com

With 7.30 we introduced both the case and DECODE function. You can issue either: select firstname, DECODE( evaluation, 'Poor', 0, 'Fair', 25, 'Good', 50, 'Very Good', 75, 'Great', 100, -1) as grade from xxxxxx. However, I personally prefer the case clause as that's more in common with most programming languages. Also it provides for a range of values. It would look like: select firstname, CASE WHEN number_of_problems = 0 THEN 100 WHEN number_of_problems > 0 AND number_of_problems < 25 THEN number_of_problems * 500 else number_of_problems * -1 END as problems from xxx Alain C. Bonnemaison wrote: > Hi, > > I am currently working with an OLAP tool (BusinessObjects) to create a > 'dynamic' SQL statement allowing to pick from a FISCAL_MONTH column > (SMALLINT datatype) a value corresponding to the current calendar month. > > For my client, the fiscal year started in October 1998 (which has the value > 19901 for FISCAL_MONTH) and ends in September of this year (FISCAL_MONTH > equal to 199912). Because of the limitation of the OLAP software I have to > setup this 'algorithm' in SQL: > > If Current Month (Current Date) Between 10 /*October*/ And 12 /*December*/ > Then > Fiscal Year = Current Year(Current Date) + 1 > And > Fiscal Month = Fiscal Year & "01" /*October*/ Or "02" /*November*/ Or "03" > /*December*/ > > ElseIf Current Month(Current Date) Between 01 /*January*/ And 09 > /*September*/ Then > Fiscal Year = Current Year(Current Date) > And > Fiscal Month = Fiscal Year & "04" /*January*/ Or "05" /*February*/ Or "06" > /*March*/ > Or "07" /*April*/ Or "08" /*May*/ > Or "09" /*June*/ Or "10" /*July*/ > Or "11" /*August*/ Or "12" /*September*/ > > In Oracle, there is a function called Decode() which allows the simulation > of an "If...Then...Else" statement. My knowledge of Informix is > unfortunately limited and the SQL reference books (Informix 7.1) I got make > no mention of such function. > > Does anyone here knows of a way to produce such a result without having to > write code in SPL? > > Any help will GREATLY be appreciated. Thanks in advance!!! > > -- > Alain C. Bonnemaison. (Remove NOSPAM to reply to me directly). > --------------------------- > Senior Project Manager > Email: abonnemaison@NOSPAM.profoundsolutions.com > Phone: 800-411-2755 > Fax: 317-579-6279 > http://www.profoundsolutions.com