This error: "Dynamic SQL Error SQL error code = -104 Invalid expression in the
select list (not contained in either an aggregate function or the GROUP BY
clause)" is returned by the following SQL:
SELECT
ReceiptID,
(
SELECT FIRST 1 S.Service FROM Service S
WHERE (S.ServiceID = RL.ServiceID)
ORDER BY RL.ReceiptLineID
) AS Service,
SUM(Qty*Cost) Price
FROM ReceiptLine RL
GROUP BY ReceiptID, Service
Additional information:
The table ReceiptLine is a detail (as in master-detail) table that stores the
line items relating to a purchase, with the fields ReceiptID and ServiceID
being foreign keys. The Service table stores product/service names.
Using the ReceiptLine and Service tables, the SQL needs to provide the total of
each receipt with only the first service name from the set of line items.
Is there an alternative way, using SQL only, to achieve the desired result?
