Skip to main content

Hello all, 

I am trying to create a custom field of a yes/no column in Delivery Statistics to show whether something was delivered “on time” after a certain number of days I am trying to use the field max_work_days_late in my query but I continue to get issues. 

NVL(&AO.Work_Time_Calendar_API.Get_Work_Days_Between((&AO.Site_API.Get_Dist_Calendar_Id(CONTRACT)),PLANNED_DELIVERY_DATE, REAL_DELIVERY_DATE),0)


IFS says this is the field it’s using but I am starting to think I can’t use this in my CASE statement, is there anyone that is better at SQL than I am to help me determine why I continue to get errors with my script for my custom field?
 

select CASE (NVL(&AO.Work_Time_Calendar_API.Get_Work_Days_Between((&AO.Site_API.Get_Dist_Calendar_Id(CONTRACT)),a.PLANNED_DELIVERY_DATE, a.REAL_DELIVERY_DATE),0)) WHEN >5 THEN "No" ELSE "Yes" END FROM  purchase_receipt_stat_uiv a where a.order_no = :order_no and a.line_no = :line_no and a.release_no = :release_no and a.receipt_no = :receipt_no


As always thanks for all the help. I’m sure this will be relatively easy for someone better versed  in SQL. 

 

Eli Williams

I didn’t review your query carefully, but I’d start by replacing your double quotes with single quotes.

Strings in Oracle use single quotes.

When Oracle sees double quotes, it’s going to think those are identifiers, like table or column names.

Hi @Eli_Williams ,

 

Check the query as bellow:

 

SELECT CASE

         WHEN t > 5 THEN

          'No'

         ELSE

          'Yes'

       END

  FROM (select NVL(&AO.Work_Time_Calendar_API.Get_Work_Days_Between((&AO.Site_API.Get_Dist_Calendar_Id(CONTRACT)),

                                                                    a.PLANNED_DELIVERY_DATE,

                                                                    a.REAL_DELIVERY_DATE),0) as t

        

          FROM purchase_receipt_stat_uiv a

         where a.order_no = :order_no

           and a.line_no = :line_no

           and a.release_no = :release_no

           and a.receipt_no = :receipt_no)

Hi guys, so after trying the code that I was given from @Janitha Jinarajadasa this is the error that I keep getting. Does anyone have any other ideas for this? Thanks for all your help again!

 

 

Terms & ConditionsCookie settings