View previous topic :: View next topic
|
Author |
Message |
balajipradeep
New User
Joined: 12 Mar 2009 Posts: 21 Location: chennai
|
|
|
|
Hi,
I have to write one sql query to see the policy number and plan id available in the DB2 table.Each policy number has the separate plan id.
For example:
Policy plan
a 1
b 4
c 6
d 5
e 1
select policy,plan where policy,plan=(a,1)(?) -
i want the list of policy to be checked in the single query. |
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Hello There,
I am not sure whetehr I understand your query correctly but check below query
Code: |
SELECT POLICY,
PLAN
FROM TABLE
WHERE (POLICY= 'a' and PLAN= 1) OR
(POLICY= 'b' and PLAN= 4) OR
... |
|
|
Back to top |
|
|
balajipradeep
New User
Joined: 12 Mar 2009 Posts: 21 Location: chennai
|
|
|
|
Thanks. I also tried the same way. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
values ((a,1),(b,4)) isn't supported (yet) on z/OS
the simpliest way to do it is with the OR clauses like guptae said
for performance reasons you could try
Code: |
with input (pol,plan) as (
select 'a',1 from sysibm.sysdummy1
union all
select 'b',4 from sysibm.sysdummy1
union all
...
)
select a.pol, a.plan from table1 A, input
where (input.pol,input.plan) = (a.pol,a.plan) |
or the old fashioned way :
Code: |
select a.pol, a.plan from table1 A where pol = 'a' and plan =1
union all
select a.pol, a.plan from table1 A where pol = 'b' and plan =4
union all
... |
|
|
Back to top |
|
|
|