Using EXISTS() in a Query 2

[zarkon4]

I am trying to write a query that will only return TRUE/FALSE from within a query

Here is the query

SELECT Inventory.Item, HasPurchOrder AS (Exists(SELECT PurchOrder.Order FROM PurchOrder WHERE PurchOrder.Item = '12345' AND PurchOrder.Status = 'OPEN')) FROM Inventory WHERE Inventory.Item = '12345'

I don't want a list of purchase orders I just want a list of
Inventory items and whether or not it has a purchase order.

I am getting the impression that EXISTS can only be used in a WHERE clause.
Is this correct? If so How can I accomplish this through a single query?

Thanks All,

Kevin

 

[SQLBill]

Check out CASE in the Books OnLine. Something like this might work.

SELECT Inventory.Item,
HasPurchOrder = CASE WHEN PurchOrder.Item = '12345' AND PurchOrder.Status = 'OPEN' THEN 'TRUE' ELSE 'FALSE'
END
FROM Inventory
WHERE Inventory.Item = '12345'

-SQLBill

 

[zarkon4]

Thanks,
This is the query I'm using and it works great

SELECT Inventory.Item, (SELECT CASE WHEN (Exists(SELECT PurchOrder.Order FROM PurchOrder WHERE PurchOrder.Item = Inventory.Item AND PurchOrder.Status = 'OPEN')) THEN 1 ELSE 0 END) AS HasPurchOrder FROM Inventory WHERE Inventory.Item = '12345'

 

[hneal98]

Actually, you might want to try this one:

SELECT
Inventory.Item,
HasPurchOrder AS Case when Exists(SELECT count(*)
FROM PurchOrder PurchOrder
WHERE PurchOrder.Item = Inventory.Item AND PurchOrder.Status = 'OPEN')>0 then 'Yes' else 'No' end
FROM
Inventory Inventory
WHERE
Inventory.Item = '12345'

 

[JohnDTampaBay]

I believe that hneal98's query has a syntax error. I think Zarkon4's query that works (the second post) is more efficient because the EXISTS stops as soon as it encounters a row that meets the given criteria. The COUNT(*) method requires a complete count of rows for the given criteria. In a table with millions of rows, this could make a big difference in performance. Just a thought.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[hneal98]

Where is the syntax error?

You are probably right on it being slower with millions of rows.

 

[JohnDTampaBay]

Two things I see: column aliasing and EXISTS.

SELECT
Inventory.Item,
HasPurchOrder AS Case when Exists(SELECT count(*)
FROM PurchOrder PurchOrder
WHERE PurchOrder.Item = Inventory.Item AND PurchOrder.Status = 'OPEN')>0 then 'Yes' else 'No' end
FROM
Inventory Inventory
WHERE
Inventory.Item = '12345'

When you want to alias a column or expression in a result set, you can use the syntax
NewColumnName = SourceColumn or expression
or this syntax
SourceColumn or expression AS NewColumnName

EXISTS resolves to TRUE or FALSE, so you can't test for EXISTS > 0.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.