cancel
Showing results for 
Search instead for 
Did you mean: 

Help with 2 queries - sales orders greater than a specific "in stock" value

richard_thurlow
Participant
0 Kudos

Hi,

I was hoping that you could help me with two queries I am trying to write.

I want to have a query that I can run, that will prompt me for a sales person and then display all customers for that sales person that have a sales order which has available stock on it that comes to equal to or greater than $100 that isn't already on a picklist.

Here is what I have currently:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0 

INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE (T1.[PickStatus] = 'N') AND (T1.[OpenQty] > '0') and T2.[SlpName] = [%0]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName]

I need to add in the WHERE clauses to indicate that the sales order needs to be have "in stock" product of over $100.

By "in stock" - this would mean Available to Release, ensuring that there is enough stock in the OWHS.[WhsCode] = 01. Main that isn't already on picklists.

I also want to have a similar query that can run - but this time I only want to have the following fields:

SELECT T0.[CardCode], T0.[CardName], sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

This way it will total up the available to release stock across multiple orders toascertain if it is above the $100, considering that should there be multiple sales orders below the $100 threshold, it wouldn't show up in the first query - so could be missed.

Let me know if the above needs clarification.

Any help would be much appreciated.

Regards

Rick

Accepted Solutions (0)

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query based on your last reply.

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = (select ta.slpname from OSLP ta where ta.slpname = [%0])

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100) X

GROUP BY X.CardCode

      ,X.CardName

Thanks

richard_thurlow
Participant
0 Kudos

Hi,

This also returns no results.

Thanks

Rick

former_member211473
Contributor
0 Kudos

Hi Richard

you can try this

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], sum ( T1.[OpenQty] * T1.[Price] ) as '$ value' FROM [dbo].[ORDR]  T0 INNER JOIN [dbo].[RDR1]  T1 ON T0.DocEntry = T1.DocEntry INNER JOIN [dbo].[OSLP]  T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode   AND T1.WhsCode = T4.WhsCode WHERE (T1.[PickStatus] = 'N') AND (T1.[OpenQty] > '0') and T2.[SlpName] = [%0] and ( T4.[OnHand] *  T1.[Price] ) > 10 GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName] , T1.[openqty] , T1.[Price]

Regards

Ranu

richard_thurlow
Participant
0 Kudos

Hi Ranu,

Thank you for this.

However - when I run this query it doesn't work as intended.

I am looking for a query which will have one line per sales order, and check to see if the sum of the product that can be supplied is over $100.

Your query looks like it is displaying one line per each product.

Regards

Rick

former_member211473
Contributor
0 Kudos

Hi Rick

If u have single item in SO, it shows output according to sales order and if u have more than or equal to 2 item the output is again according to sales order  but for individual item in a row.

Thanks

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Your query is already almost complete. Just add a couple of lines:

SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

    , T3.OnHand * T3.AvgPrice AS StockTotalValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = [%0]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING (T3.OnHand * T3.AvgPrice) >= 100

Then once you have your detailed data, you can use an encapsulating query to get the totaled data:

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , T3.OnHand * T3.AvgPrice AS StockTotalValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = [%0]

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING (T3.OnHand * T3.AvgPrice) >= 100) X

GROUP BY X.CardCode

      ,X.CardName

Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

Thank you for the reply.

I have entered this into the SAP query window and it is coming up with Incorrect Syntax near "THURLOW" (I am looking up my customers).

Should we declare a variable at the start of the query?

Any ideas on this?

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Considering that the B1 query variable ([%0]) is now in the embedded query, you might be right.

You can test the query in SQL Management Studio to see if it works for you otherwise.

For the variable to work in B1, you would have to add something like this at the top:

DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]

and replace the [%0] variable in the WHERE clause with @SalesPersonName

My own B1 version is too old to understand this syntax, so I cannot test it myself. You will probably have to play around with it, and/or search the forum for examples.

Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

OK this is what I've got at the moment:

DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , T3.OnHand * T3.AvgPrice AS StockTotalValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING (T3.OnHand * T3.AvgPrice) >= 100) X

GROUP BY X.CardCode

      ,X.CardName

But when I put it into the SQL Management Studio - it comes up with an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name '%0'

Any further thoughts on this?

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Like I said, I can't test this myself, but I took a look at another post with a similar construction.

Could you please change this:

DECLARE @SalesPersonName AS NVARCHAR(32) /* SELECT SlpName FROM OSLP WHERE SlpName */ = [%0]


to this:

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = SELECT SlpName FROM OSLP WHERE SlpName = '[%0]'

Oh, and test this in B1, not SQL Management Studio.

Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

Thank you for the reply.

I have done as you have asked and this is the error message:

"The getdate function requires 0 arguments"

Back to the drawing board

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Just found another alternative syntax, that surprisingly even works on my system. Please try this:

/* SELECT * FROM OSLP sp */


DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = /* sp.SlpName */ [%0]


and then the rest of the query.


Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

I have tried this - but it didn't work.

Although - I have added ' ' around the [%0] and it now asks me for the sales person name.

The query runs - but doesn't bring up any results.

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Ok, but not getting an error is half the battle. Now we only need to check why the query is returning an empty result set.

First lets check that the parameter is good. When you ran the query, did you choose from the list, or did you type in a value ?

richard_thurlow
Participant
0 Kudos

I choose from the list.

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Ok, lets (double) check a few things, because when I run the following query on my system, I get results:


/* SELECT * FROM OSLP sp */

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = /* sp.SlpName */ '[%0]'

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , T3.OnHand * T3.AvgPrice AS StockTotalValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING (T3.OnHand * T3.AvgPrice) >= 100) X

GROUP BY X.CardCode

      ,X.CardName

  1. Is this the exact query you are running?
  2. If so, and you are not getting results, can you manually find a case that should show up in the result set, for the chosen sales person ?
  3. If you can find such a case, manually / by eye, check that the 4 different requirements are really true:

    a. Is the Pick Status 'No' or False ?
    b. Is the Open Quantity larger than 0 (zero)
    c. Is the Sales Person for this Sales Order really the one you chose ?
    d. Does the Actual Stock in the given warehouse times the cost price in the given warehouse really exceed 100 ?
  4. If this is the exact query you are running, are you really not getting any results, or are there results, but they are not what you expected ?
  5. Does the detailed query return any results?:


/* SELECT * FROM OSLP sp */

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = /* sp.SlpName */ '[%0]'

SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , T3.OnHand * T3.AvgPrice AS StockTotalValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING (T3.OnHand * T3.AvgPrice) >= 100

Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

1. Yes - this is the exact query I am running

2. Yes found one

3. I have found an example, there is an order which has come onto SAP today - but hasn't been put onto picklist. All of the product on this order is in stock and the total is > 300.

a Pick status is N

b. Yes, this is a fresh order with no closed lines

c. Yes

d. Yes

4. This is returning no results whatsoever.

5. This doesn't return any results either.

Regards

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Time for a triple check. Please don't be offended, I certainly don't mean to imply that you are lying, but if the query is not throwing an error, then the only logical explanation is that the requirements you just checked (or that I understood you asked for) are not equal to the requirements as stated in the query. If we can determine where the two diverge, we can fix the query.

  • Question 3. c.: Did you check the given Sales Order document, and see which sales person has been determined ? So specifically NOT the BP Master data of the business partner of this specific sales order?
  • Question 3. d.: When you say: "All of the product on this order is in stock and the total is > 300", how did you check ? In other words, which fields in the B1 client did you look at to determine that the value was larger than 300 ?
    Reading over your question once more, it just came to mind, did you mean stock value per item, or total stock value of the complete order ?

Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

Thank you for all your efforts to get this working for me.

This is a screenshot from the sales order:

and here is a screenshot from the query list:

Since we last communicated - the example order I was looking at has now gone onto picklist.

As such I have another example - I put this onto SAP just the other day, and I am certain that all of te product on the order is in stock.

With regards to your last comment - I am wanting the query to look at the total stock value of the complete order. So for example... there could be 10 lines open on the order. Each line is worth $20. But only 6 lines are in stock (and available to ship - ie: not assigned to a picklist). But since 6x20 is $120 - then this result should show up in the query.

Hope this helps.

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

That last one is the answer I think. My query, as is, looks for a single item's stock to be worth more than 100. That is why you were getting an empty result set, none of the order's item's stock alone was worth more than 100.

Please replace the following bits in both queries:

, T3.OnHand * T3.AvgPrice AS StockTotalValue

becomes

, SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue

and


HAVING (T3.OnHand * T3.AvgPrice) >= 100

becomes

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100


Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

I'm sorry to say - but this is returning no results either.

Just checking this is what I am running:

/* SELECT * FROM OSLP sp */

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = /* sp.SlpName */ '[%0]'

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100) X

GROUP BY X.CardCode

      ,X.CardName

Regards

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

I see I missed something. Please remove the following highlighted bits:

...

SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , T3.OnHand

     , SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue

...

and


GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T3.OnHand, T3.AvgPrice


Regards,

Johan


richard_thurlow
Participant
0 Kudos

Hi Johan,

Unfortunately, there are no results

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

Hmm, could you please test something? Comment out this bit:

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)

by enclosing it in /* */, so you get:

/* HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0) */


If you still get nothing, please comment out:

T1.[PickStatus] = 'N'

If you then still get nothing, please comment out:

T1.[OpenQty] > '0'


Remember to also comment out the ANDs as needed.


Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

OK - so this is what I am running with currently:

/* SELECT * FROM OSLP sp */

DECLARE @SalesPersonName AS NVARCHAR(32)

SET @SalesPersonName = /* sp.SlpName */ '[%0]'

SELECT X.CardCode

      ,X.CardName

      ,SUM(X.[$ Value]) as '$ Value'

FROM

(SELECT T0.[DocNum]

     , T0.[DocDate]

     , T0.[CardCode]

     , T0.[CardName]

     , SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) AS DocumentTotalStockValue

     , sum( T1.[OpenQty]*T1.[Price]) as '$ Value'

FROM ORDR T0

     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

     INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode AND T1.WhsCode = T3.WhsCode

WHERE T1.[PickStatus] = 'N'

  AND T1.[OpenQty] > '0'

  AND T2.[SlpName] = @SalesPersonName

GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName]

/* HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0) */) X

GROUP BY X.CardCode

      ,X.CardName

This returns results for me

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Ok, that's good news. What sort of values are in the DocumentTotalStockValue column ?

richard_thurlow
Participant
0 Kudos

This is returning the total value of the sales orders open lines.

Johan_H
Active Contributor
0 Kudos

I meant, what sort of numbers. Larger or smaller than 100 ?

richard_thurlow
Participant
0 Kudos

Larger.

But this is taking into account items which aren't in stock.

Johan_H
Active Contributor
0 Kudos

Hi Rick,

If we analyze the part of the query that generates this number:

SUM(   <-- the sum statement adds up all values

ISNULL( <-- isnull makes sure that we get zero if there is no value at all

T3.OnHand  <-- through the inner join of OITW (item data in warehouse), this value tells us the stock of the given item in the given warehouse

,0)

*

ISNULL(

T3.AvgPrice <-- through the inner join of OITW (item data in warehouse), this value tells us the cost price of one unit of the given item in the given warehouse

,0)

)


In other words, even though it takes items into account which aren't in stock, that stock is zero. So for example we get:

Item in Order nr. 123456Stock in warehouseCost price in warehouseTotal stock value
THURLOW_GIZMO_01109.0090.00
THURLOW_GIZMO_021500.85120.00
THURLOW_GIZMO_0305.250.00
THURLOW_GIZMO_04416.7567.00
Total277.00


Because THURLOW_GIZMO_03 is not in stock, its stock value in this equation is zero, and therefore it is not responsible for this order to show up in the result set. The same goes for THURLOW_GIZMO_01 and THURLOW_GIZMO_04. In this case the stock value of THURLOW_GIZMO_02 is larger than 100, making the order show up in the result set.


So the question is, looking at it like this, is this the result you would expect, or am I still getting the question wrong ?


Regards,

Johan


richard_thurlow
Participant
0 Kudos

Hi Johan,

I don't think the above is accurate.

Here is an example:


Item in Order nr. 123456Stock in warehouseCost price in warehouseTotal stock value
THURLOW_GIZMO_01109.0090.00
THURLOW_GIZMO_02101.0010.00
THURLOW_GIZMO_0305.000.00
THURLOW_GIZMO_04015.000.00
Total100.00

Here is another example:


Item in Order nr. 654321Stock in warehouseCost price in warehouseTotal stock value
THURLOW_GIZMO_01209.00180.00
THURLOW_GIZMO_0201.000.00
THURLOW_GIZMO_0305.000.00
THURLOW_GIZMO_04015.000.00
Total180.00

Both of these orders should show in the report.

So - in the end, it is not a requirement for a line item to be over $100, but collectively over the sales order if it is over $100 it should show.

Thanks

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

You are right, I did not describe my logic correctly, but my intention was what you describe in your examples, and all three should show up in the list.

Incorporating your last example into the "technical" explanation, effectively we do this::

SUM(

(20 * 9.00) = 180.00 <-- THURLOW_GIZMO_01

+

(0 * 1.00) = 0.00 <-- THURLOW_GIZMO_02

+

(0 * 5.00) = 0.00 <-- THURLOW_GIZMO_03

+

(0 * 15.00) = 0.00 <-- THURLOW_GIZMO_04

)  = 180.00 <-- total warehouse value of the entire stock in the warehouse that was determined in the order row for the respective item, for the items in the order.


That is what we do with the HAVING statement that I asked you to comment out earlier:

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0)) >= 100


Same calculation once more, but this time with the requirement: >= 100


So according to this logic all three examples should show up. If some case does not show up, then it either does not conform to this logic, or the logic does not conform to your expectations.

Looking at the above, could you please check a live example, and see which it is ?


Regards,

Johan

richard_thurlow
Participant
0 Kudos

Hi Johan,

Just revisiting this thread - I have uncommented out the HAVING line - to be:

HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0))>=100) X

And it now doesn't return any results?

Regards

Rick

Johan_H
Active Contributor
0 Kudos

Hi Rick,

You need to not comment out the final ) X bit. So if you comment out the HAVING clause, best do it like this:

/*HAVING SUM(ISNULL(T3.OnHand,0) * ISNULL(T3.AvgPrice,0))>=100*/) X


Regards,

Johan