Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
2 years ago
Solved

Aggregation Error Message

I need to pull the company id related to the last billable stop in a series of stops(deliveries) where the following conditions are met: The order number is the same in both the stops table and...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cheid_4838 ,

    It looks like you're running into an issue with using an aggregate function ('MAX()') directly in the 'WHERE' clause, which is not permitted in SQL. Please update your SQL statement as below and check if it can work or not...

    SELECT s.cmp_id AS ReceiverID
FROM stops s (nolock)
JOIN (
    SELECT ord_hdrnumber, MAX(stp_sequence) AS max_sequence
    FROM stops
    WHERE stp_event IN ('LUL', 'LLD', 'DRL', 'HPL')
    GROUP BY ord_hdrnumber
) max_stops
ON s.ord_hdrnumber = max_stops.ord_hdrnumber
   AND s.stp_sequence = max_stops.max_sequence

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi cheid_4838 ,

It looks like you're running into an issue with using an aggregate function ('MAX()') directly in the 'WHERE' clause, which is not permitted in SQL. Please update your SQL statement as below and check if it can work or not...

SELECT s.cmp_id AS ReceiverID
FROM stops s (nolock)
JOIN (
    SELECT ord_hdrnumber, MAX(stp_sequence) AS max_sequence
    FROM stops
    WHERE stp_event IN ('LUL', 'LLD', 'DRL', 'HPL')
    GROUP BY ord_hdrnumber
) max_stops
ON s.ord_hdrnumber = max_stops.ord_hdrnumber
   AND s.stp_sequence = max_stops.max_sequence

Best Regards