>>On 6/22/2008 at 6:58 PM, in message
<6c************ *@mid.individua l.net>,
Serge Rielau<sr*****@ ca.ibm.comwrote :
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_MODIF IER is either 'identityoverri de' or '' and
ROWCHANGETIMES TAMP_MODIFIER is either 'rowchangetimes tampoverride' or
''?
>>
select
case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and identity = 'Y'
) then 'identityoverri de'
else ''
end as identity_modifi er
, case
when exists (
select *
from syscat.columns
where tabschema = 'FRANK'
and tabname = 'INVOICE'
and rowchangetimest amp = 'Y'
) then 'rowchangetimes tampoverride'
else ''
end as rowchangetimest amp_modifier
from sysibm.sysdummy 1;
Results:
IDENTITY_MODIF IER ROWCHANGETIMEST AMP_MODIFIER
----------------- ---------------------------
identityoverri de rowchangetimest ampoverride
1 record(s) selected.
You need to select straight from syscat.columns and then use GROUP BY to
pivot the two rows into columns.
Thanks, Serge! You made me work a bit <g>, but here's what I now have
(placed into a VIEW):
create view load_modifiers
as
select tabschema
, tabname
, max(case when identity = 'Y' then 'identityoverri de' else '' end) as
identity_modifi er
, max(case when rowchangetimest amp = 'Y' then
'rowchangetimes tampoverride' else '' end) as rowchangetimest amp_modifier
from syscat.columns
group by tabschema, tabname
;
select identity_modifi er
, rowchangetimest amp_modifier
from load_modifiers
where tabschema = 'CUSTOMER'
and tabname = 'ACCOUNTS'
;
IDENTITY_MODIFI ER ROWCHANGETIMEST AMP_MODIFIER
----------------- ---------------------------
identityoverrid e rowchangetimest ampoverride
1 record(s) selected.
Also gets good results when table does not include one or the other (or
both) types of columns.
Good stuff!
Frank