informix equalivalent for 'decode'statement in oracle

[Pavan_Yelugula]

hi all

we have this requirement where if a countrycode(parameter) is specified we should get the data from the table where the countrycode column in the table matches the parameter value. if nothing is specified all the data for all the countries should be retrieved

we found this out and rote a query in my ODBC stage accordingly

select * from COMET_TEST.REFERENCETABLE where SRC_CNTRY_CD =(DECODE('#CountryCode#','',SRC_CNTRY_CD,'#CountryCode#') )

This works great i am able to achieve my objective.
but some of my tables are in informix and the same function is not working can anyone tell me the equavalent for this in informix

any inputs will be really helpful
Thanks and regards
Pavan

[Sainath.Srinivasan]

I believe version 7.3 onwards of Informix Dynamic Server provides Decode function.

You can check it and its syntax on 'Informix Online'.

[Pavan_Yelugula]

i m using 7.1 can you please tell me if there is any round about way of getting the functionality

[Sainath.Srinivasan]

Use an OR clause as below

Select *
From Country
Where ('#CountryParameter#' = '')
Or (ContryCode = '#CountryParameter#')

[Pavan_Yelugula]

Thanks a lot it was a good idea

it is working great

[ray.wurlod]

DECODE can have more than two options.

Most databases' SQL has a CASE construct that you can use if there is no DECODE available.

Code: Select all

SELECT CASE WHEN '#CountryParameter#' = '' THEN SRC_CNTRY_CD
            WHEN '#CountryParameter#' IS NULL THEN SRC_CNTRY_CD
            ELSE '#CountryParameter#' 
       END AS CNTRY_CODE
FROM #SchemaName#.REFERENCETABLE ;