Tweet
Just a guess, but I thought Column Functions always return SQLCODE = 0 ... In your case, MAX(ACGDAT ) is a Column Function. For example, when you do: Select count(*) as RcdCount from myfile where myfile.fld1 = 'A' You'll always get a result. RcdCount may be Zero, meaning that there are no records that match the criteria. But the SQLCODE will still be zero indicating a successful select, never 100 indicating that no record was found. In the case of the Max function, the result may be Null, but I don't think you'll get the SQLCODE = 100. I hope I'm not confusing the issue for you. Mike
-
-
Date field in SQL Statement
Below, from the SQL Ref Manual section on Column Functions. Notice the remarks about the Group By clause. And since I think you can actually Group By a Constant (I did a quick test), you might want to try something like: Select Max(fldAmt) as MyAmount from myfile where myfile.fld1 = 'A' Group By 'Z' In this case, I think an empty set would result and SQLCODE will be set to 100. Maybe.... Mike
Code -
Date field in SQL Statement
.---------------. V |>-COALESCE--(--expression----,--expression-+--)---------------><
The COALESCE function returns the value of the first non-null expression. When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is null), then return a value of zero. Example SELECT EMPNO, COALESCE(SALARY,0) FROM EMPLOYEE In your case: SELECT COALESCE(MAX(ACGDAT ), '0001-01-01') I used '0001-01-01' but you can specify any other valid date value. Will return zero to your program if no value is found (NULL)Comment
-
Date field in SQL Statement
I am using embedded SQL in a COBOL program to issue the following statement:[*]****EXEC SQL[*]**** SELECT MAX(ACGDAT )[*]**** INTO
ON.ACGDAT:WS-IND[*]**** FROM DON[*]**** WHERE INORKEY = ON.INORKEY[*]**** AND TRNTYP = ON.TRNTYP[*]**** AND TOTAMT >= ON.TOTAMT[*]****END-EXEC. MOVE INORKEY OF DON TO SQL-00048 MOVE TRNTYP OF DON TO SQL-00049 MOVE TOTAMT OF DON TO SQL-00050 MOVE -4 TO SQLERRD(6) CALL PROGRAM "QSQROUTE" IN LIBRARY "QSYS" USING SQLCA SQL-00047 IF SQL-DATA-OK OF SQL-00047 = "1" THEN MOVE SQL-00052 TO ACGDAT OF DON MOVE SQL-00053 TO WS-IND OF WS-PROGRAM-VARIABLES END-IF. When the search criteria is not satisfied, I still get SQLCODE = 0 and the code generated by the pre-compile has a value of "1" for SQL-DATA-OK. Because that test is true SQL tries to move a blank value for max date to a date field and the program blows up. Since that "IF" test after the call to QSQROUTE is generated by the pre-compiler, and I have no control of its attempting to move an invalid date value, what can I do to prevent to abend? Thanks.
Comment
-
Date field in SQL Statement
COALESCE function worked perfectly.Comment
Comment