DECODE in DB2 8.1?

[Mikeland]

I come from the oracle world and would use the Oracle 'DECODE'
function
extensively. Is there any equivalent in db2???

using db2 version 8.1 on win

SELECT COUNT(*) INTO VAR_COUNT_STREE T
FROM TB_TOWN_STREET
WHERE upper(trim(STRE ETNAME)) = upper(trim(STRE ET))
AND upper(trim(TOWN _NAME)) = upper(trim(TOWN ))
AND DECODE (trim(IN_STREET _TYPE),'','1',t rim(IN_STREET_T YPE)) = DECODE
(trim(IN_STREET _TYPE),'','1', trim(STREETTYPE ));

Please, write this oracle script in Db2 language

Thanks.

[Kay Kanekowski]

Hi,
coming from oracle too.
That's my try:
SELECT COUNT(*) INTO VAR_COUNT_STREE T
FROM TB_TOWN_STREET
WHERE upper(trim(STRE ETNAME)) = upper(trim(STRE ET))
AND upper(trim(TOWN _NAME)) = upper(trim(TOWN ))
AND ( CASE ( when trim(IN_STREET_ TYPE) = '' then '1'
else trim(IN_STREET_ TYPE)) ) -- <<< IN_ ??
= ( CASE when trim(IN_STREET_ TYPE) = '' then '1'
else trim(STREETTYPE )));
hth
Kay

[Serge Rielau]

Kay Kanekowski wrote:

Hi,
coming from oracle too.
That's my try:
SELECT COUNT(*) INTO VAR_COUNT_STREE T
FROM TB_TOWN_STREET
WHERE upper(trim(STRE ETNAME)) = upper(trim(STRE ET))
AND upper(trim(TOWN _NAME)) = upper(trim(TOWN ))
AND ( CASE ( when trim(IN_STREET_ TYPE) = '' then '1'
else trim(IN_STREET_ TYPE)) ) -- <<< IN_ ??
= ( CASE when trim(IN_STREET_ TYPE) = '' then '1'
else trim(STREETTYPE )));

In DECODE NULL = NULL. Since in Oracle '' IS NULL as well, I think you
may need:
COALECSE(trim(I N_STREET_TYPE), '') = ''
... if IN_STREET_TYPE can be NULL (or '' in Oracle).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab