   [web] sql query not letting me use 'IN' the way I think it should
SELECTDEL_QS.[Quote Stamp], DEL_QS.Owner, DEL_QS.[Entered Date], DEL_QS.[Biddue Date], DEL_QS.[Quote Type 1],   DEL_QS.[Customer name], G.[PL ID], G.Total_Group_Price, DEL_QS.[Anticipated Order Currency], DEL_QS.Comments,   DEL_QS.[Sales Egr], DEL_QS.[Destination sales Egr]FROM    DEL_QS INNER JOIN (SELECTDEL.[Quote Stamp], IWQ.[PL ID], SUM(IWQ.[Extended Price]) AS Total_Group_Price   FROMDEL_QS AS DEL INNER JOIN DEL_IS AS I ON DEL.[Quote ID] = I.[Quote #] INNER JOIN DEL_IS_with_QS AS IWQ ON IWQ.[Item ID] = I.[Item ID]   WHERE (DEL.[Quote Stats] = 'Quote in process') AND (I.[Active (checked)] = 'true') AND (I.[Primary/Alternative] = 1)   GROUP BY DEL.[Quote Stamp], IWQ.[PL ID]) AS G ON DEL_QS.[Quote Stamp] = G.[Quote Stamp] WHERE (SELECT REPLACE(SUBSTRING(DEL_QS.[Destination sales Egr], CHARINDEX('(', DEL_QS.[Destination sales Egr], 0) + 1, LEN(DEL_QS.[Destination sales Egr])), ')', '')    IN  (SELECTUserEmail   FROMDEL_URS   WHERE (UserManager = 'mcdoua'))  OR (SELECT REPLACE(SUBSTRING(DEL_QS.[Sales Egr], CHARINDEX('(', DEL_QS.[Sales Egr], 0) + 1, LEN(DEL_QS.[Sales Egr])), ')', '')   IN (SELECTUserEmail   FROMDEL_URS AS DEL_URS_1   WHERE (UserManager = 'mcdoua')) The error I get is: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'IN'.Msg 156, Level 15, State 1, Line 15Incorrect syntax near the keyword 'OR'.Msg 156, Level 15, State 1, Line 17Incorrect syntax near the keyword 'IN'.
  I hope you're not expecting this to be in any way "fast"? :-)The problem is actually the "SELECT" keyword:WHERE (SELECT xyz) IN (SELECT abc)Remove the first select:WHERE xyz IN (SELECT abc);Quote:Original post by CodekaI hope you're not expecting this to be in any way "fast"? :-)The problem is actually the "SELECT" keyword:WHERE (SELECT xyz) IN (SELECT abc)Remove the first select:WHERE xyz IN (SELECT abc)LOL. I was just going to post I found the answer and here you go posting it. Honestly, thank you. Thankfully, this runs once a night and there's very little data. So nothing critical. But do you have any suggestions to make optimized?   ;  That query makes me sad :(   ;Quote:Original post by KylotanThat query makes me sad :(I would like to make the query "happier". Some pointers?;  I assume he means the nested subqueries. It's almost always better (read, executes faster) to rewrite them as joins.(It's not that I would try it in this case.)[Edited by - Konfusius on October 7, 2009 1:27:22 PM];  Yes, subqueries in my opinion are almost always the sign of a mistake (or a limitation in the database engine, but that's not the case here). But if it works, fine. Also, the need to mangle so many fields with replace, substring, charindex, len, etc. just suggests to me that the schema here is horribly mismatched to the type of information you actually want out of it. eg. There seems to be parallel information encoded in the "sales Egr" and "Destination sales Egr" which, since you seem to have to query both at once, rather than independently, implies that should be factored out into another table. There are some normalisation issues here, basically.At the very least, if that was my database, I would factor out the REPLACE(SUBSTRING(etc)) madness into stored procedures. If you're having to pull substrings out of data then But really I was just sad at how long and complex the query was. :(  I've written some long queries before but they were just chains of inner joins and the logic was easy to follow.   