Trouble with CASE in Incorta SQL view

[marcpaige]

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'

[marcpaige]

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]

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