Syntax error: expected something between '(' and the 'current_date' keyword.

Database

Syntax error: expected something between '(' and the 'current_date' keyword.

I am faceing the below for my query. If i run the bold query separately it is running correct. 

Error : Syntax error: expected something between '(' and the 'current_date' keyword.

select s22.wk_dt

from attr_table s21

cross join time s22

where (s21.DC_LOC_ID,s22.wk_dt) IN (SEL A.DC, B.wk_dt FROM fact B

JOIN (SELECT DC_LOC_ID DC, MAX(INV_DT) inv_DT FROM INV_SCH GROUP BY DC_LOC_ID where pinv_dt < current_date-dayofweek(current_date)+1) A

ON B.DC_LOC_ID=A.DC

group by s22.FSCL_WK_END_DT.

Help me out !!

6 REPLIES
Senior Apprentice

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

There's no dayofweek function in SQL, you probably run this query from SQL Assistant using ODBC and the ODBC driver replaced it with valid Teradata SQL, but this is not done in Derived Tables or View definitions.

I never liked this and usually disable it by checking "Disable Parsing" in the ODBC driver options (or uncheck "Allow usage of ODBC QL extensions in queries" in SQLA->Options->Query in older SQLA versions).

But you got luck, in 13.10 there's a day_of_week function which returns the same value, it was renamed to td_day_of_week in 14 or 14.10.

Teradata Employee

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

try,

SELECT s22.wk_dt

FROM attr_table s21

CROSS JOIN TIME s22

CROSS JOIN

(SELECT day_of_week FROM sys_calendar.CALENDAR WHERE calendar_date = CURRENT_DATE) AS a

WHERE (s21.DC_LOC_ID,s22.wk_dt) IN (SEL A.DC, B.wk_dt FROM fact B

JOIN (SELECT DC_LOC_ID DC, MAX(INV_DT) inv_DT FROM INV_SCH GROUP BY DC_LOC_ID WHERE pinv_dt < CURRENT_DATE-day_of_week +1) A

ON B.DC_LOC_ID=A.DC

GROUP BY s22.FSCL_WK_END_DT. 

Best regards

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

above below function is working fine for all the week days, but it is not working on Monday.

My req is when my query run in any day of week(Monday to Sunday) it will pull last week end date and my week start on Monday and ends on Sunday.

Can some one help me to reslove this issue ?

current_date-dayofweek(current_date-2)-1

Enthusiast

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

Without looking at the data its difficult to understand what exactly is causing the issue. Share some sample data and expected output to better understand the problem in your query..

Senior Apprentice

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

When you're on TD13.10 you can use a SQL UDF, otherwise simply use the calculation:

REPLACE FUNCTION WEEK_BEGIN(cdate DATE)
RETURNS DATE
SPECIFIC week_begin_DA
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
cdate-(((cdate - DATE '0001-01-01')) MOD 7)

This returns the begin of the week, so

SELECT WEEK_BEGIN(DATE)-1

simply subtract one:

Re: Syntax error: expected something between '(' and the 'current_date' keyword.

Please find the below querys, it is not working on Monday and it is working on rest of days.

select distinct date '2013-12-23'-dayofweek(date '2013-12-23'-2)-1 from fact;

ans : 12/15/2013

select distinct date '2013-12-24'-dayofweek(date '2013-12-24'-2)-1 from fact;

12/22/2013

select distinct date '2013-12-25'-dayofweek(date '2013-12-25'-2)-1 from fact;

12/22/2013

12/22/2013 is the last week end date .