Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need DB2 query to solve this

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 10:58 am    Post subject: Need DB2 query to solve this
Reply with quote

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
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Thu Jun 04, 2009 11:52 am    Post subject:
Reply with quote

Quote:
it's urgent
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
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 11:53 am    Post subject:
Reply with quote

Urgent

for you or for us. icon_eek.gif icon_biggrin.gif icon_eek.gif
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 11:54 am    Post subject:
Reply with quote

did you give a try.
Back to top
View user's profile Send private message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 12:03 pm    Post subject:
Reply with quote

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

icon_sad.gif
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 12:13 pm    Post subject:
Reply with quote

Quote:

45678 100 10


The transaction for the above record is 10. So according to your above query that will be picked.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Jun 04, 2009 12:14 pm    Post subject: Reply to: Need DB2 query to solve this
Reply with quote

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
View user's profile Send private message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 12:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 12:31 pm    Post subject:
Reply with quote

If you are planning to write a COBOL-DB2 code then your requirement can be done.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Jun 04, 2009 1:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 04, 2009 2:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Thu Jun 04, 2009 2:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 2:05 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GlobalGyan

New User


Joined: 31 Jan 2006
Posts: 28

PostPosted: Thu Jun 04, 2009 2:29 pm    Post subject: Followup
Reply with quote

I think the query is answered - Is this needed for a program, or homework or reporting? or interview question?
Just curious...
Back to top
View user's profile Send private message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 2:32 pm    Post subject:
Reply with quote

yes it is needed for program.
Back to top
View user's profile Send private message
Sunaina Javali

New User


Joined: 22 Apr 2009
Posts: 16
Location: Bangalore

PostPosted: Thu Jun 04, 2009 2:34 pm    Post subject:
Reply with quote

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





Thanks,
Nisha
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2165
Location: @my desk

PostPosted: Thu Jun 04, 2009 2:37 pm    Post subject:
Reply with quote

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
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 2:58 pm    Post subject:
Reply with quote

Then why don't you fetch data into an array. How many app. records are in the database.
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Jun 04, 2009 3:03 pm    Post subject:
Reply with quote

good answer Arun.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jun 04, 2009 10:26 pm    Post subject:
Reply with quote

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" icon_confused.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us