Menu
I have a view that's something like
CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
The intent is to fetch all orders that have at least one line item of type 1 along with their current status.
We're in the process of adding a second type of line item, and I have modified the view such that it will include orders that have at least one line item of type 1 or type 2:
CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))
Easy enough, but I've just had a requirement added to show whether an order contains line items of type 1 or type 2 (or both) in the grid where these results are displayed:
Order ID | T1 | T2 | Last name | Price | Status ============================================================ 12345 | x | | Smith | $100.00 | In Production 12346 | x | x | Jones | $147.23 | Part Dispatched 12347 | | x | Atwood | $12.50 | Dispatched
The only way I can think of is to do:
CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id,
CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderID = o.Id) THEN 1 ELSE 0 END AS HasType1,
CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.ID) THEN 1 ELSE 0 END AS HasType2,
o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))
But this smells wrong with the duplication of the EXISTS clauses. Is there a better qway to write it? Can I make it perform better?
286
The operator returns the value as TRUE if the subquery contains any rows, otherwise FALSE. The exists operator can be used with either of these statements- SELECT, UPDATE, INSERT or DELETE.
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Yes, just do: SELECT CASE WHEN EXISTS(subquery) THEN... There are some situations you can't use it (e.g. in a group by clause IIRC), but SQL should tell you quite clearly in that situation.
Use EXISTS to identify the existence of a relationship without regard for the quantity. For example, EXISTS returns true if the subquery returns any rows, and [NOT] EXISTS returns true if the subquery returns no rows. The EXISTS condition is considered to be met if the subquery returns at least one row.
you can LEFT JOIN on OrderLineItemType1 and OrderLineItemType2 and then filter out rows where both of those columns are NULL in the WHERE clause.
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
