I am new in SQL/PL, I wrote following procedure to return min and max value (type: TIMSTAMP)in DB2 v9.5. IT is a dynamic SQL since i would like pass fieldname and table name as parameter.
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PROCEDURE get_min_max_time(
- IN p_schemaname ANCHOR maint.archive_cntl.del_schema,
- IN p_tablename ANCHOR maint.archive_cntl.del_table,
- IN p_fieldname ANCHOR maint.archive_cntl.del_table_time_col_name,
- OUT p_min_ts TIMESTAMP,
- OUT p_max_ts TIMESTAMP)
- LANGUAGE SQL
- BEGIN
- DECLARE stmt VARCHAR(1000);
- SET stmt = 'SELECT ' || MIN(p_fieldname) || ',' || MAX(p_fieldname) || ' from ' || trim(p_schemaname) || '.' || trim(p_tablename);
- PREPARE S1 FROM stmt;
- DECLARE c1 CURSOR FOR S1;
- OPEN c1;
- FETCH c1 into v_min_ts, v_max_ts;
- CLOSE C1;
- END@
when compiling, i got following error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0120N Invalid use of an aggregate function or OLAP function. LINE
NUMBER=1. SQLSTATE=42903
Any idea what is wrong with the above code?
thanks.