Microsoft Access - too much fields, error HY001

[JoaoAfonso]

Ahoy.

I am working with DB and SELECTs, and using the following sentence it returned an error:

Code Select Expand

SELECT * FROM table1 WHERE nome='Joao' UNION ALL SELECT * FROM table1 WHERE nome='Antonio' UNION ALL SELECT * FROM table1 WHERE nome='Fernando'

ERRORTEXT - [Microsoft][ODBC Microsoft Access Controler] Too much fields defined
ERRORCODE - HY001

I believe this is strange, because table1 has like 25 columns and 5 rows up to now. In my point of view, it is too few UNION ALL to give any kind of error (all in all, I am still testing things, but when using it in a program, I would expect many more UNION ALL). Forgot to say that using just one UNION ALL it worked perfectly as supposed:

Code Select Expand

SELECT * FROM table1 WHERE nome='Joao' UNION ALL SELECT * FROM table1 WHERE nome='Antonio'

Am I doing anything wrong?

On the other hand, executing a single select with 30 UNION ALL is faster or is the same of executing 30 individual selects?

Thank you in advance for any explanations

[billhsln]

Replace your select with:


SELECT * FROM table1 WHERE nome IN ('Joao','Antonio','Fernando')

This will give you the result you are looking for.

Bill

[JoaoAfonso]

Wonderful! I was after that keyword conjunction for some days. I understand now that UNION and UNION ALL are only to be used if the intention is join results of different tables. Thank you very much!