IF EXISTS and UNION problems

[tony868]

I'm fairly new at SQL so I'm not sure if this is the correct way to write this. I have several queries that I want to join with UNIONs but I also need to run an IF EXISTS on each one first. The way I have it written now is

IF EXISTS(query 1)
  BEGIN
    query 1
  END
ELSE
  CREATE TABLE #1temp...
  INSERT #1temp...
  SELECT * from #1temp

UNION ALL

IF EXISTS(query 2)
  BEGIN
    query 2
  END
ELSE
  CREATE TABLE #2temp...
  INSERT #2temp...
  SELECT * from #2temp

...so on with 4 more queries.

When I run it in Query Analyzer I get the following error:
Server: Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'IF'.

Each individual query runs with no problems but when I try to run them all with the UNIONs I get the error. All of the select statements pull the same data type and number of columns.

Any ideas or am I going in the wrong direction with this one? Thanks!

--tony

 

[AlexCuse]

What are you trying to do here? I think you want to run all your if exists as if NOT exists, and create/populate your temp tables first. Then run the union query (I imagine it is pulling from these created tables).

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[tony868]

Thanks for the quick reply. I'm trying to run a QA report that shows the status of clients within a Cost Center and then break that down by race and gender. Each query shows a different status. The first two, for example, are active clients that started in the Cost Center prior to a date parameter(query 1) and then active clients that started in the Cost Center after the date parameter(query 2). What I'm finding is that for some Cost Centers there are no clients that meet the status criteria so I don't get a row displayed. By using IF EXISTS I figured I could test to see if there were rows displayed then, if there were not, I would create a temp table and format that in Crystal Reports. I want the UNION query to run for the temp table if no rows were present in the existing table or for the existing table if there are rows present. Is my logic off? Thanks!

 

[nice95gle]

I'm a little confused on what you want. But I think this is what you are trying to say.

IF NOT EXISTS(query 1)
  BEGIN
  	CREATE TABLE #1temp...
  	INSERT #1temp...
  END

IF NOT EXISTS(query 2)
  BEGIN
        CREATE TABLE #2temp...
  	INSERT #2temp...
  END


SELECT * from #1temp	
UNION ALL
SELECT * from #2temp

Well Done is better than well said
- Ben Franklin

 

[tony868]

Sorry about the confusion, nice95gle. I'm fairly new to this so I'm having a hard time explaining it.
Currently I get the following results where status A2(my second query) does not return any data:
Status Male Female ...
A1 1 3
A3 5 0
...
Ultimately I want my results to look similar to this:
Status Male Female ...
A1 1 3
A2 0 0
A3 5 0
...
I'm trying to get here by using IF EXISTS and creating a temp table if neccesary. I'm willing to try other methods if my logic doesn't work. Thanks!

 

[nice95gle]

Ok I get it... I'm sure someone else can jump in if they know of a better way.

If you only want one record for A1, A2, A3 etc... if your query returns nothing, you can do something like this:

bypass the temp tables all together.
Let's use A2 as an example:

IF NOT EXISTS
(query2...)

BEGIN
         insert YOURTABLENAME
         values('A2',0,0...)
END
ESLE
BEGIN
         insert YOURTABLENAME(query2...)
END

You can do the same thing for all the other queries. Hopefully this helps.



Well Done is better than well said
- Ben Franklin

 

[tony868]

Thanks for your help! I was still having some problems getting the results I wanted with your suggestions but I was able to figure out a solution. Basically I created temp tables with one row of dummy values so there will always be a result for

select * from temptable1.

I then used

INSERT INTO temptable1
SELECT query1

to insert my queries into the appropriate temp tables. From there I was able to

select * from temptable

and use UNION to join all of the tables into one dataset. Whew! I've been struggling with this for several days. Thanks again!

 

[nice95gle]

Cool, as long as you figured it out. I still have alot to learn myself.

Well Done is better than well said
- Ben Franklin