Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 1281
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us