SQL Question (re-sent)...

Charles Valentin

SQL Question (re-sent)...
> Dear Listserv Group, 12/30/04

> Any recommendations for my friendly DBA?
> Thank You & Happy New Year To All !!!
> Charles
> -----Original Message-----
> From: Polovsky, Richard
> Sent: Tuesday, December 28, 2004 1:54 PM
> Subject: SQL Question
> Charles,
>
> This is what I'm trying to do.
> The single select will be created as a view
> I can't do this with cursor processing.
> Let me know if you have any ideas
>
> Thanks
> Richard D. Polovsky
> ----------------------------------------------------------------------
> table |------KEY----------|
>
> mkt-d pri_plan DATA
>
> RID
> 1 RNY PPLANA XXXXXXXXXXXXXXX
> 2 CPT PPLANA XXXXXXXXXXXXXXX
>
> 3 CPT PPLANB XXXXXXXXXXXXXXX
>
> 4 RNY PPLANC XXXXXXXXXXXXXXX
> 5 RBS PPLANC XXXXXXXXXXXXXXX
>
> 6 RNY PPLAND XXXXXXXXXXXXXXX
>
> 7 RBS PPLAND XXXXXXXXXXXXXXX
> 8 RCT PPLAND XXXXXXXXXXXXXXX
> 9 CPT PPLAND XXXXXXXXXXXXXXX
>
> In a singleton select from
>
> CASE1
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANA'
>
> RETURN RNY PLANA XXXXXXXXXXXXXXXXX
>
> CASE2
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANB'
>
> RETURN CPT PLANB XXXXXXXXXXXXXXXXX
>
> CASE3
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANC'
>
> RETURN RNY PLANC
>
> CASE4
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLAND'
>
> RETURN RNY PLAND
> --------------------------------------------------------------------------
> ------
>


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Steen

Re: SQL Question (re-sent)...
(in response to Charles Valentin)
on UDB, it's simple, so I'm guessing your talking Z/OS

SELECT
CASE
WHEN (mkt-id = 'CPT' or mkt-id ='RNY') and pri-plan = 'PLANA'
THEN 'RNY'
WHEN (mkt-id = 'CPT' or mkt-id ='RNY') and pri-plan = 'PLANB'
THEN 'CPT'
WHEN (mkt-id = 'CPT' or mkt-id ='RNY') and pri-plan = 'PLANC'
THEN 'RNY'
WHEN (mkt-id = 'CPT' or mkt-id ='RNY') and pri-plan = 'PLANC'
THEN 'RNY'
ELSE mkt-id
END CASE,
pri_plan

from table


----------------------------------

Mark Steen

World Savings

EIM - Data Warehouse

210-543-6697



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Charles Valentine
Sent: Thursday, December 30, 2004 11:05 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Question (re-sent)...



> Dear Listserv Group, 12/30/04

> Any recommendations for my friendly DBA?
> Thank You & Happy New Year To All !!!
> Charles
> -----Original Message-----
> From: Polovsky, Richard
> Sent: Tuesday, December 28, 2004 1:54 PM
> Subject: SQL Question
> Charles,
>
> This is what I'm trying to do.
> The single select will be created as a view
> I can't do this with cursor processing.
> Let me know if you have any ideas
>
> Thanks
> Richard D. Polovsky
>
----------------------------------------------------------------------
> table |------KEY----------|
>
> mkt-d pri_plan DATA
>
> RID
> 1 RNY PPLANA XXXXXXXXXXXXXXX
> 2 CPT PPLANA XXXXXXXXXXXXXXX
>
> 3 CPT PPLANB XXXXXXXXXXXXXXX
>
> 4 RNY PPLANC XXXXXXXXXXXXXXX
> 5 RBS PPLANC XXXXXXXXXXXXXXX
>
> 6 RNY PPLAND XXXXXXXXXXXXXXX
>
> 7 RBS PPLAND XXXXXXXXXXXXXXX
> 8 RCT PPLAND XXXXXXXXXXXXXXX
> 9 CPT PPLAND XXXXXXXXXXXXXXX
>
> In a singleton select from
>
> CASE1
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANA'
>
> RETURN RNY PLANA XXXXXXXXXXXXXXXXX
>
> CASE2
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANB'
>
> RETURN CPT PLANB XXXXXXXXXXXXXXXXX
>
> CASE3
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLANC'
>
> RETURN RNY PLANC
>
> CASE4
> where mkt-id in ('CPT','RNY')
> and pri-plan = 'PLAND'
>
> RETURN RNY PLAND
>
------------------------------------------------------------------------
--
> ------
>


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



*****************************************************************************
If you are not the intended recipient of this e-mail, please notify
the sender immediately. The contents of this e-mail do not amend
any existing disclosures or agreements unless expressly stated.
*****************************************************************************


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm