i have the full procedure code, just showing the definition part... the procedure created, but the call you see above does not work. here is what the error log says for the create or replace procedure and the procedure call:
[ 06/23/2023, 09:05:56 AM ] Run Selected...
create or replace procedure gpl.jrnlviews language sql begin declare yearval int; declare yearsfx int; declare fisycur int; declare fisy2nd int; declare fisy3rd int; declare yearcur int; declare year2nd int; declare year3rd int; declare col_list varchar(8000); declare stmt_text varchar(10000); set yearval = year('2022-07-01'); set yearsfx = substr(yearval, 3, 2); set fisycur = yearval + 1; set fisy2nd = yearval; set fisy3rd = yearval - 1; set yearcur = yearsfx || yearsfx + 1; set year2nd = (yearsfx - 1) || (yearsfx); set year3rd = (yearsfx - 2) || (yearsfx - 1); set col_list = ( select listagg(cast(column_name as varchar(8000)), ', ') within group (order by ordinal_position) from qsys2.syscolumns where table_name = 'dpjrny2223' and table_schema='COALIBF' ); set stmt_text = ( 'create or replace view coalibf.DpJrnYVw (' || col_list || ', FiscalYear) as ' || 'select a.*, ' || char(fisycur) || ' from coalibf.dpjrny' || char(yearcur) || ' a ' || 'union all ' || 'select b.*, ' || char(fisy2nd) || ' from coalibf.dpjrny' || char(year2nd) || ' b ' || 'union all ' || 'select c.*, ' || char(fisy3rd) || ' from coalibf.dpjrny' || char(year3rd) || ' c' ); execute immediate stmt_text; set col_list = ( select listagg(cast(column_name as varchar(8000)), ', ') within group (order by ordinal_position) from qsys2.syscolumns where table_name = 'gljy2223' and table_schema='COALIBF' ); set stmt_text = ( 'create or replace view coalibf.GLJrnYVw (' || col_list || ', FiscalYear) as ' || 'select a.*, ' || char(fisycur) || ' from coalibf.gljy' || char(yearcur) || ' a ' || 'union all ' || 'select b.*, ' || char(fisy2nd) || ' from coalibf.gljy' || char(year2nd) || ' b ' || 'union all ' || 'select c.*, ' || char(fisy3rd) || ' from coalibf.gljy' || char(year3rd) || ' c' ); execute immediate stmt_text; end
Statement ran successfully (1,075 ms = 1.075 sec)
[ 06/23/2023, 09:06:32 AM ] Run Selected...
call gpl.jrnlviews()
SQL State: 22004
Vendor Code: -87
Message: [SQL0087] NULL value not allowed. Cause . . . . . : An expression or variable returned the null value where a null value is not allowed. Recovery . . . : Correct the expression or assign a non-null value to the variable so that the result is not null.
Failed statements: 1
------------------------------
David Strawn
------------------------------
Original Message:
Sent: Thu June 22, 2023 10:29 AM
From: Mike Zaringhalam
Subject: calling a stored procedure with no parms
You are calling the procedure correctly, but If that is your full create procedure statement, it will fail to create the object.
When you have BEGIN, you must also have an END.
BEGIN and END aren't always necessary to have if your function/procedure can be done in 1 single statement.
At that point you just need a RETURN followed by your 1 statement.
-Mike Z
------------------------------
Mike Zaringhalam
Original Message:
Sent: Thu June 22, 2023 10:20 AM
From: David Strawn
Subject: calling a stored procedure with no parms
here is my proc:
create or replace procedure gpl.jrnlviews
language sql
begin
Here is my call:
call gpl.jrnlviews();
tried without the parens, but didn't work...
Is this not how to call an internal stored procedure?
------------------------------
David Strawn
------------------------------
#SQL