Error-Missing/Invalid SQL statement'E(3706):Syntax error

Database
Enthusiast

Error-Missing/Invalid SQL statement'E(3706):Syntax error

Hi All,

I'm trying to run the following procedure and I'm getting the following error.Could anyone please help me.

Thanks in advance-
Syed

-DROP PROCEDURE Proc_load_quater
CREATE PROCEDURE Proc_load_quater()
Begin
DECLARE LowerBoundDate DATE;
DECLARE UpperBoundDate DATE;
DECLARE IteratingDate DATE;
SET LowerBoundDate = 1/1/2007;
SET LowerBoundDate = 1/1/2017;
SET Iteratingdate = LowerBoundDAte;
label: while Iteratingdate <= UpperBoundDate
DO
insert into TEST_LKP_QTR
(
Quarter_ID
,Quarter_NM
,Days_Qtr_Num
,Prev_Qtr_Qtr_ID
,Last_Yr_Qtr_Id
,Year_ID
)
Values
(
CASE
WHEN Month(:Iteratingdate) = 1 OR Month(:Iteratingdate) = 2 OR Month(:Iteratingdate) = 3
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (1 AS VARCHAR))
WHEN Month(:Iteratingdate) = 4 OR Month(:Iteratingdate) = 5 OR Month(:Iteratingdate) = 6
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (2 AS VARCHAR))
WHEN Month(:Iteratingdate) = 7 OR Month(:Iteratingdate) = 8 OR Month(:Iteratingdate) = 9
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (3 AS VARCHAR))
WHEN Month(:Iteratingdate) = 10 OR Month(:Iteratingdate) = 11 OR Month(:Iteratingdate) = 12
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (4 AS VARCHAR))
ELSE
END
,
CASE
WHEN Month(:Iteratingdate) = 1 OR Month(:Iteratingdate) = 2 OR Month(:Iteratingdate) = 3
THEN CAST (Year(:Iteratingdate) AS varchar) + 'Q1'
WHEN Month(:Iteratingdate) = 4 OR Month(:Iteratingdate) = 5 OR Month(:Iteratingdate) = 6
THEN CAST (Year(:Iteratingdate) AS varchar) + 'Q2'
WHEN Month(:Iteratingdate) = 7 OR Month(:Iteratingdate) = 8 OR Month(:Iteratingdate) = 9
THEN CAST (Year(:Iteratingdate) AS varchar) + 'Q3'
WHEN Month(:Iteratingdate) = 10 OR Month(:Iteratingdate) = 11 OR Month(:Iteratingdate) = 12
THEN CAST (Year(:Iteratingdate) AS varchar) + 'Q4'
ELSE
END
,
datediff(day,:Iteratingdate,dateadd(month,3,:Iteratingdate))
,
CASE
WHEN (Month(:Iteratingdate) = 1 OR Month(:Iteratingdate) = 2 OR Month(:Iteratingdate) = 3)
Then CAST (YEAR(DATEADD(YEAR,-1,:Iteratingdate)) AS varchar) + (CAST (4 AS VARCHAR))
WHEN (Month(:Iteratingdate) = 4 OR Month(:Iteratingdate) = 5 OR Month(:Iteratingdate) = 6)
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (1 AS VARCHAR))
WHEN (Month(:Iteratingdate) = 7 OR Month(:Iteratingdate) = 8 OR Month(:Iteratingdate) = 9)
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (2 AS VARCHAR))
WHEN (Month(:Iteratingdate) = 10 OR Month(:Iteratingdate) = 11 OR Month(:Iteratingdate) = 12 )
Then CAST (Year(:Iteratingdate) AS varchar) + (CAST (3 AS VARCHAR))
ELSE
END
,
CASE
WHEN Month(:Iteratingdate) = 1 OR Month(:Iteratingdate) = 2 OR Month(:Iteratingdate) = 3
Then CAST (YEAR(DATEADD(YEAR,-1,:Iteratingdate)) AS varchar) + (CAST (1 AS VARCHAR))
WHEN Month(:Iteratingdate) = 4 OR Month(:Iteratingdate) = 5 OR Month(:Iteratingdate) = 6)
Then CAST (YEAR(DATEADD(YEAR,-1,:Iteratingdate)) AS varchar) + (CAST (2 AS VARCHAR))
WHEN Month(:Iteratingdate) = 7 OR Month(:Iteratingdate) = 8 OR Month(:Iteratingdate) = 9)
Then CAST (YEAR(DATEADD(YEAR,-1,:Iteratingdate)) AS varchar) + (CAST (3 AS VARCHAR))
WHEN Month(:Iteratingdate) = 10 OR Month(:Iteratingdate) = 11 OR Month(:Iteratingdate) = 12)
Then CAST (YEAR(DATEADD(YEAR,-1,@Iteratingdate)) AS varchar) + (CAST (4 AS VARCHAR))
END
,
CASE
WHEN Month(:Iteratingdate) = 1 OR Month(:Iteratingdate) = 2 OR Month(:Iteratingdate) = 3
THEN CAST (Year(:Iteratingdate) AS varchar)
WHEN Month(:Iteratingdate) = 4 OR Month(:Iteratingdate) = 5 OR Month(:Iteratingdate) = 6
THEN CAST (Year(:Iteratingdate) AS varchar)
WHEN Month(:Iteratingdate) = 7 OR Month(:Iteratingdate) = 8 OR Month(:Iteratingdate) = 9
THEN CAST (Year(:Iteratingdate) AS varchar)
WHEN Month(:Iteratingdate) = 10 OR Month(:Iteratingdate) = 11 OR Month(:Iteratingdate) = 12
THEN CAST (Year(:Iteratingdate) AS varchar)
ELSE
END
);
SET Iteratingdate = ADD_MONTHS(Iteratingdate, 3);
END WHILE;
END;

--------------------------------------------ERROR-----------------------------------------------------

SPL1027:E(L82), Missing/Invalid SQL statement'E(3706):Syntax error: expected something between the 'WHEN' keyword and the 'Month' keyword. '.
10 REPLIES
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

First try to identify from which block the error is happening.Then check the syntax.

Thanks.
SN
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

hi,

rewrite your CASE stmts as below:

.
.
.
CASE WHEN EXTRACT(Month FROM Iteratingdate) IN ('1','2','3')
Then EXTRACT(Year FROM Iteratingdate) + 1
ELSE(
CASE WHEN EXTRACT(Month FROM Iteratingdate) IN ('4','5','6')
Then EXTRACT(Year FROM Iteratingdate) + 2
ELSE(
CASE WHEN EXTRACT(Month FROM Iteratingdate) IN ('7','8','9')
Then EXTRACT(Year FROM Iteratingdate) + 3
ELSE(
CASE WHEN EXTRACT(Month FROM Iteratingdate) IN ('10','11','12')
Then EXTRACT(Year FROM Iteratingdate) + 4
ELSE NULL
END) END) END) END
,
.
.
.

HTH,
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

Thanks all for your help. I need the teradata function similar to DATEADD to add/subtract a year from date.

Syed-
Teradata Employee

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

Hello,

The best thing you can have for this is, UDF. Alternatively, you can try with EXTRACT and/or INTERVAL.

Regards,

Adeel
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

Thanks ...!

I tried using Extract its not working may be may be I'm doing some syntax error as I'm super new to teradata.Could anyone help me in changing the following case statement to teradata specially the DATEADD function.

CASE
WHEN extract (Month from (:iteratingdate)) = 1 OR extract (Month from(:iteratingdate)) = 2 OR extract (Month from(:iteratingdate)) = 3
Then CAST extract (YEAR from ((YEAR,-1,:IteratingDate)) AS varchar) || (CAST (4 AS VARCHAR))
WHEN extract (Month from(:iteratingdate)) = 4 OR extract (Month from(:iteratingdate)) = 5 OR extract (Month from(:iteratingdate)) = 6
Then CAST extract (Year(:iteratingdate)) AS varchar) || (CAST (1 AS VARCHAR))
WHEN extract (Month from(:iteratingdate)) = 7 OR extract (Month from(:iteratingdate)) = 8 OR extract (Month from(:iteratingdate)) = 9
Then CAST extract (Year(:iteratingdate)) AS varchar) || (CAST (2 AS VARCHAR))
WHEN extract (Month from(:iteratingdate)) = 10 OR extract (Month from(:iteratingdate)) = 11 OR extract (Month from(:iteratingdate)) = 12
Then CAST extract (Year(:iteratingdate)) AS varchar) || (CAST (3 AS VARCHAR))
END

Thanks-
Sohail.
SN
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

hi,

try to replicate the EXTRACT stmts as in above sample. and can you be specific about what you want to do with the date arithmetic in the CASE stmts - I see you concatenating '||' with year and not adding...

Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

If you see below in then statement im trying to subtract one year from the date and i'm not sure that the syntax is right.

WHEN extract (Month from (:iteratingdate)) = 1 OR extract (Month from(:iteratingdate)) = 2 OR extract (Month from(:iteratingdate)) = 3
Then CAST extract (YEAR from (ADD_MONTHS(:IteratingDate,-12)) AS varchar) || (CAST (4 AS VARCHAR))

Thanks-
Sohail
SN
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

this should work.....

....
THEN EXTRACT(YEAR FROM (ADD_MONTHS(:ITERATNGDATE,-12))) || ....
...
I am not sure if you are trying to add '4' to the year or aoncatenate (||) it as varchar and also the cat is incorrect that you didnt specify the data length for varchar.
Enthusiast

Re: Error-Missing/Invalid SQL statement'E(3706):Syntax error

Thanks that really helps ...!What are the teradata function which are similar to DATEPART and DATEDIFF .

SS-