473,846 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

IF SELECT UNION

Using SQL 2000...

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

tblInvoiceMessa ges:
MessageID int
MessageText varchar(100)
CustomerID int
AllRetailStores bit
AllWholesaleSto res 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:

tblInovoiceMess ages
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.HasRetailDes tinations
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailSt ores = 1)
UNION
SELECT *
FROM tblInvoiceMessa ges 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.HasRetailSto res
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailSt ores = 1)
UNION
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
UNION
IF (SELECT TC.HasWholesale Stores
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllWholesal eStores = 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?

Jul 23 '05 #1
4 6960
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.AllRetailSt ores = 1 AND c.HasRetailStor es = 1)
OR (im.AllWholeSal eStores = 1 AND c.HasWholeSaleS tores = 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)
Jul 23 '05 #2
Elroyskimms (el*********@ya hoo.com) writes:
Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)
IF (SELECT TC.HasRetailSto res
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailSt ores = 1)
UNION
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
UNION
IF (SELECT TC.HasWholesale Stores
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllWholesal eStores = 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.HasRetailDes tinations
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
BEGIN
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailSt ores = 1)
UNION
SELECT *
FROM tblInvoiceMessa ges 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.HasRetailSto res
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID) = 1
BEGIN
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllRetailSt ores = 1)
UNION
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)
UNION
SELECT *
FROM tblInvoiceMessa ges IM
WHERE (IM.IsActive = 1) AND (IM.AllWholesal eStores = 1)
AND EXISTS (SELECT
FROM tblCustomer TC
WHERE TC.CustomerID = @CustomerID
AND TC.HasWholesale Stores = 1)
END
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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

Jul 23 '05 #4
Elroyskimms (el*********@ya hoo.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7928
by: Chris Becker | last post by:
I have the following query: SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BY Month Unfortunately it only returns rows for months that have data assigned to them. How can I tweak this so that months 1-12 are returned, and Hits = 0 for months with no data in the base table?
17
5036
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no)
1
3351
by: jtwright | last post by:
I've got a view that creates a parent child relationship, this view is used in Analysis Services to create a dimension in a datastore. This query tends to deadlock after about 10 days of running smoothly. Only way to fix it is to reboot the box, I can recycle the services for a quick fix but that usually only works for the next 1-2 times I call the view. This view is used to create a breakdown of the bill-to locations from...
3
5735
by: dumbledad | last post by:
Hi All, I'm confused by how to replace a SELECT statement in a SQL statement with a specific value. The table I'm working on is a list of words (a column called "word") with an index int pointing to the sentence they come from (a column called "regret"). I also have a table of stop words (called "GenericStopWords") that contains the words I do not want to consider. That table has a single column called "word". I started off using a...
3
6740
by: Ker | last post by:
I have a query that works great. It gives me the min for multiple fields. Within this query, I also need to get the max of some fields too. I currently have output of Date Name Min Start 9/1/03 Walker Rhines 7:00AM I also need Max such as Max End 3:00PM
3
6769
by: Randall Skelton | last post by:
I have a number of tables with the general structure: Column | Type | Modifiers -----------+--------------------------+----------- timestamp | timestamp with time zone | value | double precision | Indexes: tbl__timestamp and I would like to find the union of the timestamps. Something like:
6
3398
by: Apaxe | last post by:
In the database i have a table with this information: key_id =1 key_desc =43+34+22+12 I want sum the values in key_desc. Something like: SELECT key_desc FROM table But the result of the select was "111" and not "43+34+22+12".
2
2821
by: rickcf | last post by:
I have two tables. Table A is the main table I need to update and table B is the update table with the new data. Table B contains only two fields- fielda (also contained in table A for the linking but also is the field that needs changed) and a second field (fieldb) which is the NEW value of fielda that I need to change in TableA. Thus I need a combination UPDATE/SELECT statement to link the two tables togeather to update table A with the new...
4
2630
by: Manikrag | last post by:
Hi Team, Is it possible to sort select query based on input string? I am looking for somthing like: select TOP 20 PREFERRED_NAME from FRS_TABLE where Lower(PREFERRED_NAME) like Lower('%shar%') order by PREFERRED_NAME LIKE '%shar%'
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10978
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7879
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5716
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4113
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.