sql not allowing to use case variable in where clause comparison

Question

I am trying to run a query in sql_server

I want to join 2 col and compare quantity of both & print_col for which 1st table(sid) has less quantity than 2nd table(idt)

Since I need all data of 1st table irrespective of no join, that's why I am using LEFT JOIN and making null field to 0

SELECT sid.id, sid.total_quantity, idt.id,
CASE 
    WHEN idt.total_quantity IS NULL THEN 0
   ELSE idt.total_quantity
END as total_quantity2
FROM abc_details as sid
LEFT JOIN (SELECT * FROM def_details WHERE location_name = 'XYZ') as idt
ON sid.item_no = idt.item_no 
WHERE sid.stock_invoice_id = 37
ORDER BY sid.item_code, sid.lot_number

That is working fine with case condition's col_name 'total_quantity2'

But, when I try to compare

WHERE sid.stock_invoice_id = 37 AND sid.total_quantity < total_quantity2

But, I am getting error

 Unknown column 'total_quantity2' in 'where clause'

WHy this is happeing & how to fix this

You have no access to column aliases in the WHERE clause. (They don't exist yet.) — jarlh, Aug 15 '19 at 12:17
Tip: `coalesce(idt.total_quantity, 0)` instead of that `case`. — jarlh, Aug 15 '19 at 12:17

score 1 · Accepted Answer · answered Aug 15 '19 at 12:21

You can simplify your query to:

SELECT sid.id, sid.total_quantity, idt.id,
       COALESCE(idt.total_quantity, 0) as total_quantity2
FROM abc_details sid LEFT JOIN
     def_details idt
     ON sid.item_no = idt.item_no AND
        idt.location_name = 'XYZ'
WHERE sid.stock_invoice_id = 37
ORDER BY sid.item_code, sid.lot_number;

This changes the CASE expression to COALESCE() and removes the subquery.

For your WHERE clause, you might as well repeat the expression:

WHERE sid.stock_invoice_id = 37 AND
      sid.total_quantity < COALESCE(idt.total_quantity, 0)

However, given that quantities are usually non-negative and assuming that the NULL values come from the LEFT JOIN, you can write the query as:

SELECT sid.id, sid.total_quantity, idt.id,
       idt.total_quantity as total_quantity2
FROM abc_details sid JOIN
     def_details idt
     ON sid.item_no = idt.item_no AND
        idt.location_name = 'XYZ'
WHERE sid.stock_invoice_id = 37 AND
      sid.total_quantity < idt.total_quantity
ORDER BY sid.item_code, sid.lot_number;

That is, you require a matching row if you want this inequality in the WHERE clause.

You are a genius. Nice approach – Satish Patro Sep 02 '19 at 09:08 — Satish Patro, Sep 02 '19 at 09:08

sql not allowing to use case variable in where clause comparison

1 Answers1