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 ()
b + 1
... which compiles without error.
However, when I execute this procedure, I receive this error message:
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
b + 1
Can it help you scenario?
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 ()
INSERT INTO with_target ( b )
SELECT 1 b
FROM with_source ) tmp
SELECT b + 1
SELECT 1 b
FROM with_source ) tmp ; END;
DELETE FROM with_target;
SELECT * FROM with_target;
SQL Data Manipulation LanguageChapter 1: The SELECT Statement
WITH [RECURSIVE] Request Modifier
Rules and Restrictions for the WITH and WITH RECURSIVE Request
ModifiersYou cannot specify a WITH or WITH RECURSIVE request modifier in the definitions of
any of these database objects:
• Views and recursive views
• 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.
I have this query, which executes fine.
INSERT INTO workdb.delq_pool_final2
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?
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".
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)