Question

Delivery Statistics - Custom Field Based on Date

  • 13 August 2021
  • 3 replies
  • 103 views
E
Rank
Userlevel 3
Badge +6

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

This topic has been closed for comments

3 replies

durette
Rank
Userlevel 7
Badge +18

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.

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
J
Rank
Userlevel 6
Badge +14

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)

E
Rank
Userlevel 3
Badge +6

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