alternative to UNION?

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Hi,

I have a problem that maybe I could get your advice on. I have
several queries I want to combine. The number of queries changes
dynamically depending on user input, but let's just say it can get up
to 50 or 60 queries. I wanted just to use UNION but there appears to
be some upper limit on the number of UNIONs I can use. I've poked
around, but haven't seen any alternatives to using UNION that I'm
comfortable with.

Here's an example of what I'm looking at. Take these 3 queries where
b_cdoe changes:
----------------------------------------------------------------
Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "1" group by val

This might return a table that looks like:

val
___

4
----------------------------------------------------------------
Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "2" group by val

This might return a table that looks like:

val
___

<---- no val

---------------------------------------------------------------

Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "3" group by val

This might return a table that looks like:

val
___

2
3
4
5
----------------------------------------------

So, doing a UNION on these 3 queries gives me:

val
___

2
3
4
5

which I like. It didn't dupe the 4 and everything's sorted still.

If I can't use UNION, is there anything you'd recommend if we were
dealing with say 50 or 60 queries? Should I create a temp table and
just run each query and just do inserts into that temp table after
each query? It just seems cumbersome to maintain my sorted order.
I'm using embedded SQL in Java, btw.

Thanks much in advance for any advice.

Marc
 
Dear Marc:

I don't think you need a UNION query for this. Given your examples,
how about just putting the complexity in the filtering:

SELECT DISTINCT val
FROM myTable
WHERE [date] IN (2000, 2001, 2002, 2003)
AND a_code = "1" AND b_code IN ("1", "2", "3")

The logic may get a bit more complex over your 50 or 60 combinations.
I suggest a study of this logic may yield some way of expressing it
reasonably. Or, there may be a combination of such logic with a small
number of UNIONs to do the same thing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thanks! I can't believe I didn't see that... I got so wrapped up in
combining separate queries I didn't even think to just filter them
into one. I think this will do the trick.

Marc

Tom Ellison said:
Dear Marc:

I don't think you need a UNION query for this. Given your examples,
how about just putting the complexity in the filtering:

SELECT DISTINCT val
FROM myTable
WHERE [date] IN (2000, 2001, 2002, 2003)
AND a_code = "1" AND b_code IN ("1", "2", "3")

The logic may get a bit more complex over your 50 or 60 combinations.
I suggest a study of this logic may yield some way of expressing it
reasonably. Or, there may be a combination of such logic with a small
number of UNIONs to do the same thing.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a problem that maybe I could get your advice on. I have
several queries I want to combine. The number of queries changes
dynamically depending on user input, but let's just say it can get up
to 50 or 60 queries. I wanted just to use UNION but there appears to
be some upper limit on the number of UNIONs I can use. I've poked
around, but haven't seen any alternatives to using UNION that I'm
comfortable with.

Here's an example of what I'm looking at. Take these 3 queries where
b_cdoe changes:
----------------------------------------------------------------
Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "1" group by val

This might return a table that looks like:

val
___

4
----------------------------------------------------------------
Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "2" group by val

This might return a table that looks like:

val
___

<---- no val

---------------------------------------------------------------

Select val from myTable where date in (2000, 2001, 2002, 2003) and
a_code = "1" and b_code = "3" group by val

This might return a table that looks like:

val
___

2
3
4
5
----------------------------------------------

So, doing a UNION on these 3 queries gives me:

val
___

2
3
4
5

which I like. It didn't dupe the 4 and everything's sorted still.

If I can't use UNION, is there anything you'd recommend if we were
dealing with say 50 or 60 queries? Should I create a temp table and
just run each query and just do inserts into that temp table after
each query? It just seems cumbersome to maintain my sorted order.
I'm using embedded SQL in Java, btw.

Thanks much in advance for any advice.

Marc
Click to expand...
Click to expand...
 
Back
Top