cancel
Showing results for 
Search instead for 
Did you mean: 

Trouble with CASE in Incorta SQL view

marcpaige
Astronaut

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:

 

select
    s.Emp,
    s.Type,
    d.Date,
    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'))
    end as StartDateTime,
    case
        when (d.Day_Name = 'Sunday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.sun_out,'HH:mm'))
        when (d.Day_Name = 'Monday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.mon_out,'HH:mm'))
        when (d.Day_Name = 'Tuesday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.tue_out,'HH:mm'))
        when (d.Day_Name = 'Wednesday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.wed_out,'HH:mm'))
        when (d.Day_Name = 'Thursday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.thur_out,'HH:mm'))
        when (d.Day_Name = 'Friday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.fri_out,'HH:mm'))
        when ('d.Day_Name = Saturday'then concat(date_format(d.Date'yyyy-MM-dd'),' ', date_format(s.sat_out,'HH:mm'))
    end as EndDateTime,
    case
        when (d.Day_Name = 'Sunday'then int(s.sun_lunch) * 60.0
        when (d.Day_Name = 'Monday'then int(s.mon_lunch) * 60.0
        when (d.Day_Name = 'Tuesday'then int(s.tue_lunch) * 60.0
        when (d.Day_Name = 'Wednesday'then int(s.wed_lunch) * 60.0
        when (d.Day_Name = 'Thursday'then int(s.thur_lunch) * 60.0
        when (d.Day_Name = 'Friday'then int(s.fri_lunch) * 60.0
        when (d.Day_Name = 'Saturday'then int(s.sat_lunch) * 60.0
    end as LunchMins
from
    huntington.schedules s
    inner join dwhr.AllEmployees p on right('0000000' + s.Emp, 7= p.EMPLID
    cross join Date_Time.Date d
where
    d.Date between '2023-01-01'
    and '2023-03-31'
6 REPLIES 6

RADSr
Captain
Captain

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.  

 

-- IncortaOne@PMsquare.com --

ZachBreimayer
Employee
Employee

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?

ZachBreimayer_0-1677533358692.png

 

marcpaige
Astronaut

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. 

JoeM
Community Manager
Community Manager

@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