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
 

 

SQL query to pull out some records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
santohsks1987
Warnings : 1

New User


Joined: 29 Dec 2010
Posts: 31
Location: Mumbai

PostPosted: Tue Jan 11, 2011 3:03 pm    Post subject: SQL query to pull out some records
Reply with quote

Hi,

I have three fields namely A,B,C in a table and a file. I have to write a SQL query in which i ll pull out some records fro the table if any of the above 2 fields are equal. That is I ll write the query as
SELECT FIRST_NM, REL_CD, BITRTH_DT
FROM T_NEUT_MBR
WHERE
REL_CD NOT = ā€˜Eā€™
AND ((FIRST_NM = WS-FIRST-NAME(SUB)
AND ((REL_CD = WS-REL-CD-SUB)
OR BIRTH_DT = WS-BIRTH_DT(SUB)))
OR (REL_CD = WS-REL_CD(SUB)
AND BIRTH_DT = WS-BIRTH_DT(SUB))

But if it gives more than 1 record than i will have to check the equality of the other field also. How should i do this.
Back to top
View user's profile Send private message

balakrishna reddy.bala

New User


Joined: 15 Sep 2010
Posts: 17
Location: india

PostPosted: Tue Jan 11, 2011 5:38 pm    Post subject:
Reply with quote

Hi,

When you post any piece of code, please use the Code tag.

For your requirement, please go thru the below link


http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnapk10/2.2?ACTION=MATCHES&REQUEST=cursor&TYPE=FUZZY&SHELF=&DT=20070123230158&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
Back to top
View user's profile Send private message
santohsks1987
Warnings : 1

New User


Joined: 29 Dec 2010
Posts: 31
Location: Mumbai

PostPosted: Tue Jan 11, 2011 5:41 pm    Post subject:
Reply with quote

but i dont want to use the cursors.....
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jan 11, 2011 5:41 pm    Post subject: Re: SQL query2
Reply with quote

Code:
SELECT FIRST_NM, REL_CD, BITRTH_DT,
         case when FIRST_NM = :WS-FIRST-NAME(SUB) then 1 else 0 end
       + case when REL_CD   = :WS-REL-CD (SUB)    then 1 else 0 end
       + case when BIRTH_DT = :WS-BIRTH_DT(SUB)   then 1 else 0 end
     as numCondTrue

  FROM  T_NEUT_MBR
 WHERE REL_CD NOT = ā€˜Eā€™
   AND ((    FIRST_NM = :WS-FIRST-NAME(SUB)
         AND (   (REL_CD = :WS-REL-CD (SUB)
               OR BIRTH_DT = :WS-BIRTH_DT(SUB)))
       OR (    REL_CD = :WS-REL_CD(SUB)
         AND BIRTH_DT = :WS-BIRTH_DT(SUB))
order by 4 desc
fetch first 1 row only
Back to top
View user's profile Send private message
santohsks1987
Warnings : 1

New User


Joined: 29 Dec 2010
Posts: 31
Location: Mumbai

PostPosted: Tue Jan 11, 2011 5:48 pm    Post subject:
Reply with quote

@GuyC:it looks fine...... can u please give a brief explanation of the query.....
Thank u for the reply.......
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jan 11, 2011 6:17 pm    Post subject:
Reply with quote

each case instructions just returns 1 if the condition is true 0 if false.
These are added together
so you get 0,1,2 or 3 as numcondtrue

ofcourse only 2 and 3 will come out of the select because of the where-clause.

order by 4 desc : measn order by the fourth column in the result , which is numcondtrue , and DESCENDING meaning 3 first then all the 2s

fetch first 1 row only : will give you only 1 row even when there would have been more.
Back to top
View user's profile Send private message
santohsks1987
Warnings : 1

New User


Joined: 29 Dec 2010
Posts: 31
Location: Mumbai

PostPosted: Tue Jan 11, 2011 6:21 pm    Post subject:
Reply with quote

oh its excellent..... thank you......
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am


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