All Forums Database
andrewah 11 posts Joined 06/11
18 Sep 2013
Use of CTE within stored procedures

Hi all

 

I am using Teradata Express 13.10 & I am trying to write a stored procedure which uses multiple references to dataset defined using a 'common table expression.'

 

I have a test case below:

 

create table with_source ( a int generated always as identity , b int );
 
create table with_target ( a int generated always as identity , b int );
 
insert into with_source ( b ) values ( 1 );

And this stored procedure ...

replace procedure with_test_proc ()
begin
 
       insert
         into
              with_target
            (
                b
            )
         with
              tmp
            (
                b
            )
           as
            (
                  select
                         1 b
                    from
                         with_source
            )
       select
              b
         from
              tmp
    union
       select
              b + 1
         from
              tmp
            ;
 
end;

... which compiles without error.
However, when I execute this procedure, I receive this error message:

Unhandled exception caught in rr_dfs_balance_recon:00000:   : Syntax error, expected something like a name or a Unicode delimited identifier or a 'SELECT' keyword or '(' or a 'NONTEMPORAL' keyword or 'AS' keyword between '(' and the 'WITH' keyword.: setting return code = -1

 

Does anybody have an idea how to resolve this?

 

Thanks,

 

Andrew.

M.Saeed Khurram 544 posts Joined 09/12
18 Sep 2013

It is strange that this SQL works fine in SQL-Assistant, but when called througha procedure throws error. I have checked with recursive CTE as well, but it throws the same error. 
But it works fine the usual derived table syntax, as follows

REPLACE MACRO with_test_proc AS 
(
  
       INSERT
         INTO
              with_target
            (
                b
            )
            SELECT b
            FROM
            (
                  SELECT
                         1 b
                    FROM
                         with_source
            ) tmp
            UNION
            SELECT
              b + 1
              FROM
            (
                  SELECT
                         1 b
                    FROM
                         with_source
            ) tmp
            ;
);

Can it help you scenario?
 

Khurram

Harpreet Singh 101 posts Joined 10/11
18 Sep 2013

It works with derived table ..
 
CREATE TABLE with_source ( a INT GENERATED always AS IDENTITY , b INT );
CREATE TABLE with_target ( a INT GENERATED always AS IDENTITY , b INT );
INSERT INTO with_source ( b ) VALUES ( 1 );
 
REPLACE PROCEDURE with_test_proc ()
BEGIN
INSERT INTO with_target ( b )
SELECT b
FROM (
SELECT 1 b
FROM with_source ) tmp
UNION
SELECT b + 1
FROM (
SELECT 1 b
FROM with_source ) tmp ; END;
DELETE FROM with_target;
CALL with_test_proc();
SELECT * FROM with_target;

dnoeth 4628 posts Joined 11/04
19 Sep 2013

SQL Data Manipulation Language
 

Chapter 1: The SELECT Statement

WITH [RECURSIVE] Request Modifier

Rules and Restrictions for the WITH and WITH RECURSIVE Request

Modifiers
 
 

You cannot specify a WITH or WITH RECURSIVE request modifier in the definitions of

any of these database objects:

• Views and recursive views

• Triggers

• Stored procedures

• Derived tables

Don't ask me why it's restricted and i have to admit i never noticed it before. I only needed WITH within a cursor definition and this is allowed since TD13.

You might ask suppport for the reason and/or open an Enhancement Request.

 

Dieter

Dieter

M.Saeed Khurram 544 posts Joined 09/12
19 Sep 2013

Many thanks Dieter for the valuable information!
 
 

Khurram

stahengik 15 posts Joined 10/14
14 Nov 2014

Hi,
I have this query, which executes fine.

INSERT INTO workdb.delq_pool_final2
    SELECT 
	    pool_id,
	         age,
	        SUM(prin_loan_bal)/SUM(orig_pool_amt) AS  pool_factor,
	        SUM  (net_loss)/SUM(orig_pool_amt) AS cu_net_losses,
	        SUM(unit_loss)/SUM(orig_unit_amt) AS cu_unit_losses,
	        SUM (dq_31)/SUM(prin_loan_bal) AS delinq31_ratio,
	         
	        SUM (net_loss/p.factor)/SUM(orig_pool_amt) AS dscu_net_losses,
	        SUM (unit_loss/p.factor)/SUM(orig_unit_amt) AS dscu_unit_losses,
	        SUM (dq_31/p.factor)/SUM(prin_loan_bal) AS dsdelinq31_ratio
	         
        FROM 	 workdb.delq_pool_final F LEFT JOIN workdb.pool_factors P 	ON  season = (MOD((EXTRACT(MONTH FROM cut_date) + age - 1),12) +1)
        GROUP BY 1,2;

but when I try to try to execute it as part of macro it results in error: 3706: expecting somthing between '(' and MOD keyword. 
What's the reason and reamedy for this?

Raja_KT 1246 posts Joined 07/09
14 Nov 2014

How is your macro structure?
You could have made a new topic :) for ease of reference.
The topic above is  "use-of-cte-within-stored-procedures".

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 Nov 2014

Your MOD function uses ODBC syntax which is automatically rewritten when you connect using ODBC, the "Disable Parsing" option has not been checked and the query is a DML statement.
 
When you put it in a SP there's no rewrite and it fails.

(((EXTRACT(MONTH FROM cut_date) + age - 1) MOD 12) +1)

 

Dieter

stahengik 15 posts Joined 10/14
17 Nov 2014

Thanks a lot!!
It works now!!

You must sign in to leave a comment.