Firstsortedvalue issue

[maternmi]

Hello,

Every production order  (Prod_Order_Change_Reason) can have null or more then one change reasons, which will be shown as number. To each change reason I have a timestamp (Prod_Order_Change_Time). So I want to have the last change reason to each production order.

Here the abstract from my script:

PickUp_Reliability:

load

     Firstsortedvalue (Prod_Order_Change_Reason,-Prod_Order_Change_Time) as Prod_Order_LastChange_Reason,

     *

;

load

      ID_SOP,

      Prod_Order_Change_Reason,

      Prod_Order_Change_Time,

     Billing_Date,

     OC_Date

resident

tmpTab

;

By reloading the script I've got an error "invalid expression". I get it only when I add the load order Firstsortedvalue....

Does somebody knows what is wrong?

Thanks!

BR

Michael

[swuehl · Accepted solution]

FirstSortedValue() is a aggregation function and must be used with a group by statement in your script:

PickUp_Reliability:

load

      ID_SOP,

      Prod_Order_Change_Reason,

      Prod_Order_Change_Time,

     Billing_Date,

     OC_Date

resident

tmpTab

load

     ID_SOP,

     Firstsortedvalue (Prod_Order_Change_Reason,-Prod_Order_Change_Time) as Prod_Order_LastChange_Reason

RESIDENT PickUp_Reliability GROUP BY ID_SOP;