?
Solved

SQL - IF STATEMENT IN UNION

Posted on 2011-04-21
11
Medium Priority
?
370 Views
Last Modified: 2012-05-11
Can you use an IF statment in a Union?

Select * From TableA

UNION

BEGIN
  IF (@Test = 'A')
      Select * From Table 'B'
      Where dtBegin BETWEEN @Beg and @End
END

UNION

Select * From Table 'C'
0
Comment
Question by:CipherIS
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35443272
no, not IF.

but you can do this:
Select * From TableA

UNION ALL
      Select * From Table 'B'
      Where dtBegin BETWEEN @Beg and @End
  AND @Test = 'A'

UNION ALL

Select * From Table 'C'

Open in new window

0
 
LVL 1

Author Comment

by:CipherIS
ID: 35443754
The problem is that I only want

Select * From Table 'B'
      Where dtBegin BETWEEN @Beg and @End
  AND @Test = 'A'

Included ONLY IF (@Test  = 'A')
0
 
LVL 3

Expert Comment

by:kumarnimavat
ID: 35444686
I think you can do only by the given method by using simple if else loop. Let me know if it helps or you have any method to do!

 IF (@Test = 'A')
BEGIN
Select * From TableA
UNION
Select * From Table 'B'
Where dtBegin BETWEEN @Beg and @End
UNION
Select * From Table 'C'
end

ELSE
BEGIN
Select * From TableA
UNION
Select * From Table 'C'
END
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 35444888
try:

Select * From TableA

UNION
      Select * From Table 'B'
      Where dtBegin BETWEEN @Beg and @End and @Test = 'A'
UNION

Select * From Table 'C'
create table t1 (i int)

insert into t1
select 1
create table t2 (i int)

insert into t2
select 2

create table t3 (i int)

insert into t3
select 3

-------
declare @c int
set @c=1

select * from t1 
union
select * from t2 where @c=0
union
select * from t3


---result
1
2
-----------------------

declare @c int
set @c=1

select * from t1 
union
select * from t2 where @c=1

union
select * from t3


result
1
2
3

Open in new window

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35444899
correction:
in my posted example:
result is
1
3
-------------------------------------

declare @c int
set @c=1

select * from t1
union
select * from t2 where @c=0
union
select * from t3


--
or
--


declare @c int
set @c=1

select * from t1
union
select * from t2 where  i =2 and @c=0
union
select * from t3
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35446234
>The problem is that I only want

the result will be that.
because if @testA is not 'A', that query will return 0 rows ...
0
 
LVL 1

Author Comment

by:CipherIS
ID: 35831973
none of these solutions give me the result i'm looking for
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35832018
why does my solution not work for you?
can you please clarify?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 35956190
I only want the select statement to run if the variable = 'A' otherwise I do not want it to process the SQL statment.
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 35956615
CipherIS:

the answers were provided exactely for your question:
"Can you use an IF statment in a Union?
" For UNION that you posted in original post
Answer: No

--
EE made extra mile for you - and provided you with examples that are actually working -->how to simulate IF with union
--

create table TableA (i char)

insert into TableA
select 'A'
create table TableB (i char)

insert into TableB
select 'B'

create table TableC (i char)

insert into TableC
select 'C'

-------
declare @c Char
set @c='A'
select * from TableA 
union
select * from TableB where @c='A'
union
select * from TableC


---result
--A
--B
--C
-----------------------

set @c='Z'

select * from TableA 
union
select * from TableB where @c='A'

union
select * from TableC


result
--A
--C

Open in new window

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 35956618
I have posted another example for you -- that is doing what you are asking
please post if you did not get how it is working and need some help to explain
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question