urgent!!!!!!!!!!! help required in db2 sql

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lakshmananl - 14 Mar 2006 11:05 GMT

hi people i am facing a problem, when i am converting SQL server sql to
db2 v7.2 SQL the existing sql server command is

SELECT
isnull(u.NAME, 'NO USERS') USERNAME,
isnull(u.kanauserid, -1) USERID,
f.LABEL FOLDERLABEL,
f.FOLDERID,
f.isretired,
isnull(uf.PRIORITY, 0) PRIORITY
FROM
kc_folder f

the above is for SQL server i need the equivalent command in db2 for
report generation,
i am facing problem when i use isnull(u.NAME, 'NO USERS') USERNAME
i tried with case i am getting syntax error, kindly help me.

regards
lakshmanan

Reply to this Message

Gert van der Kooij - 14 Mar 2006 11:16 GMT

> i am facing problem when i use isnull(u.NAME, 'NO USERS') USERNAME
> i tried with case i am getting syntax error, kindly help me.

Use the coalesce function: coalesce(u.NAME, 'NO USERS')

Reply to this Message

bikkaran@in.ibm.com - 14 Mar 2006 11:35 GMT

use COALESCE inplace of nulll

SELECT
SELECT
COALESCE(u.NAME, 'NO USERS') as USERNAME,
COALESCE(u.kanauserid, -1) as USERID,
f.LABEL FOLDERLABEL,
f.FOLDERID,
f.isretired,
COALESCE(uf.PRIORITY, 0) as PRIORITY
FROM
kc_folder f

Reply to this Message

lakshmananl - 14 Mar 2006 13:43 GMT

hi thank you all for your timely help.

lakshmanan

Reply to this Message

Serge Rielau - 14 Mar 2006 13:52 GMT

> hi thank you all for your timely help.
>
> lakshmanan

Follow-up. You are migrating SQL Server to DB2 V7.2.
Are you aware that DB2 V7.2 is out of support.
Also DB2 V8.2 has a ton of functions which help in SQL Server migrations.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dave Hughes - 14 Mar 2006 15:44 GMT

> use COALESCE inplace of nulll
>
[quoted text clipped - 8 lines]
> FROM
> kc_folder f

Some extra info:

COALESCE is also available in MS SQL Server 2005 (and maybe prior
versions?). However, COALESCE is not *exactly* the same as ISNULL on MS
SQL Server. I've just had a quick read of the docs and it appears
ISNULL only accepts *two* arguments (original value, replacement
value), while the COALESCE function accepts two or more arguments (as
it does on DB2).

Hence:

COALESCE(value1, value2, value3)

Is equivalent to:

CASE WHEN value1 IS NULL THEN
CASE WHEN value2 IS NULL THEN
value3
END
END

Extend the above as necessary for the number of arguments.

Incidentally, COALESCE is also supported (with two or more arguments)
on Oracle, MySQL, PostgreSQL, Firebird/Interbase, etc. I seem to recall
it's one of the functions mandated by the SQL standard (along with it's
arbitrary number of arguments) which might explain why it's so widely
implemented. In summary, you're better off sticking with COALESCE as
it'll make porting your SQL between platforms a bit easier.

HTH,

Dave.

P.S. VALUE is a synonym for COALESCE in DB2 (just in case you ever run
across it), and NVL on Oracle is like ISNULL on SQL Server. However,
for the reasons given above I'd recommend always sticking with COALESCE.

Reply to this Message