|
View previous topic :: View next topic
|
| Author |
Message |
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
I want to check a field value is NULL or not when I fetch the DB2 table
I read [ DB2 for OS/390 REXX Language Support.pdf ] like below
but I can understand how to define a NULL indicator for a DB2 host variable , just after the host variable or must start with IND ?
| Code: |
In the following example, the phone number for employee Haas is selected into
variable HVPhone. After the SELECT statement executes, if no phone number for
employee Haas is found, indicator variable INDPhone contains -1.
SQLSTMT = ,
"SELECT PHONENO WHERE LASTNAME=©HAAS©"
"EXECSQL PREPARE S1 FROM :SQLSTMT"
"EXECSQL DECLARE C1 CURSOR FOR S1"
"EXECSQL OPEN C1"
"EXECSQL FETCH C1 INTO :HVPhone :INDPhone"
If INDPhone < 0 Then ,
Say 'Phone number for Haas is null.' |
|
|
| Back to top |
|
 |
yuvrajdutta
New User
Joined: 13 Jul 2009
Posts: 40
Location: India
|
|
|
|
This is what works for me:
| Code: |
/* REXX */
ADDRESS TSO
"SUBCOM DSNREXX"
IF RC = 1 THEN DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX
"CONNECT DB2R"
QRY="SELECT EMP_ID,",
"FIRST_NAME,",
"MIDDLE_NAME,",
"LAST_NAME",
"FROM IBMGRP.EMP"
SAY QRY
"EXECSQL DECLARE C1 CURSOR FOR S1"
IF SQLCODE /= 0 THEN CALL SQLCA
'EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY'
IF SQLCODE /= 0 THEN CALL SQLCA
"EXECSQL OPEN C1"
IF SQLCODE /= 0 THEN CALL SQLCA
DO UNTIL(SQLCODE /= 0)
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE /= 0 THEN
CALL SQLCA
ELSE
DO
DO I=1 TO OUTSQLDA.SQLD
SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME
SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE
SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN
SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND
IF OUTSQLDA.I.SQLIND >= 0 THEN
SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
ELSE
SAY "NULL DATA RETRIEVED"
END I
SAY
SAY "END OF RECORD"
SAY
END
END
"EXECSQL CLOSE C1"
IF SQLCODE /= 0 THEN CALL SQLCA
"DISCONNECT"
IF SQLCODE /= 0 THEN CALL SQLCA
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')
SQLCA:
SAY "SQLCODE = " SQLCODE
SAY "SQLSTATE = " SQLSTATE
SAY "SQLERRMC = " SQLERRMC
EXIT
|
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
Dave - your link was to a Linux/Unix/Windows document not mainframe.
Chaoj - just code your indicator variable immediately after the variable as per the example you posted. The indicator variable does not have to begin IND. |
|
| Back to top |
|
|
chaoj
Active User
Joined: 03 Jun 2010
Posts: 103
Location: Dalian
|
|
|
|
| Nic Clouston wrote: |
Dave - your link was to a Linux/Unix/Windows document not mainframe.
Chaoj - just code your indicator variable immediately after the variable as per the example you posted. The indicator variable does not have to begin IND. |
Thank you !!! It works !! |
|
| Back to top |
|
|
|
|
