Attempting to filter rows based on value of subquery in select

Question

I'll preface this by stating I'm a beginner to SQL...

I'm trying to generate a list of parts that have not been charged out to orders within the year prior to the statement run date. Maybe I'm going about this the wrong way but I'm getting the quantity of the parts that have been charged to orders by using a sum function in a subquery nested within the select.

    SELECT b.binid AS 'Bin Location',
        i.partnumber AS 'Part No.',
        i.descrip AS 'Description',
        i.mfgcode AS 'Mfg Code',
        i.stocked AS 'Stocked',
        i.itemtype AS 'Part Type',
        i.onhand AS 'On Hand',
        (
            SELECT COALESCE(SUM(l.qtyreqd),0)
            FROM orderln l
            LEFT OUTER JOIN orders o
                ON l.orderid = o.orderid
            WHERE l.itemid = i.itemid
                AND o.ordertype = 'REPAIR'
                AND NOT o.STATUS = 'CANCELED'
                AND l.linetype = 'PART'
                AND l.chgdate BETWEEN '1/10/2018 12:00:00 AM'
                    AND '1/11/2019 12:00:00 AM'
            ) AS 'Usage'
    FROM item i
    LEFT OUTER JOIN iteminv b
        ON b.itemid = i.itemid
    WHERE i.shopid = 'FD TRACTOR'
        AND b.binid BETWEEN '00.01.00'
            AND '00.20.00'
        AND 'Usage' < 1
    ORDER BY b.binid ASC,
        i.partnumber ASC;

I expect to see a list like:

+---------------+-----------+---------------+-----------+-----------+-----------+-----------+---------+ 
|Bin Location   |Part No.   |Description    |Mfg Code   |Stocked    |Part Type  |On Hand    |Usage    |
+---------------+-----------+---------------+-----------+-----------+-----------+-----------+---------+
|00.01.00       |0002       |Widget 2       |XXXXX      |Y          |STANDARD   |5.000000   |0.000000 |
|00.02.00       |0003       |Widget 3       |XXXXX      |Y          |STANDARD   |0.000000   |0.000000 |
+-----------------------------------------------------------------------------------------------------+

but what I get are no results. If I take out the "AND 'Usage' < 1" bit in the WHERE clause, I get applicable results but I do not want anything with a usage value of 1 or greater.

Answer 1

Your query should throw a conversion error because you're trying to compare a varchar to an int type.

In any case, you should include the subquery in the where clause, like so:

SELECT b.binid AS 'Bin Location',
    i.partnumber AS 'Part No.',
    i.descrip AS 'Description',
    i.mfgcode AS 'Mfg Code',
    i.stocked AS 'Stocked',
    i.itemtype AS 'Part Type',
    i.onhand AS 'On Hand',
    (
        SELECT COALESCE(SUM(l.qtyreqd),0)
        FROM orderln l
        LEFT OUTER JOIN orders o
            ON l.orderid = o.orderid
        WHERE l.itemid = i.itemid
            AND o.ordertype = 'REPAIR'
            AND NOT o.STATUS = 'CANCELED'
            AND l.linetype = 'PART'
            AND l.chgdate BETWEEN '1/10/2018 12:00:00 AM'
                AND '1/11/2019 12:00:00 AM'
        ) AS 'Usage'
FROM item i
LEFT OUTER JOIN iteminv b
    ON b.itemid = i.itemid
WHERE i.shopid = 'FD TRACTOR'
    AND b.binid BETWEEN '00.01.00'
        AND '00.20.00'
    AND 
    (
        SELECT COALESCE(SUM(l.qtyreqd),0)
        FROM orderln l
        LEFT OUTER JOIN orders o
            ON l.orderid = o.orderid
        WHERE l.itemid = i.itemid
            AND o.ordertype = 'REPAIR'
            AND NOT o.STATUS = 'CANCELED'
            AND l.linetype = 'PART'
            AND l.chgdate BETWEEN '1/10/2018 12:00:00 AM'
                AND '1/11/2019 12:00:00 AM'
        ) < 1
ORDER BY b.binid ASC,
    i.partnumber ASC;

Or you can use a cte like EzLo suggested, which makes the query easier to read and understand.

Answer 2

This is your problem:

SELECT 
    (/*... some expressions*/) AS 'Usage'
FROM 
    --....
WHERE 
    --....
    AND 'Usage' < 1

You are using a subquery to generate a column and you alias it as Usage. There are 2 problems with the way you filter this Usage column:

• You are writing the explicit literal value 'Usage', which isn't a way to reference correctly the column you are generating. The expression 'Usage' < 1 should throw a Conversion failed error since the engine will try to cast the literal value "Usage" to integer to do the comparison.

• You can't reference an alias in a WHERE clause on the same scope. You have to either repeat the same expression that generated the column or wrap the result set on a subquery/CTE and reference it outside.

  ;WITH CTE AS
  (
      SELECT 
          (/*... some expressions*/) AS 'Usage'
      FROM 
          --....
  )
  SELECT
      --....
  FROM
      CTE AS C
  WHERE 
      --....
      AND C.Usage < 1 -- No single quotes!