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
 

 

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: 8593
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: 1226
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts REXX cdoe to purge a JOB from spool a... Sulabh Agrawal CLIST & REXX 11 Thu Mar 09, 2017 11:36 am
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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