02-27-2023 12:09 PM
I am trying to create a new derived view using Incorta SQL. I am not trying to do anything tricky that I am aware of. I have checked the syntax for the query against Spark SQL for the CASE statement and it doesn't look out of order. Here is the query. It fails on the first CASE statement. The error message isn't helpful. Any insights are appreciated.
Error Message:
INC_05020000: Invalid Query
due to Error converting CASE WHEN (d.Day_Name = 'Sunday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.sun_in,'HH:mm')))) WHEN (d.Day_Name = 'Monday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.mon_in,'HH:mm')))) WHEN (d.Day_Name = 'Tuesday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.tue_in,'HH:mm')))) WHEN (d.Day_Name = 'Wednesday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.wed_in,'HH:mm')))) WHEN (d.Day_Name = 'Thursday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.thur_in,'HH:mm')))) WHEN (d.Day_Name = 'Friday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.fri_in,'HH:mm')))) WHEN (d.Day_Name = 'Saturday') THEN (concat((date_format(d.Date,'yyyy-MM-dd')),' ',(date_format(s.sat_in,'HH:mm')))) ELSE NULL END into INCORTA formula
Query:
02-27-2023 12:18 PM
Looking at a thread in stackoverflow looks like to get the timestamp you might want to try something like this syntax:
select timestamp '2014-04-03 12:34:00'::time
but before that, does the CASE work if you concat the date w/ 'blah' or some other text? That will tell you if the date_format function is giving you trouble.
02-27-2023 01:29 PM
Hi Marc,
I copied this into SSMS and there is an error in the SQL on the EndDateTime case statement, youve simply misplaced a single quote on Saturday as illustrated in the attached image. Can you please fix, try rerunning and let us know if the same error persists?
03-01-2023 08:54 AM
I was able to correct all of my syntax errors and convert to Incorta functions and it runs.
On a separate note, I am trying to compare a date to the current date in the WHERE clause. I have tried getDate(), curdate(), current_date(), $currentDate, and Date(). None are recognized. It has to be something simple that I am missing.
03-01-2023 10:58 AM - edited 03-01-2023 11:04 AM
@marcpaige = it might just be an issue of matching a date to a date. Maybe you could share the formats so we can test it out.
For example, Incorta displays the following date for $currentDate: 3/1/23
What won't work:
date("3/1/23") = $currentDate
date("03/01/2023") = $currentDate
What will work (YYYY-MM-DD):
date("2023-03-01") = $currentDate