6

I am trying to create a simple view in Microsoft SQL Server Management Studio 2008 R2. I tried using the Views > New View designer as well as typing the CREATE VIEW command directly in to the query builder, but no matter how I create it, the studio malforms my query.

Here is the source query. Pay special attention to the WHERE clause:

 SELECT RTRIM(Orders.ITEMNUMBER) AS ItemNumber, 
        Orders.Type, 
        Orders.Quantity, 
        Orders.OrderNumber, 
        OrderDetails.SaleDate, 
        OrderLineItems.ReturnType,
        OrderLineItems.Action,
        OrderLineItems.Restock,
        OrderLineItems.Return
 FROM Orders 
 INNER JOIN OrderDetails ON Orders.Type = OrderDetails.Type 
 AND Orders.OrderNumber = OrderDetails.OrderNumber 
 INNER JOIN OrderLineItems ON Orders.OrderNumber = OrderLineItems.OrderNumber
 WHERE (OrderLineItems.Type = 'INVOICE' AND Orders.Type='3') OR 
       (OrderLineItems.Type = 'RETURN' AND Orders.Type='4')

However, no matter what, SQL Server Management Studio malforms the query to this:

 SELECT RTRIM(Orders.ITEMNUMBER) AS ItemNumber, 
        Orders.Type, 
        Orders.Quantity, 
        Orders.OrderNumber, 
        OrderDetails.SaleDate, 
        OrderLineItems.ReturnType,
        OrderLineItems.Action,
        OrderLineItems.Restock,
        OrderLineItems.Return
 FROM Orders 
 INNER JOIN OrderDetails ON Orders.Type = OrderDetails.Type 
 AND Orders.OrderNumber = OrderDetails.OrderNumber 
 INNER JOIN OrderLineItems ON Orders.OrderNumber = OrderLineItems.OrderNumber
 WHERE (OrderLineItems.Type = 'INVOICE') AND (Orders.Type='3') OR 
       (OrderLineItems.Type = 'RETURN') AND (Orders.Type='4')

See, the difference is that I want to pull rows where OrderLineItems.Type is INVOICE and Orders.Type is 3, OR OrderLineItems.Type is RETURN and Orders.Type is 4. The query it rewrites for me messes up the parentheses - it won't allow me to put two AND clauses on either side of an OR clause.

Is there any way to bypass Microsoft's query rewriter? Or would the above rewritten query still be interpreted correctly even though the parentheses are not properly separating the two AND clauses?

As I mentioned, I already tried creating this using a straight up CREATE query in the New Query view, but it still ends up malformed.

Thanks for your help.

EDIT / Conclusion

So it turns out that both queries above are logically equivalent. MS SQL's order of operations causes it to evaluate each AND statement first, then finally check the OR statement last. The parentheses, though nice for clarity, were unnecessary.

Typel
  • 1,109
  • 1
  • 11
  • 34
  • Perhaps by adding inner and outer () you can bypass the issue. It's odd behavior for sure. `(OrderLineItems.Type = 'INVOICE' AND Orders.Type='3') OR (OrderLineItems.Type = 'RETURN' AND Orders.Type='4')` becomes `((OrderLineItems.Type = 'INVOICE') AND (Orders.Type='3')) OR ((OrderLineItems.Type = 'RETURN') AND (Orders.Type='4'))` – xQbert Mar 21 '14 at 20:15
  • I did try that, adding extra parentheses around each part, eg ((a=b) AND (c=d)) OR ((e=f) AND (g=h)) but as soon as I submit that, the query builder reverts back to (a=b) AND (c=d) OR (e=f) AND (g=h) ... can't seem to get it to understand. – Typel Mar 21 '14 at 20:18
  • I'd have to play around with the engine. It's possible that the engine handles this to give you the desired results. looking at the order it's possible the engine is doing it correctly. (Line type AND order type) OR (Line Type and order Type) are you getting incorrect results? – xQbert Mar 21 '14 at 20:24
  • Oddly it seems that the engine is correct, they will evaluate out to the same due to precident of execution http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or AND wins first then OR. – xQbert Mar 21 '14 at 20:26
  • Oh wow, okay - that seems odd, but I guess it's because I'm used to being more explicit with my parentheses rather than relying on the order of operations, which can vary by language. I'll take a closer look at these results and see whether or not they really are correct after all. Thanks for this. – Typel Mar 21 '14 at 20:33
  • xQbert, it looks like you're correct. The two statements ARE equivalent, based on the MS SQL Order of operations (had to look that one up)... The two AND statements will resolve first and then finally the OR statement will be evaluated last. Thanks so much for your help. – Typel Mar 21 '14 at 20:51
  • Being explicit is ok - PERFECT actually. But do not expect a code generator to be so. As in: Use a CREATE VIEW statement and you will be fine. The Designer will optimize by it's own guidelines. – TomTom Mar 21 '14 at 21:16

2 Answers2

0

Might sound silly, but have you tried just going along with it, and putting the parens around the whole thing after corrected? Since it insists on isolating each condition...

WHERE ((OrderLineItems.Type = 'INVOICE') AND (Orders.Type='3')) OR ((OrderLineItems.Type = 'RETURN') AND (Orders.Type='4'))

durbnpoisn
  • 4,666
  • 2
  • 16
  • 30
  • Yep, sure did. Sorry I didn't mention that in the question, but it does the same thing - ((a=b) AND (c=d)) OR ((e=f) AND (g=h)) always reverts back to (a=b) AND (c=d) OR (e=f) AND (g=h) ... it sure would make sense if there was an option somewhere to disable query rewriting... looking for that option, but it seems strangely absent. – Typel Mar 21 '14 at 20:25
  • Okay. I have another theory. It could be because of the placement of your joins. Because you have a join that specifies the same object as you AND statements, they may have to be isolated for conditions. Meaning, you can't join them in one place, and run a condition someplace else. – durbnpoisn Mar 21 '14 at 20:29
  • Well basically the reason why the WHERE clause is there at all is because I'm trying to join two fields that aren't formatted the same, but have equivalent meaning. ie, in OrderLineItems, Type is spelled out as "INVOICE" or "RETURN" but in Orders, Type is denoted by a 3(invoice) or 4(return). So the WHERE clause is intended to act as a substitute JOIN because we can't actually join them based on a simple equality check. I think Type is only used to join Orders with OrderDetails prior to the WHERE clause, so I'm not sure if it would conflict with the WHERE clause... not certain I guess. – Typel Mar 21 '14 at 20:40
  • Hmm. Ok. If you always know that 3 is Invoice and 4 is Return, do you really need that AND condition? If you need to know the result of each, set your select to return a CASE of Orders.Type. 3 as "Invoice" and 4 as "Return". And leave the OR clause in there. – durbnpoisn Mar 21 '14 at 20:45
  • I think we've figured this one out in the comments above, but thanks so much for your assistance, durbnpoisn. So, it turns out that the two statements are logically equivalent since the MS SQL order of operations resolves the two AND statements first and then finally checks the OR statement in the middle last. So the parentheses I had added for my own clarity really were unnecessary after all. Microsoft says: don't question it, embrace it! haha – Typel Mar 21 '14 at 20:53
0

By knuckling under SQL Server you are loosing readability.

You may try impove it using CTE:

with where_condition as 
(select 'INVOICE' OrderLineType, '3' OrderType union all 
 select 'RETURN', '4')

select * from <list of your tables>, where_condition
where OrderLineItems.Type = where_condition.OrderLineType and 
Orders.Type= where_condition.OrderType

Unfortunatelly, SQL Server do not yet support (tuple) in (select ..) syntax.

vav
  • 4,584
  • 2
  • 19
  • 39