|
|
Posted by Erland Sommarskog on 10/01/02 11:17
Elroyskimms (elroyskimms@yahoo.com) writes:
> Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)
> IF (SELECT TC.HasRetailStores
> FROM tblCustomer TC
> WHERE TC.CustomerID = @CustomerID) = 1
> SELECT *
> FROM tblInvoiceMessages IM
> WHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)
> UNION
> SELECT *
> FROM tblInvoiceMessages IM
> WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
> UNION
> IF (SELECT TC.HasWholesaleStores
> FROM tblCustomer TC
> WHERE TC.CustomerID = @CustomerID) = 1
> SELECT *
> FROM tblInvoiceMessages IM
> WHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)
>
> Attempt #2 is the same as Attempt#1 except that I attempt to Union
> another If Select query to the first two queries. This attempt
> generates:
> Server: Msg 156, Level 15, State 1, Line 12
> Incorrect syntax near the keyword 'IF'.
>
> I have tested each individual If Select statement, and they all return
> proper results. However, anytime I attempt to Union more than 1 If
> Select statement together, I get the Msg 156 error. Is there some
> limitation that I am not aware of?
You have mixed up control-of-flow language with SELECT statememts.
This may best be explained by looking at the first batch, but reformatted:
IF (SELECT TC.HasRetailDestinations
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
BEGIN
SELECT *
FROM tblInvoiceMessages IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)
UNION
SELECT *
FROM tblInvoiceMessages IM
WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
END
IF is one statemet, SELECT is another. So you get a syntax error when
you try to use IF in the middle of a SELECT statement.
This might work for your second query:
IF (SELECT TC.HasRetailStores
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
BEGIN
SELECT *
FROM tblInvoiceMessages IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)
UNION
SELECT *
FROM tblInvoiceMessages IM
WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
UNION
SELECT *
FROM tblInvoiceMessages IM
WHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)
AND EXISTS (SELECT
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID
AND TC.HasWholesaleStores = 1)
END
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|