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

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.

VenkateshP
Partner
Partner

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.

Screenshot.png

Thanks

Venkatesh