Use a subselect. In that subselect instead of attaching text, attach an
ordinal number. In the parent select, select max(that_number) along with
the tablespace name.

j.

-----Original Message-----
From: Park James Y.
[mailto:db2udbdba-ezmlmshield-x67939527.[Email address protected]
Sent: Tuesday, August 29, 2006 12:27 PM
To: LazyDBA Discussion
Subject: Help for CASE statement



Dear listers.


When I run this SQL,

SELECT DISTINCT(DBNAME||TSNAME), ICTYPE, SUBSTR(DSNAME,1,4),
(CASE ICTYPE
WHEN 'F' THEN 'FULL BACKUP'
WHEN 'Y' THEN 'LOAD LOG(NO)'
WHEN 'Z' THEN 'LOAD LOG(YES)'
WHEN 'I' THEN 'COPY FULL NO'
WHEN 'R' THEN 'LOAD REPLACE LOG(YES)'
WHEN 'S' THEN 'LOAD REPLACE LOG(NO)'
ELSE 'OTHERS'
END)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'MIRP'
ORDER BY ICTYPE;

I get tablespace names with F TYPE and then same tablespace names with Y
and/or Z and other types as you see below.

MIRP AIQO F bkp.
MIRP AIQO F bar.
MIRP AIQO Z


My question is how can I make a tablespace name appear with only 'F'
type when the same tablespace has other type? On the above query result
I don't want to see the last line that says, 'MIRP AIQO Z'. How
should I modify this statement?


Thanks

James


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page