0

I have a query in teradata where i am trying to get the week number from a specific date in the format yyyymmdd (20160201). We have a calendar table (not the teradata one because we count weeks slightly differently) which allows you to join the date and export the results

When i run the query with static dates so for the example below runs fine

FROM table_main AL1
 JOIN cal_table cal
  ON  AL1.run_date = cal.cal_dateyyyymmdd
WHERE AL1.run_date >= 20160201
AND AL1.run_date < 20160220

When i try to generalize the statement to the previous week

FROM table_main AL1
 JOIN cal_table cal
  ON  AL1.run_date = cal.cal_dateyyyymmdd
WHERE AL1.run_date >= CAST(CAST(((DATE-DAYOFWEEK(DATE)-5) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT)
AND AL1.run_date < CAST(CAST(((DATE-DAYOFWEEK(DATE)+ 1) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT)

I get the error

SELECT Failed. 3706: Syntax error: expected something between ')' and '-'.

Has anyone ever seen this before?

John Smith
  • 2,448
  • 7
  • 54
  • 78

2 Answers2

2

DAYOFWEEK is not an existing Teradata function, it's ODBC syntax, which is sometimes (apparantely not in your case) automatically translated by the ODBC-driver to valid Teradata SQL. It will work when you use the correct syntax {fn DAYOFWEEK(DATE_)}, but imho you should always avoid those ODBC functions (check Disable Parsing in the ODBC options), because it will fail when you submit the same query using JDBC/.NET/CLI.

And your calculation is overly complicated, try to avoid expensive typecasts date/string:

WHERE AL1.run_date >= CAST(NEXT_DAY(DATE-13, 'mon') AS INT) + 19000000
AND AL1.run_date < CAST(NEXT_DAY(DATE-7, 'sun') AS INT) + 19000000

NEXT_DAY(..., 'mon') -- find the next monday
CAST(... AS INT) -- returns YYMMDD
... + 19000000 -- to match your date key
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks @dnoeth. this is what i was looking for. I also found that i can remove the table completely and use `week(cast((run_date - 19000000 ) AS DATE)) as week_num` which seems to work – John Smith May 19 '16 at 10:48
  • @JohnSmith: `week` is another ODBC function and I doubt it's always returning the correct number. If you need ISO weeks `WEEKNUMBER_OF_YEAR` might be what you want, there's an `ISO` option. – dnoeth May 19 '16 at 11:00
0

you can't put the (AS) in the (where clause) It's accepted only in the query columns.

Mohamed-aaa-Omran
  • 31
  • 2
  • Hi, Thanks for the quick response. Im not sure i follow. If i use`CAST(CAST(((DATE-DAYOFWEEK(DATE)-5) (FORMAT 'YYYYMMDD')) AS CHAR(8)) AS INT)` and don't join on to the other table on it, it works fine. Is it just not allowed when you want to join another table? – John Smith May 19 '16 at 08:33
  • use it like this: FROM table_main AL1 JOIN cal_table cal ON AL1.run_date = cal.cal_dateyyyymmdd WHERE AL1.run_date >= CAST(CAST(((DATE-DAYOFWEEK(DATE)-5) (FORMAT 'YYYYMMDD')) AS CHAR(8)) ) AND AL1.run_date < CAST(CAST(((DATE-DAYOFWEEK(DATE)+ 1) (FORMAT 'YYYYMMDD')) AS CHAR(8)) INT) – Mohamed-aaa-Omran May 19 '16 at 08:52
  • Hi @Mohamed, Could you explain to me what you changed as it looks identical to the problem statement in my second chunk of code – John Smith May 19 '16 at 09:00
  • sorry @JohnSmith i missed understanding, it's my first time to see (CAST) statement. i'll delete my answer. – Mohamed-aaa-Omran May 19 '16 at 10:16