Hello all, I'm trying to create a stored procedure that will 1. Create a Table 2. Populate the table from sys_calendar.calendar base on input parm. 3. Move that to output parm. 4. Drop the table
Question: Can I do this in one procedure, using dbc.sysexecsql I'm at step 2 and when I try to reference var_date( in the same way I referenced dbnm, tbnm, I get no records, If I use just Date(in place of ||Var_date when I create Procedure) as parm I Insert correct records from sys_cal.calendar when I call it;
Any help would be greatly appreciated.
REPLACE PROCEDURE dbname.outdt (IN DBNM CHAR(8), IN TBNM CHAR(10), IN VAR_DATE DATE OUT ODATE DATE)
CALL DBC.SYSEXECSQL ('CREATE TABLE ' || DBNM || '.' || TBNM || ' (G_DT DATE FORMAT ''YYYY-MM-DD'' ,G_DAY BYTEINT)');
CALL DBC.SYSEXECSQL ('INSERT INTO ' || DBNM ||'.'|| TBNM || ' SEL CALENDAR_DATE, DAY_OF_MONTH ' || 'FROM ' || 'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < '||VAR_DATE);
Think about the value of the string expression. This is NOT what you want: 'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < '||VAR_DATE because that would become (for example) 'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < 2006-12-06' so the term on the right is the integer expression 2006 minus 12 minus 6 or 1988 which in turn would correspond to an invalid date (1900-19-88) that falls between 1900-12-31 and 1901-01-01.
If you could use parameter substitution in dynamic SQL, you would say (as I suspect you did with DATE) 'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < VAR_DATE'
An alternative is to explicitly build a date literal string, e.g. 'SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE < date''' ||cast(cast(VAR_DATE AS FORMAT('YYYY-MM-DD') AS CHAR(10))||''''