| Oracle error re: null dates returned on a select - into sql structure in a cobol program [message #39143] |
Thu, 20 June 2002 08:03  |
Phil Braniff
Messages: 2
Registered: June 2002
|
Junior Member |
|
|
I’m having a problem with the fact that I perform a select-into statement and there are null values in the date fields. I attempt to use the NVL function on the date field and it keeps coming back with an error: 01840, 00000, "input value not long enough for date format". I made the reconciled-date field extremely large and it still comes back with that message. When the date field is removed from the select-into group, the SQL works fine. What do you do with a NULL date on a select-into situation? I’ve modified the NVL blank date string many times to be much larger than anything a date might need. It’s up to 40 characters now and it still comes back with that error message. I made the receiving date field in the declare section to be 40 characters as well. It doesn’t seem to make any difference. When I leave off the NVL function on the date, I get a: 01405, 00000, "fetched column value is NULL" error message from the main WHENEVER SQLERROR in the connect section. What else could I do to smoothly complete the sql statement when there are NULL dates?
The precompiler is: Pro*COBOL: Release 1.8.4.0.0 - Production on Thu Jun 20 11:39:33 2002
The following are the pertinent sections of the cobol code:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VARYING.
01 PASSWORD PIC X(10) VARYING.
01 COMPANY-ID PIC X(03).
01 CHECK-NUMBER PIC X(09).
01 BANK-ID PIC X(03).
01 RECONCILED-DATE PIC X(40).
01 STATUS-IND PIC X(01).
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
…….
A250-ORACLE-CONNECTION SECTION.
A250-010.
EXEC SQL WHENEVER SQLERROR DO PERFORM A250-020-ORACLE-ERROR END-EXEC.
MOVE "pgvi" TO USERNAME-ARR.
MOVE 4 TO USERNAME-LEN.
MOVE "pgvi1" TO PASSWORD-ARR.
MOVE 5 TO PASSWORD-LEN.
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD END-EXEC.
GO TO A250-EXIT.
A250-020-ORACLE-ERROR.
DISPLAY " ".
DISPLAY "THERE WAS A DATABASE ERROR!!" BELL REVERSED.
DISPLAY " Oracle return code is ",SQLCODE.
DISPLAY " Type oerr ora ",SQLCODE," without the minus sign".
DISPLAY " ".
PERFORM A250-ORACLE-DISCONNECT.
A250-ORACLE-DISCONNECT.
PERFORM E100-CLOSE.
DISPLAY " ".
DISPLAY "Disconnected from ORACLE" BELL REVERSED.
STOP RUN.
A250-EXIT.
EXIT.
…
B150-CHECK-BANK-REC SECTION.
B150-010.
DISPLAY " Company ID = ",COMPANY-ID.
DISPLAY " Bank ID = ",BANK-ID.
DISPLAY " Check Number = ",CHECK-NUMBER.
DISPLAY " "
EXEC SQL
WHENEVER NOT FOUND
DO PERFORM B150-026-NOT-FOUND-MSG END-EXEC.
EXEC SQL
SELECT NVL(RECONCILED_AMT,0),
NVL(STATUS_IND,' '),
NVL(RECONCILED_DATE,' ')
INTO : RECONCILED-AMT,
STATUS-IND,
RECONCILED-DATE
FROM CHECK_RECONCS
WHERE COMPANY_ID = : COMPANY-ID
AND BANK_ID = : BANK-ID
AND CHECK_NUMBER = : CHECK-NUMBER
END-EXEC.
GO TO B100-EXIT.
B150-026-NOT-FOUND-MSG.
MOVE " " TO W10-PMT-PAY-RECON-SW.
MOVE SPACE TO W10-PMT-PAY-RECON-DATE.
MOVE SPACE TO W10-PMT-PAY-STATEMENT-DATE.
display " NOT FOUND - CHECK NUMBER",CHECK-NUMBER.
MOVE "N" TO W20-RECONCILIATION-REC-FOUND.
B150-EXIT.
EXIT.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
