Question on query for template field in APS

Patrick Herr

• November 12, 2021 14:11
• Edited

Hello,

in APS I added a template field that gives information regarding the progress of the work order (similar to production improvement report, but on one field).

The template field contains the resource ID for the last routing position where a time receipt exists. Additionaly the field contains the sum of all posted items on for the routing position.

So like that for example: BGL01-100 St. (Last posting on resource BGL01, 100 pieces have been posted in total)

The field worked fine for several weeks, now suddenly I get an error "single line query returns more than one line" 

Has anybody got an idea what is the reason and what I should change in the query?

Thanks for your answer in advance!

Patrick

SELECT T30."APLATZ_ID"||'-'||CAST(T30."SUM_MENGE_GUT" AS INT)||'St.'
FROM 
       (SELECT distinct T10."BELNR_ID", T10."BELPOS_ID", T10."letzte Afo",T20."APLATZ_ID",sum(T20."MENGE_GUT") AS "SUM_MENGE_GUT"
       FROM
       (SELECT 
        T0."BELNR_ID"
       ,T0."BELPOS_ID"
       ,max(T0."POS_ID") AS "letzte Afo"
       from BEAS_ARBZEIT T0
       INNER JOIN BEAS_FTPOS T1
       ON T0."BELNR_ID" =T1."BELNR_ID" AND T0."BELPOS_ID" =T1."BELPOS_ID"
       WHERE T1."ABGKZ"!='J'
       GROUP BY T0."BELNR_ID", T0."BELPOS_ID")T10

LEFT JOIN BEAS_ARBZEIT T20
ON T10."BELNR_ID"=T20."BELNR_ID" AND T10."BELPOS_ID" = T20."BELPOS_ID" AND T10."letzte Afo" = T20."POS_ID"
GROUP BY T10."BELNR_ID", T10."BELPOS_ID", T10."letzte Afo",T20."APLATZ_ID")T30
WHERE T30."BELNR_ID"="BEAS_FTPOS"."BELNR_ID" AND T30."BELPOS_ID"="BEAS_FTPOS"."BELPOS_ID"

Vote

0

• 

  Martin Heigl

  • November 12, 2021 14:32

  Hi Patrik

  Note: Last operation = max(BEAS_FTAPL."SortId")

  Reason can be a time receipt on parallel position

  please try it with add the condition T0."RESOURCENPOS_ID"=0

   

  regards

  Martin
• 

  Patrick Herr

  • November 15, 2021 08:56

  Hello Martin,

  thank you for pointing me in the right direction.

  The problem was that in a routing position a resource group was determined as resource and not a single resource. So time receipts on the same routing position can be done on more than on resource leading to the query resulting in several lines.

  Thanks!

  Patrick

    

Please sign in to leave a comment.