Prev: SQL to find if table has identity and/or row change timestamp columns
Next: SQL to find if table has identity and/or row changetimestampcolumns
From: Serge Rielau on 22 Jun 2008 20:58
Frank Swarbrick wrote:
> 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.
You need to select straight from syscat.columns and then use GROUP BY to
pivot the two rows into columns.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 | 
Pages: 1
Prev: SQL to find if table has identity and/or row change timestamp columns
Next: SQL to find if table has identity and/or row changetimestampcolumns