SQL0440N error in Stored Procedure...... ..

[dalvigirish]

Hi,

I am new to DB2. I am trying to write a simple stored procedure, but getting the error attached below.

ERROR
=============== =============== =============== =============
TGT.AUDIT - Build started.
Create stored procedure returns -440.
TGT.AUDIT: 28: [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=28. SQLSTATE=42884


TGT.AUDIT - Build failed.
TGT.AUDIT - Roll back completed successfully.

=============== =============== =============== =============

Attched below is the code which I have written. The error statement is marked as *. Can anyone please help me out....

Thanks in Adv.

Girish Dalvi



CREATE PROCEDURE TGT.AUDIT (IN V_TAB_NAME VARCHAR(10) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
dl: BEGIN
DECLARE v_tbl_name varchar(10);
DECLARE I int default 0;
DECLARE v_col_count int;
DECLARE v_col_name varchar(10);
DECLARE v_total INT;
DECLARE v_col varchar(10);
DECLARE SQLSTRING varchar(4000);
DECLARE V_STMT STATEMENT;




DECLARE cur_col_name cursor for select colname from metadata where tname=V_TAB_NAM E;


select count(*) into v_col_count from metadata where tname=V_TAB_NAM E;
insert into tmp values ('the new value is ',v_col_count);
open cur_col_name;

while I < v_col_count
do
fetch cur_col_name into v_col_name;
* SET SQLSTRING = 'SELECT sum(cast('|| v_col_name || ')) into' ||v_total|| ' from ' || V_TAB_NAME;
PREPARE V_STMT FROM SQLSTRING;
Insert into TOTALS values (V_TAB_NAME,v_c ol_name, v_total);
set I=I+1;
end while;
close cur_col_name;

END dl

[sylvie36]

Hello,

When you see the error message you have the number of the line which is not ok. It's the 28's one so it's the next one :

And the message is sample. You can't used || has an argument so you need employed quotes ' ' for puting it as commantary and not as a type of argument of the function V_TAB_NAME

* SET SQLSTRING = 'SELECT sum(cast('|| v_col_name || ')) into' ||v_total|| ' from ' || V_TAB_NAME

To my mind you must trying by put some another quotes as after in your line as next :

* SET SQLSTRING = 'SELECT sum(cast('|| v_col_name || ')) into' ||v_total|| ' from ' '||' V_TAB_NAME

Good luck

If you need more explication I'm trying to help you

Bye

Hi,

I am new to DB2. I am trying to write a simple stored procedure, but getting the error attached below.

ERROR
=============== =============== =============== =============
TGT.AUDIT - Build started.
Create stored procedure returns -440.
TGT.AUDIT: 28: [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=28. SQLSTATE=42884


TGT.AUDIT - Build failed.
TGT.AUDIT - Roll back completed successfully.

=============== =============== =============== =============

Attched below is the code which I have written. The error statement is marked as *. Can anyone please help me out....

Thanks in Adv.

Girish Dalvi



CREATE PROCEDURE TGT.AUDIT (IN V_TAB_NAME VARCHAR(10) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
dl: BEGIN
DECLARE v_tbl_name varchar(10);
DECLARE I int default 0;
DECLARE v_col_count int;
DECLARE v_col_name varchar(10);
DECLARE v_total INT;
DECLARE v_col varchar(10);
DECLARE SQLSTRING varchar(4000);
DECLARE V_STMT STATEMENT;




DECLARE cur_col_name cursor for select colname from metadata where tname=V_TAB_NAM E;


select count(*) into v_col_count from metadata where tname=V_TAB_NAM E;
insert into tmp values ('the new value is ',v_col_count);
open cur_col_name;

while I < v_col_count
do
fetch cur_col_name into v_col_name;
* SET SQLSTRING = 'SELECT sum(cast('|| v_col_name || ')) into' ||v_total|| ' from ' || V_TAB_NAME;
PREPARE V_STMT FROM SQLSTRING;
Insert into TOTALS values (V_TAB_NAME,v_c ol_name, v_total);
set I=I+1;
end while;
close cur_col_name;

END dl

[sylvie36]

Hello,
To my mind you can try to add some quotes to the line 28 which is the line on error as next :
* SET SQLSTRING = 'SELECT sum(cast('|| v_col_name || ')) into' ||v_total|| ' from ' '||' V_TAB_NAME
Because if you don't put quotes || is a type of argument for the function V_TAB_NAME which isn't the case.

Good luck

Bye