topic Re: Trouble with CASE in Incorta SQL view in Data & Schema Discussions

Trouble with CASE in Incorta SQL view

marcpaige — Mon, 27 Feb 2023 20:09:29 GMT

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'

Re: Trouble with CASE in Incorta SQL view

RADSr — Mon, 27 Feb 2023 20:18:54 GMT

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.

Re: Trouble with CASE in Incorta SQL view

ZachBreimayer — Mon, 27 Feb 2023 21:29:54 GMT

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?

Re: Trouble with CASE in Incorta SQL view

marcpaige — Wed, 01 Mar 2023 16:54:14 GMT

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.

Re: Trouble with CASE in Incorta SQL view

JoeM — Wed, 01 Mar 2023 19:04:51 GMT

@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

Re: Trouble with CASE in Incorta SQL view

marcpaige — Wed, 01 Mar 2023 19:36:35 GMT

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.

Re: Trouble with CASE in Incorta SQL view

VenkateshP — Tue, 04 Apr 2023 11:53:01 GMT

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