UNION SELECT question 1

[VictoryHighway]

Hello,
I have a question regarding using a UNION SELECT query. I have so far only seen the UNION SELECT used to add a single entry on to the query (usually an "All"). Is it possible to add multiple entries on? I have tried to do that, and I keep getting errors such as "The number of columns in the two tables or queries of a Union Query do not match." How do I fix that?

Here is my code:

SELECT DISTINCTROW CallType.TypeID, CallType.Product, CallType.Type FROM CallType WHERE (((CallType.Product)=1)) ORDER BY CallType.Type 
UNION SELECT "1","0", "Application","2","0", "Troubleshooting", "3","0","Request for Information / File(s)", "4","0", "Request for Quotation" FROM CallType
ORDER BY CallType.Type;

Thanks in advance for your help.

--Geoffrey

 

[mpastore]

You need to specify the same number of columns in both you top and botom statements:

SELECT DISTINCTROW CallType.TypeID, CallType.Product, CallType.Type FROM CallType WHERE (((CallType.Product)=1)) ORDER BY CallType.Type
UNION SELECT "1","0", "Application","2","0", "Troubleshooting", "3","0","Request for Information / File(s)", "4","0", "Request for Quotation" FROM CallType
ORDER BY CallType.Type;

The bolded items are causing the problem.

Mike Pastore

Hats off to (Roy) Harper

 

[VictoryHighway]

OK,
So how do I specify the number of columns? There are supposed to be three columns on both sides, and I thought that's what I had set up? Or is each "," a column on the UNION side? If so, how do I set up more rows on the UNION side of query?

--Geoffrey

 

[mpastore]

Yes, eash "," is considered a pseudo-column on the bottom query, You could put place-holders on the top query in order to make them match, the widths will have to be the same.

Mike Pastore

Hats off to (Roy) Harper

 

[VictoryHighway]

OK, here's another question. Instead of putting these values in the UNION SELECT statement, could I put them in another table and then merge that in using the UNION SELECT?

 

[PHV]

Have you tried something like this ?
SELECT DISTINCTROW TypeID,Product,Type
FROM CallType
WHERE Product=1
UNION
SELECT 1,0,"Application"
FROM MSysAccessObjects WHERE ID=0
UNION
SELECT 2,0,"Troubleshooting"
FROM MSysAccessObjects WHERE ID=0
UNION
SELECT 3,0,"Request for Information / File(s)"
FROM MSysAccessObjects WHERE ID=0
UNION
SELECT 4,0,"Request for Quotation"
FROM MSysAccessObjects WHERE ID=0
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VictoryHighway]

Sunuvagun! It worked! Thanks so much!

--Geoffrey