IBM Mainframe Forum

i have a sql query and i want to execute this using rexx.

SELECT * FROM
(SELECT
PRIMARY KEY OF TABLE1
FROM TABLE1) AS PARTA

LEFT OUTER JOIN

(SELECT
PRIMARY KEY OF TABLE2
FROM TABLE2) AS PARTB

ON PARTA.PRIMARY KEY OF TABLE1= PARTB.PRIMARY KEY OF TABLE2

WHERE PARTB.PRIMARY KEY OF TABLE2 IS NULL


PLEASE SUGGEST HOW TO EXECUTE IT USING REXX......

Hello,

What have you tried? What happened?

"Application programming and SQL guide" can be read or downloaded from the IBM website.

It contains a whole section on using SQL with REXX

Below is my Rexx code:


"ISREDIT MACRO "
Clientid = ""

ADDRESS TSO "SUBCOM DSNREXX" /* DSN Rexx environment available

if rc then
do
s_rc= RXSUBCOM('ADD','DSNREXX','DSNREXX')
end

ARG DD1
CLS
IF DD1 = ' ' THEN
DO
SAY 'Enter the name of database'
PULL DD1

ADDRESS DSNREXX "CONNECT" DD1

if SQLCODE \= 0 then
Do
say 'Sql Error during connect to subsystem:' DD1
End

/* PREPARE SQL STATEMENTS */

address tso
sqlStmt = " SELECT * (SELECT FROM TABLE1) AS PARTA"
sqlStmt = " LEFT OUTER JOIN"
sqlStmt = " (SELECT FROM TABLE2) AS PARTB"
sqlStmt = sqlStmt ||" ON PARTA.PRIMARY KEY OF TABLE1 = PARTB.PRIMARY KEY OF TABLE2"
sqlStmt = sqlStmt ||"WHERE PARTB.PRIMARY KEY OF TABLE2 = NULL"

/* execute the sql statment */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR WITH HOLD FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :SQLDA FROM :SQLSTMT"
ADDRESS DSNREXX "EXECSQL OPEN C1"
If SQLCODE \= 0 then
do
say ' SQL error :' SQLCODE
EXIT 0
end

num=0
Do while sqlcode = 0

/*Fetch the cursor */

ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :SQLDA"
if sqlcode =0 then
do
num = num+1
PRIMARY KEY OF TABLE1 = sqlda.1.sqldata
PRIMARY KEY OF TABLE1 .num = PRIMARY KEY OF TABLE1
say ' PRIMARY KEY OF TABLE1 :' PRIMARY KEY OF TABLE1 .num
end
else
if num = 0 then
say ' No record found'
End

ADDRESS DSNREXX "EXECSQL CLOSE C1"

/* End of the program */
EXIT 0


In this code i am comparing two tables and finding out the record which is there in table1 but not there in table2. table1 is the child table and table2 is the parent table.

i want to take 4 inputs from the user : name of table1, name of table2, primary key of table1, primary key of table2

i tried my code and i am able to connect to the database. but the sql query is not getting executed. it is showing following error code: -514

Please suggest the corrections in my code so that i can get the desired output..

Hello,

Did you refer to the -514 in the manual?
http://publibz.boulder.ibm.com/cgi-bin/ ... /2.1.6.223?

Suggest you need to check sqlcode after every execsql. . .

i checked sqlcode after every execsql statement like below. It worked fine for the "cursor declaration statement" but giving SQL error2 : -104 for the prepare statement.


/* execute the sql statment */
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR WITH HOLD FOR S1"
If SQLCODE \= 0 then
do
say ' SQL error1 :' SQLCODE
EXIT 0
end

ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :SQLDA FROM :SQLSTMT"
If SQLCODE \= 0 then
do
say ' SQL error2 :' SQLCODE
EXIT 0
end

ADDRESS DSNREXX "EXECSQL OPEN C1"
If SQLCODE \= 0 then
do
say ' SQL error3 :' SQLCODE
EXIT 0
end

Please suggest..

Hello,

What did you learn when you looked up the -104?