IF SELECT UNION

[Elroyskimms]

Using SQL 2000...

tblCustomer:
CustomerID int
CompanyName varchar(20)
HasRetailStores bit
HasWholesaleStores bit
HasOtherStores bit

tblInvoiceMessages:
MessageID int
MessageText varchar(100)
CustomerID int
AllRetailStores bit
AllWholesaleStores bit
AllOtherStores bit
AllStores bit
IsActive bit

The Invoice Messages are text blocks which will be added to invoices
going out to customers. A customer can have Retail stores, Wholesale
stores, and/or Other Stores. The messages can go to only those
customers with a specific type of store, or all customers, or to a
specific customer. It is important to note that a customer can have 1,
2 or all 3 types of stores. Here are a couple of sample entries in the
invoice messages table:

tblInovoiceMessages
1,For Customers with Retail and Wholesale Stores,0,1,1,0,0
2,Only For Customer # 10,10,0,0,0,0
....

Attempt #1 (IF SELECT UNION SELECT)
IF (SELECT TC.HasRetailDestinations
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)

Attempt #1 checks if the Customer has retail stores, and if it does,
returns all messages for Retail Stores. The second Select statement
checks for all messages designated for that particular Customer. I use
Union to combine the tables (which have identical structures) and it
works great.

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?

[Hugo Kornelis]

On 30 May 2005 13:29:24 -0700, Elroyskimms wrote:

(snip)

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?

Hi Elroyskimms,

UNION is part of a query. You can UNION together several SELECT clauses,
but you can't use control-flow code in between.

I think that you could get the output you need from the following query:

SELECT column list -- never use SELECT * in production code!!
FROM Customer AS c,
InvoiceMessages AS im
WHERE c.CustomerID = @CustomerID
AND im.IsActive = 1
AND ( im.CustomerID = @CustomerID
OR (im.AllRetailStores = 1 AND c.HasRetailStores = 1)
OR (im.AllWholeSaleStores = 1 AND c.HasWholeSaleStores = 1))

(untested, since you didn't post CREATE TABLE and INSERT statements with
test data and expected output - see www.asp.faq.com/5006)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Elroyskimms (el*********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Elroyskimms]

Thanks everyone for your help. I had incorrectly assumed that because
the result of the IF SELECT statement was in table form, I could use it
inside a SELECT statement like a nested SELECT. I modified Hugo's
suggestions just slightly and it seems to work great, and it is much
simpler then what I had originally planned.

Is there a security risk behind using SELECT * or is it a "Best
Practice"? Thanks!

-E

[Erland Sommarskog]

Elroyskimms (el*********@yahoo.com) writes:

Is there a security risk behind using SELECT * or is it a "Best
Practice"? Thanks!

Best practice.

Assume that the function where you use this query actually reads six of ten
columns. First of all you save some network bandwidth, by only getting
the columns you need. Second, assume that the DBA considers dropping
one of the columns. If you explicitly list the columns you need, he can
easily see whether you are using that column or not. Third, the DBA may add
another column, and insert it in the middle, if you now have referred to
columns by number in the client (which is bad practice), your function will
stop working.

SELECT * is wonderful for debugging when do you ad-hoc queries, but it's
bad in production code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp