How to decode the default '-' using select case

[choongyin]

Hi there,

By default, a column will be inserted a '-' by the DB2. Say, my table (TEST) data as below (both columns are CHAR):

FNAME LNAME
----- -----
- lname1

where '-' automatically inserted by DB2 when I executed the insert statement: INSERT INTO TEST (LNAME) VALUES ('lname1').

When I tried to do this: SELECT CASE WHEN FNAME='' THEN 'fname1' WHEN FNAME='-' THEN 'fname1' ELSE FNAME END FROM TEST, the result is '-'. What can I do in order to get the correct result and not '-'?

 

[grofaty]

Hi,

What would you like to get at result?

Grofaty

 

[swampBoogie]

It's null not - I suppose.

select case when fname is null or fname = '' then 'fname1'
else fname end from test

 

[MarcLodge]

Hi,
It looks as if FNAME has been defined as being able to contain NULL values. The '-' that you are seeing is a NULL. If you wish to select the field then use the format WHERE FNAME IS NULL or if not WHERE FNAME IS NOT NULL. To cater for the value being present or not, you should specify a small int field (a null indicator) in the the select. A value of -1 in this field indicates a NULL value is present and a value of 0 in this indicates a value is present.

Hope this helps

Marc

 

[ghera]

Hi,

Definately this column is null. you can use
select COALESCE(column_name, value_if_null) from...

for example

SELECT COALESCE(FNAME, 'nonamegiven') FROM ....

 

[choongyin]

Hi there,

Thanks for the help.
Yup, it is a null and not '-'.

Thanks & rgds,
Celine