Prev: DB2 9 runtime client install
Next: SQL to find if table has identity and/or row change timestampcolumns
From: Frank Swarbrick on 22 Jun 2008 20:50
How can I fix this so that it doesn't do essentially the same scan
twice, but it gives the same results (a single row where
IDENTITY_MODIFIER is either 'identityoverride' or '' and
ROWCHANGETIMESTAMP_MODIFIER is either 'rowchangetimestampoverride' or ''?

select
case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and identity = 'Y'
) then 'identityoverride'
else ''
end as identity_modifier
, case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and rowchangetimestamp = 'Y'
) then 'rowchangetimestampoverride'
else ''
end as rowchangetimestamp_modifier
from sysibm.sysdummy1;

Results:

IDENTITY_MODIFIER ROWCHANGETIMESTAMP_MODIFIER
----------------- ---------------------------
identityoverride rowchangetimestampoverride

1 record(s) selected.
 | 
Pages: 1
Prev: DB2 9 runtime client install
Next: SQL to find if table has identity and/or row change timestampcolumns