IF is not SQL function that works
in ExecuteSQL(). However, SUM(CASE ....)
Sent from miPhone
If I display negative numbers for returns I can do this but I'm sure I can do better.
ExecuteSQL(" SELECT SaleLineItemName, SUM(SaleLineQuantity) FROM SaleLine JOIN Sale ON Sale.SaleUUID = SaleLine.SaleUUID WHERE Sale.SaleUUID = ? OR Sale.OriginalSaleUUID = ? GROUP BY SaleLineItemName"; ""; ""; Report::gSelectedUUID; Report::gSelectedUUID)
Is there a way to use a repetition field together with GROUP BY? I've tried SL.SaleLineStockName but it doesn't work.
Now that I can see the images/tables (not available on email) you have posted, I might really consider making a calculated field based on the relationship SaleUUID and the make the quantity with your conditional.
isEmpty(relationship::OriginalSaleUUID) ; <<the value is positive>> ; <<the value is negative>>)
Then SUM from there.
1 of 1 people found this helpful
For 1st post
SUM(CASE WHEN OriginalSaleUUID IS NULL THEN SaleLineQuantity ELSE 0 END),
SUM(CASE WHEN OriginalSaleUUID IS NOT NULL THEN SaleLineQuantity ELSE 0 END)
FROM SaleLine JOIN Sale USING(SaleUUID)
GROUP BY SaleLineItemName
But do you need WHERE for only one UUID?
I need just for one UUID.
ORDER BY (the sorting...) is always the last
- at least in ExecuteSQL() -
The result set is not records from any table, GROUPed values. So you need aggregate function for SaleLineUUID.
You should choose one of SaleLineUUID in each GROUP, for example in the 1st post Item A has 1,3,4 and Item B has 2.
If you use MIN(SaleLineUUID), ORDERed result is A then B.
If you use MAX(SaleLineUUID), B then A.