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
 
REXX - DB2 query

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

New User


Joined: 31 Mar 2008
Posts: 14
Location: kansas city

PostPosted: Fri Jul 17, 2009 4:42 pm    Post subject: REXX - DB2 query
Reply with quote

I am facing problem while executing a Sql query having two table in use.
Dont we execute a SQL query having two table -like nested sql or join.
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8620
Location: Back in jolly old England

PostPosted: Fri Jul 17, 2009 4:53 pm    Post subject:
Reply with quote

What exactly is the problem here.
Back to top
View user's profile Send private message
kansassho

New User


Joined: 31 Mar 2008
Posts: 14
Location: kansas city

PostPosted: Tue Jul 21, 2009 4:05 pm    Post subject:
Reply with quote

I am executing mu query using DSNREXX routine.
My code looks like

Code:
SQLSTMT = "SELECT  DISTINCT A.COMPANY_CODE,A.MASTER_ID       
FROM MLQV1b0A.CONTF A                                       
WHERE (A.RESIDENT_STATE = 'OC ' OR  A.US_CITIZEN_IND = 'N')"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"           
say rc                                                       
If SQLCODE <> 0 Then Call SQLCA                             
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"           
say rc                                                       
If SQLCODE <> 0 Then Call SQLCA                             
ADDRESS DSNREXX "EXECSQL OPEN C1"                           
say rc                                                       
If SQLCODE <> 0 Then Call SQLCA                             
/* CHECKING SQL CODE */                                     
DO UNTIL(SQLCODE \= 0)                                       
   "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"             
   IF(SQLCODE = 0) THEN DO                                   
        say 'in'                                             
        LINE1 = ''                                           
        LINE1 = ''                           
        LINE2 = ''                           
        LINE1 =LINE1 OUTSQLDA.2.SQLDATA       
        LINE2 =LINE2 OUTSQLDA.4.SQLDATA       
        SAY     LINE1 LINE2                   
   END                                       
   ELSE                                       
        SAY SQLCODE                           
END                                           




Now my question is the variable SQLSTMT is not able to take a query of more than 250 character.

Is there any way, so that I can get any query of more than 250 character.

Please suggest
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1252
Location: Israel

PostPosted: Tue Jul 21, 2009 6:39 pm    Post subject:
Reply with quote

Code:
SQLSTMT = "SELECT  DISTINCT A.COMPANY_CODE,A.MASTER_ID       
FROM MLQV1b0A.CONTF A                                       
WHERE (A.RESIDENT_STATE = 'OC ' OR  A.US_CITIZEN_IND = 'N')"

I like to keep my statements well formatted:
Code:
SQLSTMT = "SELECT  DISTINCT A.COMPANY_CODE,A.MASTER_ID",
   "FROM MLQV1b0A.CONTF A",
   "WHERE (A.RESIDENT_STATE = 'OC ' OR  A.US_CITIZEN_IND = 'N')"

I have queries like that with more than 400 chars.
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed Jul 22, 2009 12:58 am    Post subject:
Reply with quote

What is the error you are getting? Please post the entire error message.
Back to top
View user's profile Send private message
Venkat1001

New User


Joined: 25 Feb 2008
Posts: 12
Location: chennai

PostPosted: Mon Aug 31, 2009 8:01 am    Post subject: Reply to: REXX - DB2 query
Reply with quote

SQLSTMT1 = "SELECT DISTINCT A.COMPANY_CODE,A.MASTER_ID"
SQLSTMT2 = "FROM MLQV1b0A.CONTF A"
SQLSTMT3 = "WHERE (A.RESIDENT_STATE = 'OC ' OR A.US_CITIZEN_IND = 'N')"
SQLSTMT = SQLSTMT1||SQLSTMT2||SQLSTMT3

try this and it works.
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
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Looping REXX - unable to interrupt RAVISANKAR07 CLIST & REXX 3 Wed Dec 06, 2017 1:08 pm
No new posts The same REXX program doesnt work for... jackzhang75 CLIST & REXX 5 Wed Dec 06, 2017 2:51 am
No new posts Rexx - File tailoring samzee71 CLIST & REXX 10 Thu Nov 30, 2017 10:47 pm
This topic is locked: you cannot edit posts or make replies. Rexx to Grab ISMF Information. upendrasri All Other Mainframe Topics 16 Sun Nov 26, 2017 12:35 am

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