Need DB2 query to solve this

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

Hi,

Any one can help to write DB2 query for the above query.
it's urgent

DB2 Table has 3 cols

POLICY | AMT | TRANSACTION
12345 100 10
12345 200 20
12345 200 30
23456 100 10
23456 200 20
23456 300 50
34567 100 70
34567 200 80
45678 100 10

Policy is any 5 digit number, amount is $ amt, transaction is 2 digit number.

I need to get all the policies that have transactions 10 and 20. As per the above table, the policies 12345 and 23456 are the ones that have 10 and 20 in transaction colm.

Please write a DB2 query for this.

Thanks,
Nisha

Arun Raj · Posted: Thu Jun 04, 2009 11:52 am

arvind.m · Posted: Thu Jun 04, 2009 11:53 am

Urgent

for you or for us.

arvind.m · Posted: Thu Jun 04, 2009 11:54 am

did you give a try.

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

Yes...

i tried with below query

select policy from A
where transaction in (10,20)

o/p :
12345 100 10
12345 200 20

23456 100 10
23456 200 20

45678 100 10

but in o/p : 45678 100 10 should not picked up

arvind.m · Posted: Thu Jun 04, 2009 12:13 pm

enrico-sorichetti · Posted: Thu Jun 04, 2009 12:14 pm

maybe it would have been better to reword along the line

policies that have both rows with a 10 and a 20 in the transaction column

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

my requirement is this:

I need to get all the policies that have transactions 10 and 20. As per the above table, the policies 12345 and 23456 are the ones that have 10 and 20 in transaction colm.

10 and 20 not 10 or 20. here there no 20 for the below result:
45678 100 10

thanks,
Nisha

arvind.m · Posted: Thu Jun 04, 2009 12:31 pm

If you are planning to write a COBOL-DB2 code then your requirement can be done.

Srihari Gonugunta · Posted: Thu Jun 04, 2009 1:49 pm

Hi Sunaina,
Please try the following query

SELECT A.POLICY
FROM TAB A INNER JOIN TAB B
ON A.POLICY = B.POLICY
WHERE A.TRANSACTION = '10'
AND B.TRANSACTION = '20'

dbzTHEdinosauer · Posted: Thu Jun 04, 2009 2:02 pm

Arun Raj · Posted: Thu Jun 04, 2009 2:02 pm

Sunaina Javali,

This should be more efficient.

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

Mr Arun

it looks like that u r answered me in a unproffessional way. noone will expect these type of reults in any forum.
I have suggestion for you please change your way of representing your response in forums like this.

thanks,
Nisha

GlobalGyan · New User Joined: 31 Jan 2006 Posts: 28

I think the query is answered - Is this needed for a program, or homework or reporting? or interview question?
Just curious...

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

yes it is needed for program.

Sunaina Javali · New User Joined: 22 Apr 2009 Posts: 16 Location: Bangalore

Thank you all for your valuable answers.
also thanks for your time .

Thanks,
Nisha

Arun Raj · Posted: Thu Jun 04, 2009 2:37 pm

arvind.m · Posted: Thu Jun 04, 2009 2:58 pm

Then why don't you fetch data into an array. How many app. records are in the database.

arvind.m · Posted: Thu Jun 04, 2009 3:03 pm

good answer Arun.

dick scherrer · Posted: Thu Jun 04, 2009 10:26 pm

Hello Nisha,