Comlex union query question

[Jack]

Hi,
I have say a union query as follows:

select salesman, custid, month1amt, month2amt, month3amt, .....month12amt
from query1
union all
select salesman, custid, month1amt,...................
from query2
union all
select salesman, custid, month1amt, month2amt, month3amt,
.........................
from query3
....

The problem is that in the union query there is no way of knowing that some
of the columns may not exist. So if a column does not exist is there any way
put a null in the column programmatically so that the union query will not
break due to similar number of columns in the union query. I appreciate any
help for resolutio of this issue. Thanks.

 

[Gina Whipp]

Jack,

Null is the magic word! Just put Null seperated by commas a place holder
for colums. See example...

SELECT "Coils" AS ItemType, crCustomerID, crCoilID, crCoilListNet
FROM qryCoilsReceived
UNION ALL SELECT "Coils", crCustomerID, crCoilID, crCoilListNet
FROM qryCoilsInStorage
UNION ALL SELECT "Material", mrCustomerID, Null, mrNetWeight
FROM qryMaterialInStorage
UNION ALL SELECT "Lifts", oCustomerID, LiftStr, lGrossWeight
FROM qryOpenLifts;

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jack]

Folks,
Actually I will not know ahead of time the number of columns in the table
So if I do not know ahead of time I would like to programmatically use null.
Thus the logic should be like this:

If colA exists use colA value else use null,
Same for ColB etc.

Thanks.

 

[Gina Whipp]

Jack,

Now I am worried your tables are set up incorrectly. How is it you will not
know how many columns will be your table and/or query?

To answer your question, I have never attempt that nor do I THINK it's
possible without some serious code, if even then. Perhaps the many smarter
than I will pass by this posting and tell me I'm wrong....

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Piet Linden]

Jack,

Now I am worried your tables are set up incorrectly.  How is it you will not
know how many columns will be your table and/or query?

To answer your question, I have never attempt that nor do I THINK it's
possible without some serious code, if even then.  Perhaps the many smarter
than I will pass by this posting and tell me I'm wrong....

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm







- Show quoted text -

I suppose you *could* write some code to loop through the fields
collection to do this, but I wouldn't recommend it unless absolutely
necessary. I have done it before... only because I didn't have a
choice. You would be FAR better off normalizing your table(s). If
you index, you'll get excellent response times. When I was forced to
write obscenely large union queries, performance was appalling or the
app just plain crashed. (Union queries cannot be indexed... so only
use them as a last resort.) You can write code to process your tables
to create the union queries for you, though... or to just normalize
the data...