View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Hello,
May be for you, but NOT for anybody over here. If you are not familiar with writing sqls, search the forum for working examples, read the manuals, try something yourself and still if you are stuck up somewhere, post the error messages/output here. Somebody might be able to help. |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
Urgent
for you or for us. |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
did you give a try. |
|
Back to top |
|
|
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
|
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
The transaction for the above record is 10. So according to your above query that will be picked. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
If you are planning to write a COBOL-DB2 code then your requirement can be done. |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
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' |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
arvind.m wrote: |
... write a COBOL-DB2 code |
COBOL-DB2 code is water cooler language.
technically correct - imbedded SQL in COBOL program.
arvind.m wrote: |
...then your requirement can be done. |
actually, any facility that processes SQL.
QMF, SPUFI, imbedded SQL in COBOL/REXX/PL1/JAVA, DSNTIAUL to name a few. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Sunaina Javali,
This should be more efficient.
Code: |
SELECT POLICY FROM YOUR_TAB A
WHERE TRANSACTION = 10
AND EXISTS (SELECT 1 FROM YOUR_TAB B
WHERE A.POLICY = B.POLICY
AND TRANSACTION = 20) |
|
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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... |
|
Back to top |
|
|
Sunaina Javali
New User
Joined: 22 Apr 2009 Posts: 16 Location: Bangalore
|
|
|
|
yes it is needed for program. |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Quote: |
I have suggestion for you please change your way of representing your response in forums like this |
I dont really need a suggestion from you. If you had the least knowledge of representing "responses", you would n't have responded in this manner.
If something is really urgent for you, you should do it quickly, not anybody else here. And please do understand the fact that nobody is getting paid here to do the work for YOU. |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
Then why don't you fetch data into an array. How many app. records are in the database. |
|
Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008 Posts: 205 Location: Hyderabad
|
|
|
|
good answer Arun. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello Nisha,
Quote: |
it looks like that u r answered me in a unproffessional way. noone will expect these type of reults in any forum. |
Sorry to intrude, but what in the world are you talking about? What was posted that you found "unprofessional" |
|
Back to top |
|
|
|