Stored procedure error

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted
Enthusiast

Stored procedure error

Hello,

 

I am trying to create a dynamic query through stored procedure but getting below error.

Error  - Call Failed 5628 : Store_proc1 :column TABLE1 not found in COMP.LEVEL_TAB

 

DB_NAME and TB_NAME are input parameters to stored procedure stor_proc1and giving TABLE1 as TAB_NAME value.

 

Line in store procedure :

Set STM4 = ' CASE WHEN COLUMNID = LASTCOL THEN ' || ' || ' || '''' || ' FROM ' || '''' || ' || ' || ' TRIM(DB_NAME.TB_NAME)' || ' ELSE ' || '''' || ' ' || '''' || ' END ' ;

Set STM5 = ' FROM COMP.LEVEL_TAB ' ;

 

Basically i am trying to replicate below line from macro in this stored procedure and that is why i have given concat sign (||) in comments. Also tried giving as 'TRIM(' || DB_NAME || '.' || 'TRIM(' || TB_NAME but it was giving error something expected between DB_NAME and '.' so gave together.

 

Line in Macro :

CASE WHEN COLUMNID = LASTCOL THEN || ' FROM ' || TRIM(:DB_NAME) || '.' || TRIM(:TB_NAME) || ELSE ' ' END

FROM COMP.LEVEL_TAB

 

Macro is working fine but as dynamic query can't be run in it so trying to replicate same in Stored procedure. 

 

Please help.

 

Thanks

 


Accepted Solutions
Teradata Employee

Re: Stored procedure error

There was a mistake :)

Try this :

 

Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM ' || TRIM(DB_NAME) || '.' || TRIM(TB_NAME) || ' '' ELSE '' '' END';

To show anything, use a global temporary to log.

 

I used @dnoeth method from this post to do someting very basic :

 

 

CREATE GLOBAL TEMPORARY TABLE gt_DBMS_OUTPUT
 (
   TS TIMESTAMP(2) DEFAULT Current_Timestamp(2), 
   Step VARCHAR(100), 
   ErrorMsg VARCHAR(200)
 ) ON COMMIT PRESERVE ROWS;
 


replace procedure stor_proc1
( DB_NAME    varchar(128)
, TB_NAME    varchar(128)
)
DYNAMIC RESULT SETS 1
begin
	  
    declare STM4 varchar(500);
    
    delete from gt_DBMS_OUTPUT;
    
    Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM ' || TRIM(DB_NAME) || '.' || TRIM(TB_NAME) || ' '' ELSE '' '' END';
    INSERT INTO gt_DBMS_OUTPUT (Step, ErrorMsg) VALUES ('step 1', STM4);  
    
    BEGIN
      DECLARE qry VARCHAR(100);
      DECLARE ErrorMsgs CURSOR WITH RETURN ONLY FOR c;
      SET qry = 'SELECT * FROM gt_DBMS_OUTPUT ORDER BY TS;';
      PREPARE c FROM qry;
      OPEN ErrorMsgs;
   END;
    
end;

call stor_proc1('ABCD   ', '    EFGH   ');

 

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Stored procedure error

Hi tera_user,

 

Yes, those quote can get tedious sometimes.

My rule for handling a conversion of a string inside another string is to always replace one quote by two, than put one at the start and one at the end, then handle the parameters / variables using concatenations.

Try this :

Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM '' || ' || TRIM(DB_NAME) || ''.'' || TRIM(TB_NAME) || ' ELSE '' '' END';

Of course, you must always print your variables before executing them while in development phase, it really helps !

Enthusiast

Re: Stored procedure error

 

Hi Waldar,

 

Thanks for the details.

How do i display each statement in a stored procedure to check its output because i am executing the entire SP with CALL statement.

 

 

Enthusiast

Re: Stored procedure error

 

Hi Waldar,

 

I am getting this error with below syntax.

 

Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM '' || ' || TRIM(DB_NAME) || ''.'' || TRIM(TB_NAME) || ' ELSE '' '' END';

 

SPL1027 E(L18), Missing/Invalid SQL Statement 'E(3707) : Syntax error, expected something like ';' between a string or a unicode character literal and '.'.'.

 

Thanks.

 

Teradata Employee

Re: Stored procedure error

There was a mistake :)

Try this :

 

Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM ' || TRIM(DB_NAME) || '.' || TRIM(TB_NAME) || ' '' ELSE '' '' END';

To show anything, use a global temporary to log.

 

I used @dnoeth method from this post to do someting very basic :

 

 

CREATE GLOBAL TEMPORARY TABLE gt_DBMS_OUTPUT
 (
   TS TIMESTAMP(2) DEFAULT Current_Timestamp(2), 
   Step VARCHAR(100), 
   ErrorMsg VARCHAR(200)
 ) ON COMMIT PRESERVE ROWS;
 


replace procedure stor_proc1
( DB_NAME    varchar(128)
, TB_NAME    varchar(128)
)
DYNAMIC RESULT SETS 1
begin
	  
    declare STM4 varchar(500);
    
    delete from gt_DBMS_OUTPUT;
    
    Set STM4 = 'CASE WHEN COLUMNID = LASTCOL THEN || '' FROM ' || TRIM(DB_NAME) || '.' || TRIM(TB_NAME) || ' '' ELSE '' '' END';
    INSERT INTO gt_DBMS_OUTPUT (Step, ErrorMsg) VALUES ('step 1', STM4);  
    
    BEGIN
      DECLARE qry VARCHAR(100);
      DECLARE ErrorMsgs CURSOR WITH RETURN ONLY FOR c;
      SET qry = 'SELECT * FROM gt_DBMS_OUTPUT ORDER BY TS;';
      PREPARE c FROM qry;
      OPEN ErrorMsgs;
   END;
    
end;

call stor_proc1('ABCD   ', '    EFGH   ');

 

Enthusiast

Re: Stored procedure error

 

Thanks Waldar as now i am able to check the SP output and generate the correct query.