CASE clause statement in DB2

Question

I have a table PROCESS . Currently it doesnt not have any records in it. I need to return one hardcoded row if the table doesnt have any record . I am doing a select when the primary key column "id" is null then i hard code the values and return it as below

SELECT CASE WHEN p.ID IS NULL THEN 1 ELSE p.ID END ,
CASE WHEN p.COMPANY IS NULL THEN 'COMP1' ELSE p.COMPANY END
FROM PROCESS p

I took reference from the below link If-else statement in DB2/400

But it always returns me an empty row in DB2 database and not the hardcoded values used in select statement. 08:50:27 SUCCESS SELECT 0.307 0.301 0 Empty result set fetched 08:50:29 FINISHED 0.307 0.301 0 Success: 1 Failed: 0

Please help me on this

question from:https://stackoverflow.com/questions/65912496/case-clause-statement-in-db2

Answer 1

no way to do in this way, since a primary key could never be null. and select * from empty table return no row (0 row) it do not return null.

you can do it like that:

select ID, COMPANY from PROCESS
UNION ALL
select 1 as ID, 'COMP1' as COMPANY from sysibm.sysdummy1 where (select count(*) from PROCESS) = 0;