Posted by Hugo Kornelis on 09/30/80 11:17

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)

[Back to original message]