Skip to Content
Jun 18, 2021 at 09:00 AM

Show as a marker if a sales order has a line of 0 value


Hi Community,

I have two issues here I can't figure out and I'm hoping to get some pointers to work it out.

I have a query that looks at sales orders and gets info on the sales order only.
I now have a requirement for it to show some information based on the lines of the sales orders.
If any of the lines have an empty value, then mark the result as such.
While I have it working somewhat, it shows the sales order three times in the result if it has a 0 value line. Once with no 'Empty line' marker as the proper cost display, once with the 'Empty line' marker with proper cost display and once as the no 'Empty line' marker but incorrect cost (second point below). See highlighted line in attached, that is the only line per sales order I want to display.


Secondly to this, now I am querying the sales order lines, I have a calculation to show the pre-tax value of each sales order, but it is multiplying this by the number of empty lines in the sales order.
E.G. Sales order is £10, and has 5 lines, 3 of them are empty with no value. The value of the sales order is showing as £30 instead of £10!

Here is the query I have.

SELECT distinct T0.[DocNum], sum(T0.[DocTotal] - T0.[VatSum]) as 'Total', T0.[DocDate], T0.[NumAtCard], T2.[PrjName],  T3.[Name] as 'Contact Name', T0.[U_UR_Code],
when T4.[Price] < 0.01 then 'Empty Line'
LEFT OUTER JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID] 
LEFT OUTER JOIN OPRJ T2 ON T0.[Project] = T2.[PrjCode]
LEFT OUTER JOIN OCPR T3 ON T0.[CntctCode] = T3.[CntctCode]
LEFT OUTER JOIN RDR1 T4 on T0.[DocEntry] = T4.[DocEntry]
WHERE T0.[DocStatus] ='o' and  T0.[CardCode] ='[%0]'
and  T0.[U_CPO] IS NULL
GROUP BY T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T2.[PrjName], T3.[Name], T0.[U_UR_Code], T4.[Price]

Any advice you can offer for this?

This is on 9.3 if it has any relevance.



empty-line.png (5.8 kB)