Hi All,
Anyone please help me in writing a unix teradata.sql query for the below logic:
I need to update a table after running below two select statements and store it in a variable.
1. store the ouput from the below query in a variable as $(update_dt)
select calendar_date - (case day_of_week when 1 then 0
when 2 then 1
when 3 then 2
when 4 then 3
when 5 then 4
when 6 then 5
when 7 then 6
end ) as sunday_of_week
from Sys_calendar.calendar
where calendar_date = add_months(cast(CURRENT_DATE as date), - 12)
2. Store the ouput from the below query in a variable as $(last_dt)
SELECT CURRENT_DATE- INTERVAL '2' YEAR
After this, Need to pass the output to the below update statement:
update db.table3 c
set column1 = '$(update_dt)',
column2 = '$(update_dt)',
column3 = '$(update_dt)'
where(c.column4,c.column5,c.column6) in
(select a.column4,a.column5,a.column6 from
db.table1 a,
db.table2 b
where a.column4 = b.column4
and a.column5 = b.column5
and a.column6 = b.column6
and a.column1 < '$(last_dt)')
Note: Table1 and Table3 are same tables
Thanks!
$(update_dt) can be calculated without a Select:
TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12))
For $(last_dt) don't use INTERVALs as it will fail the next Feb. 29th:
ADD_MONTHS(CURRENT_DATE, -24)
And then there's no need for a Unix script anymore:
update db.table3 c set column1 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12)), column2 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12)), column3 = TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12)) where(c.column4,c.column5,c.column6) in (select a.column4,a.column5,a.column6 from db.table1 a, db.table2 b where a.column4 = b.column4 and a.column5 = b.column5 and a.column6 = b.column6 and a.column1 < ADD_MONTHS(CURRENT_DATE, -24))
Thanks a lot for your reply.
I am getting below error while running that update statement.
UPDATE Failed. 3706: Syntax error: expected something between '(' and the 'ADD_MONTHS' keyword.
I think for TD_SUNDAY function this error occurs, as we use Teradata 14.0
TD_SUNDAY(ADD_MONTHS(CURRENT_DATE,-12))
Please Advice. Also It will be great to update sunday day in 'yyyymmdd' format.
Thanks!
In TD14 you can use NEXT_DAY instead of TO_SUNDAY:
NEXT_DAY(ADD_MONTHS(CURRENT_DATE,-12)-7, 'SUN')
What's your Teradata release? Can't be TD14, because this introduced NEXT_DAY. Does SELECT NEXT_DAY(ADD_MONTHS(CURRENT_DATE,-12)-7, 'SUN') work?