Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jwirvin
Starting Member
3 Posts |
 Posted - 2003-02-03 : 14:51:28
|
| Hey all,I am having a problem with the following query. I am using a parameter within a SQL statement that works fine as long as I don't have both an inner join in the first select when combining it using a Union. For instance, 'select table1.field1, table1.field2, table1.field3 from table1 inner join table 2 on table1.field1 = table2.field1 where table1.field3 = ?' works as expected. However, if I attempt to Union the first select when it contains a parameter and an inner join, then I get an error message "incorrect syntax near the word 'inner'". If I have a parameterized Union without an inner join, there are no problems. Anyone aware of why a Union with a parameter and an inner join are not allowed? To help clarify, I get the above message when the query gets written as follows : 'select Contacts.ContactID, Contacts.CustomerID, 'Y' as IsBuyer from Contacts inner join CustomerBuyers on Contacts.ContactID = CustomerBuyers.ContactID where Contacts.CustID = ? UNION Select Contacts.ContactID, Contacts.CustomerID, 'N' as IsBuyer from Contacts inner join CustomerBuyers on Contacts.ContactID = CustomerBuyers.ContactID where Contacts.CustID = ? and CustomerBuyers.ContactID is null'If I write the above statement without the parameters and use the literal values, the query returns with no problems. Only when introducing the ? (parameter) with an inner join do I get the error message.Sincerely,J.W. Irvin |
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-02-03 : 16:18:16
|
quote:
Hey all,I am having a problem with the following query. I am using a parameter within a SQL statement that works fine as long as I don't have both an inner join in the first select when combining it using a Union. For instance, 'select table1.field1, table1.field2, table1.field3 from table1 inner join table 2 on table1.field1 = table2.field1 where table1.field3 = ?' works as expected. However, if I attempt to Union the first select when it contains a parameter and an inner join, then I get an error message "incorrect syntax near the word 'inner'". If I have a parameterized Union without an inner join, there are no problems. Anyone aware of why a Union with a parameter and an inner join are not allowed? To help clarify, I get the above message when the query gets written as follows : 'select Contacts.ContactID, Contacts.CustomerID, 'Y' as IsBuyer from Contacts inner join CustomerBuyers on Contacts.ContactID = CustomerBuyers.ContactID where Contacts.CustID = ? UNION Select Contacts.ContactID, Contacts.CustomerID, 'N' as IsBuyer from Contacts inner join CustomerBuyers on Contacts.ContactID = CustomerBuyers.ContactID where Contacts.CustID = ? and CustomerBuyers.ContactID is null'If I write the above statement without the parameters and use the literal values, the query returns with no problems. Only when introducing the ? (parameter) with an inner join do I get the error message.Sincerely,J.W. Irvin
Try giving your tables in the second part an alias. ALternatively you could rewrite your query to avoid the union and use CASE to set your IsBuyer Column to Y or N |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-03 : 16:26:36
|
| Yup, Case will work, and prob. be more effiecient. Try This:SELECT Contacts.ContactID, Contacts.CustomerID, CASE WHEN CustomerBuyers.ContactID is null Then 'N' ELSE'Y' END as IsBuyer FROM Contacts INNER JOIN CustomerBuyers ON Contacts.ContactID = CustomerBuyers.ContactID WHERE Contacts.CustID = ?Good LuckBrett8-) |
|
|
|
jwirvin
Starting Member
3 Posts |
Posted - 2003-02-03 : 17:16:23
|
| Thanks darinh and X002548. I decided to rewrite the query using the CASE statement and removed the UNION. I truly appreciate your answers. Also, for anyone reviewing this, the resolved SQL is like what X002548 stated, but with a left join instead of an inner join. Only makes sense, since the UNION has been removed.SELECT Contacts.ContactID , Contacts.CustomerID , CASE WHEN CustomerBuyers.ContactID is null Then 'N' ELSE 'Y' END as IsBuyer FROM Contacts LEFT JOIN CustomerBuyers ON Contacts.ContactID = CustomerBuyers.ContactID WHERE Contacts.CustID = ? Once again, thanks a bunch! Still wish I knew why that UNION fails with a parameter and an inner join.Sincerely,J.W. Irvin |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-02-09 : 17:55:16
|
quote:
Once again, thanks a bunch! Still wish I knew why that UNION fails with a parameter and an inner join.Sincerely,J.W. Irvin
I haven't tried it but my guess is because you have used the same tablename on both sides of the union and the Contacts.CustID = ? appearing twice is giving the problem. To ease your mind try using an alias for the second bit e.g. something likeselect Contacts.ContactID, Contacts.CustomerID, 'Y' as IsBuyer from Contacts inner join CustomerBuyers on Contacts.ContactID = CustomerBuyers.ContactID where Contacts.CustID = ? UNION Select Contacts2.ContactID, Contacts2.CustomerID, 'N' as IsBuyer from Contacts AS Contacts2 inner join CustomerBuyers on Contacts2.ContactID = CustomerBuyers.ContactID where Contacts2.CustID = ? and CustomerBuyers.ContactID is nullHowever, even if it works I reckon the CASE method is much cleaner. You could look at the exection plans to prove which is better. |
|
|
|
|
|
|
|
|
Using parameters with Union and inner join