cancel
Showing results for 
Search instead for 
Did you mean: 

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

neilos
Active Participant
0 Kudos

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.

empty-line.png

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],
(case
when T4.[Price] < 0.01 then 'Empty Line'
end)
FROM ORDR T0  
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.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor

Hi,

Perhaps this works for you:

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]
,CASE 
   WHEN (select T4.[Price] from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry]) < 0.01 THEN 'Price is zero'
   WHEN (select T4.[Quantity] from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry]) = 0 THEN 'Quantity is zero'
   /*etc*/
   ELSE 'No issues'
  END AS [Issue]	
FROM ORDR T0  
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]
WHERE T0.[DocStatus] ='O' and  T0.[CardCode] ='[%0]'
and  T0.[U_CPO] IS NULL

Regards,

Johan

neilos
Active Participant
0 Kudos

Hi Johan,

Thanks for the response.

I can't get that to work as is.
It gives this error - 1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Must specify table to select from...
It doesn't like the 'SELECT T4.[Price]... '

So I modified it so the SELECT was as I have it initially, and the CASE followed your example, then added in the GROUP clause as it also didn't work without that part.
Then it gave me the same output as I have initially, which isn't a surprise as it is pretty much the same as I have before.

Should that query work as you have supplied it?

Johan_H
Active Contributor
0 Kudos

Hi,

Ok, it does that sometimes, when you use subqueries. Please try this:

/* select * from OCRD x */
DECLARE @CardCode AS NVARCAHR(20)
SET @CardCode = /* x.CardCode */ '[%0]'

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]
,CASE 
   WHEN (select T4.[Price] from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry]) < 0.01 THEN 'Price is zero'
   WHEN (select T4.[Quantity] from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry]) = 0 THEN 'Quantity is zero'
   /*etc*/
   ELSE 'No issues'
  END AS [Issue]	
FROM ORDR T0  
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]
WHERE T0.[DocStatus] ='O' and  T0.[CardCode] = @CardCode
and  T0.[U_CPO] IS NULL<br>

Regards,

Johan

neilos
Active Participant
0 Kudos

Thanks for your attention on this Johan.

Still not working, and I don't have the knowledge to figure out the issue.
Copied and pasted in a blank query;
I changed 'NVARCAHR' to 'NVARCHAR'
Removed the end <br>
Removed the 'SUM ... total' so that in turn removed the requirement to add a group clause.

I now get the error displayed
1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 'Received Alerts' (OAIB)

Johan_H
Active Contributor
0 Kudos

Hi,

Sorry, I wasn't paying attention. Please try this:

/* select * from OCRD x */
DECLARE @CardCode AS NVARCHAR(20)
SET @CardCode = /* x.CardCode */ '[%0]'

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]
,CASE 
   WHEN (select distinct 0 from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry] AND T4.[Price] < 0.01) = 0 THEN 'Order contains zero price'
   WHEN (select distinct 0 from RDR1 T4 where T0.[DocEntry] = T4.[DocEntry] AND T4.[Price] = 0) = 0 THEN 'Order contains zero quantity'
   /*etc*/
   ELSE 'No issues'
  END AS [Issue]	
FROM ORDR T0  
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]
WHERE T0.[DocStatus] ='O' and  T0.[CardCode] = @CardCode
and  T0.[U_CPO] IS NULL

Regards,

Johan

neilos
Active Participant

Hi Johan,

I really appreciate this.
Works wonderfully, I'll figure out what it is doing and learn from this example.
Many thanks.

Answers (0)