CASE clause statement in DB2

0
votes

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

2
db2case
Did we get you right, that you want to return all records in the table OR just one artificial row, if there are no rows in the table? - Mark Barinstein
@Mark Barinstein : I need to retrieve one artificial row(hard coded ) if there are no rows in the table. - Chammu
The question was about as well, what you want to return, if there are rows in the table. All rows? A number of rows, which satisfy some (which one?) condition? - Mark Barinstein

2 Answers

2
votes

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;
2
votes

There are various ways you could achieve what (I think) you want. This is one

SELECT
    COALESCE(ID,1) AS ID
,   COALESCE(COMPANY,'COMP1') AS COMPANY
FROM
    TABLE(VALUES 1) AS DUMMY(D)
LEFT JOIN
    PROCESS
ON
    1=1