Use of CTE within stored procedures

Database
Enthusiast

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.

8 REPLIES
Enthusiast

Re: Use of CTE within stored procedures

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
Enthusiast

Re: Use of CTE within stored procedures

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;

Junior Contributor

Re: Use of CTE within stored procedures

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

Enthusiast

Re: Use of CTE within stored procedures

Many thanks Dieter for the valuable information!

Khurram
Enthusiast

Re: Use of CTE within stored procedures

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?

Enthusiast

Re: Use of CTE within stored procedures

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".

Junior Contributor

Re: Use of CTE within stored procedures

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)
Enthusiast

Re: Use of CTE within stored procedures

Thanks a lot!!

It works now!!