By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,886 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,886 IT Pros & Developers. It's quick & easy.

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

P: 1
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_NAME;


select count(*) into v_col_count from metadata where tname=V_TAB_NAME;
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_col_name, v_total);
set I=I+1;
end while;
close cur_col_name;

END dl
Jun 15 '06 #1
Share this Question
Share on Google+
2 Replies


P: 4
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_NAME;


select count(*) into v_col_count from metadata where tname=V_TAB_NAME;
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_col_name, v_total);
set I=I+1;
end while;
close cur_col_name;

END dl
Jul 24 '06 #2

P: 4
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
Jul 24 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.