View Full Version : Date field in SQL Statement
11-04-2004, 07:16 AM
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
11-04-2004, 07:36 AM
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 <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b185656/1')
11-04-2004, 09:56 AM
.---------------. V | <blockquote><tt>>-COALESCE--(--expression----,--expression-+--)--------------->< </tt></blockquote> 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)
11-05-2004, 06:32 AM
I am using embedded SQL in a COBOL program to issue the following statement: ****EXEC SQL **** SELECT MAX(ACGDAT ) **** INTO :DON.ACGDAT:WS-IND **** FROM DON **** WHERE INORKEY = :DON.INORKEY **** AND TRNTYP = :DON.TRNTYP **** AND TOTAMT >= :DON.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.
11-05-2004, 06:32 AM
COALESCE function worked perfectly.
Powered by vBulletin® Version 4.1.5 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.