SEO

#1 (**permalink**) 02-27-2008, 12:10 PM

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_STREET
FROM TB_TOWN_STREET
WHERE upper(trim(STREETNAME)) = upper(trim(STREET))
AND upper(trim(TOWN_NAME)) = upper(trim(TOWN))
AND DECODE (trim(IN_STREET_TYPE),'','1',trim(IN_STREET_TYPE)) = DECODE
(trim(IN_STREET_TYPE),'','1', trim(STREETTYPE));

Please, write this oracle script in Db2 language

Thanks.

#2 (**permalink**) 02-27-2008, 12:10 PM

Hi,
coming from oracle too.
That's my try:
SELECT COUNT(*) INTO VAR_COUNT_STREET
FROM TB_TOWN_STREET
WHERE upper(trim(STREETNAME)) = upper(trim(STREET))
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

#3 (**permalink**) 02-27-2008, 12:10 PM

Kay Kanekowski wrote:
> Hi,
> coming from oracle too.
> That's my try:
> SELECT COUNT(*) INTO VAR_COUNT_STREET
> FROM TB_TOWN_STREET
> WHERE upper(trim(STREETNAME)) = upper(trim(STREET))
> 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(IN_STREET_TYPE), '') = ''
... if IN_STREET_TYPE can be NULL (or '' in Oracle).

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

DECODE in DB2 8.1?