This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Docs Learn Support
Help
Docs Learn Support
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Trouble with CASE in Incorta SQL view

marcpaige
Astronaut

‎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:

 

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'
0 Kudos
6 REPLIES 6

RADSr
Captain
Captain

‎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.  

 

-- IncortaOne@PMsquare.com --

ZachBreimayer
Employee
Employee

‎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?

ZachBreimayer_0-1677533358692.png

 

marcpaige
Astronaut

‎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. 

0 Kudos

JoeM
Community Manager
Community Manager
In response to marcpaige

‎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
Copyright © 2024 Khoros, LLC