1

I want to return the available stock value through a query where the amount on orders is deducted from total stock and where amount on order = NULL is transformed to 0. So that if there are no items open on orders, it simply shows the total stock. Otherwise, it needs to deduct several items on stock.

Disclaimer: Relatively new to more advanced queries, so any explanation on what I'm doing wrong here would be incredibly helpful in the future!

Currently trying to query SQL Server, I've gotten as far that i can get most of the query to work except for the ISNULL function

SELECT dbo.[Items].[Productcode Customer], 
       dbo.[Items].Description, 
       dbo.[Items].[Description 2],
       dbo.[Items].[EAN Code], 

       (SELECT SUM(dbo.[Entry].[Quantity]) 
        FROM dbo.[Entry] 
        WHERE dbo.[Logistic Items].No_ = dbo.[Entry].[Item SKU No_]) -

       (SELECT SUM(dbo.[Order Lines].[Quantity (Base)]) 
        FROM dbo.[Order Lines] 
        WHERE dbo.[Items].No_ = dbo.[Order Lines].[Item No_] 
          AND dbo.[Order Lines].[Document No_] LIKE '%UIT%') AS Quant_avail

FROM dbo.[Items] 
LEFT JOIN dbo.[Order Lines] ON dbo.[Items].No_ = dbo.[Order Lines].[Master Item No_]

WHERE dbo.[Items].[Customer Code] = 'KL0134'

GROUP BY
    "Items"."No_",
    "Items"."Productcode Customer",
    "Items".Description,
    "Items"."Description 2",
    "Items"."EAN Code"

The WHERE function is to indicate that only outbound orders are to be deducted.

I'm getting all fields I want but the Quant_avail column is only showing NULL. Which kinda makes sense, because there are no items on placed on orders. All i need to do now is convert the NULL field to 0 at: 

(SELECT SUM(dbo.[WMS Order Lines].[Quantity (Base)]) 
 FROM dbo.[WMS Order Lines] 
 WHERE dbo.[Logistic Items].No_ = dbo.[WMS Order Lines].[Master Item No_] 
   AND dbo.[WMS Order Lines].[Document No_] LIKE '%UIT%') AS Quant_avail
Parfait
  • 104,375
  • 17
  • 94
  • 125
Niek
  • 11
  • 1
  • Interestingly, parts of this query does not error out like the missing table reference to `dbo.[Logistic Items]` in first subquery. Additionally, the arithmetic expression of the subqueries should be placed in `GROUP BY` as well since they are non-aggregated columns in top level query. – Parfait Jan 28 '19 at 16:48
  • Just a performance note, do not group by varchar columns. Group by item id and then join the Items table. – Karel Frajták Jan 28 '19 at 17:09
  • @Niek . . . I suspect there is an easier way to write the query. Consider asking another question with sample data, desired results, and an explanation of the logic you want. – Gordon Linoff Jan 28 '19 at 18:55

1 Answers1

0

Just call ISNULL with your original Quant_avail as a parameter:

ISNULL(
    (SELECT SUM(dbo.[WMS Order Lines].[Quantity (Base)]) 
    FROM dbo.[WMS Order Lines] 
    WHERE dbo.[Logistic Items].No_ = dbo.[WMS Order Lines].[Master Item No_] 
    AND dbo.[WMS Order Lines].[Document No_] LIKE '%UIT%')
, 0) AS Quant_avail
Karel Frajták
  • 4,389
  • 1
  • 23
  • 34
  • 1
    Wow, this works. I've tried placing the ISNULL function in front of this section before, but I had an error...Thank you so much though! – Niek Jan 29 '19 at 07:28