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.
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
group by s22.FSCL_WK_END_DT.
Help me out !!
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.
FROM attr_table s21
CROSS JOIN TIME s22
(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
GROUP BY s22.FSCL_WK_END_DT.
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 ?
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..
When you're on TD13.10 you can use a SQL UDF, otherwise simply use the calculation:
REPLACE FUNCTION WEEK_BEGIN(cdate DATE)
RETURNS NULL ON NULL INPUT
INLINE TYPE 1
cdate-(((cdate - DATE '0001-01-01')) MOD 7)
This returns the begin of the week, so
simply subtract one:
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;
select distinct date '2013-12-25'-dayofweek(date '2013-12-25'-2)-1 from fact;
12/22/2013 is the last week end date .