Re: Please Help! Decode() in Informix??

Alain I hope you see this. I tried emailing the response as well, but it kept getting rejected. Madison Pruet wrote: > 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