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:
03-01-2023 11:36 AM
Thanks, Joe. the field I am using on the left is an Incorta Date data type. The right side is an attempt to get the current date. I had hoped that I could use $currentDate but the Incorta SQL complains that the $currentDate is:
Unknown column $currentDate
I pushed the date check to an earlier part of the process but I would still like to know how to get the current date in an Incorta SQL table.
04-04-2023 04:53 AM
Hi Marc,
Incorta SQL supports postgresql functions so you should use current_date instead of current_date(). Also make sure that when you are using between both are the same formats as mentioned below for your reference.
Thanks
Venkatesh