AS400, SQL and COBOL anomalies
Tags:
I coded an update query in COBOL and included COMMIT=NONE so the update could go through. When the COBOL program executed it returned nulls, and coalesced the value to the default. When the query was executed either through qmqry or on-line on the sql screen it worked fine and updated the field properly.
I also tried calling a cl program that executed the QMQRY from within the COBOL program and I also got bad results. When I called the CL from within another CL or called the CL program directly I got expected results.
Here is the query:
EXEC SQL UPDATE FILEA A SET A.ITMID = COALESCE((SELECT DISTINCT B.ITMID FROM FILEB B WHERE A.FLDA = B.FLDA AND A.FLDB = B.FLDB AND A.FLDC = B.FLDC),:NO-ITM) END-EXEC.
Software/Hardware used:
as400 cobol iseries embedded sql
I also tried calling a cl program that executed the QMQRY from within the COBOL program and I also got bad results. When I called the CL from within another CL or called the CL program directly I got expected results.
Here is the query:
EXEC SQL UPDATE FILEA A SET A.ITMID = COALESCE((SELECT DISTINCT B.ITMID FROM FILEB B WHERE A.FLDA = B.FLDA AND A.FLDB = B.FLDB AND A.FLDC = B.FLDC),:NO-ITM) END-EXEC.
Software/Hardware used:
as400 cobol iseries embedded sql
Asked:
Last updated:
Answer Wiki
-
Last updated:62,340 pts.
- History
-
Contributors
- Ordered by most recent
-
62,340 pts.
Discuss This Question: 1 Reply
- UPDATE FILEA A SET A.ITMID = COALESCE((SELECT DISTINCT B.ITMID FROM FILEB B WHERE A.FLDA = B.FLDA AND A.FLDB = B.FLDB AND A.FLDC = B.FLDC),:NO-ITM)
Since that statement contains a host variable -- NO-ITM -- it wouldn't run in QMQRY nor in interactive SQL. You had to use a different UPDATE statement. What was it? And what is the COBOL definition of NO-ITM? (NO-ITM probably doesn't matter, but it might help in understanding.) Did the COBOL execution actually update rows causing A.ITMID to become null when it previously had a value or was A.ITMID already null?