Union Query Error

[Earthmover]

Hi Everyone,

When I try and run the below query I get the error message "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

SELECT ID, "A" As SEQ, A As Action FROM 453_Query_1 UNION ALL SELECT ID, "B", B FROM 453_Query_1 UNION ALL SELECT ID, "C", C FROM 453_Query_1 UNION ALL SELECT ID, "D", D FROM 453_Query_1 UNION ALL SELECT ID, "E", E FROM 453_Query_1 UNION ALL SELECT ID, "F",F FROM 453_Query_1 UNION ALL SELECT ID, "G", G FROM 453_Query_1 UNION ALL SELECT ID, "H", H FROM 453_Query_1 UNION ALL SELECT ID, "I", I FROM 453_Query_1 UNION ALL SELECT ID, "J", J FROM 453_Query_1 UNION ALL SELECT ID, "K", K FROM 453_Query_1 UNION ALL SELECT ID, "L", L FROM 453_Query_1 UNION ALL SELECT ID, "M", M FROM 453_Query_1 UNION ALL SELECT ID, "N", N FROM 453_Query_1 UNION ALL SELECT ID, "O", O FROM 453_Query_1 UNION ALL SELECT ID, "P", P FROM 453_Query_1 UNION ALL SELECT ID, "Q", Q FROM 453_Query_1 UNION ALL SELECT ID, "R", R FROM 453_Query_1 UNION ALL SELECT ID, "S", S FROM 453_Query_1 UNION ALL SELECT ID, "T", T FROM 453_Query_1 UNION ALL SELECT ID, "U", U FROM 453_Query_1 UNION ALL SELECT ID, "V", V FROM 453_Query_1 UNION ALL SELECT ID, "W", W FROM 453_Query_1 UNION ALL SELECT ID, "X", X FROM 453_Query_1 UNION ALL SELECT ID, "Y", Y FROM 453_Query_1 UNION ALL SELECT ID, "Z", Z FROM 453_Query_1 UNION ALL SELECT ID, "Z1", Z1 FROM 453_Query_1 UNION ALL SELECT ID, "Z2", Z2 FROM 453_Query_1 UNION ALL SELECT ID, "Z3", Z3 FROM 453_Query_1 UNION ALL SELECT ID, "Z4", Z4 FROM 453_Query_1 UNION ALL SELECT ID, "Z5",
ORDER BY ID, SEQ;

Hoping someone can point me in the right direction as I cant really see an error

Cheers,


Earthmover.

[June7]

Your last UNION is incomplete:

UNION ALL SELECT ID, "Z5", Z5, FROM 453_Query_1

Also, might want the ALL keyword in the first SELECT.

[Earthmover]

Thanks June7,

I've added that last bit on but am still getting the error...

SELECT ID, "A" As SEQ, A As Action FROM 453_Query_1 UNION ALL SELECT ID, "B", B FROM 453_Query_1 UNION ALL SELECT ID, "C", C FROM 453_Query_1 UNION ALL SELECT ID, "D", D FROM 453_Query_1 UNION ALL SELECT ID, "E", E FROM 453_Query_1 UNION ALL SELECT ID, "F",F FROM 453_Query_1 UNION ALL SELECT ID, "G", G FROM 453_Query_1 UNION ALL SELECT ID, "H", H FROM 453_Query_1 UNION ALL SELECT ID, "I", I FROM 453_Query_1 UNION ALL SELECT ID, "J", J FROM 453_Query_1 UNION ALL SELECT ID, "K", K FROM 453_Query_1 UNION ALL SELECT ID, "L", L FROM 453_Query_1 UNION ALL SELECT ID, "M", M FROM 453_Query_1 UNION ALL SELECT ID, "N", N FROM 453_Query_1 UNION ALL SELECT ID, "O", O FROM 453_Query_1 UNION ALL SELECT ID, "P", P FROM 453_Query_1 UNION ALL SELECT ID, "Q", Q FROM 453_Query_1 UNION ALL SELECT ID, "R", R FROM 453_Query_1 UNION ALL SELECT ID, "S", S FROM 453_Query_1 UNION ALL SELECT ID, "T", T FROM 453_Query_1 UNION ALL SELECT ID, "U", U FROM 453_Query_1 UNION ALL SELECT ID, "V", V FROM 453_Query_1 UNION ALL SELECT ID, "W", W FROM 453_Query_1 UNION ALL SELECT ID, "X", X FROM 453_Query_1 UNION ALL SELECT ID, "Y", Y FROM 453_Query_1 UNION ALL SELECT ID, "Z", Z FROM 453_Query_1 UNION ALL SELECT ID, "Z1", Z1 FROM 453_Query_1 UNION ALL SELECT ID, "Z2", Z2 FROM 453_Query_1 UNION ALL SELECT ID, "Z3", Z3 FROM 453_Query_1 UNION ALL SELECT ID, "Z4", Z4 FROM 453_Query_1 UNION ALL SELECT ID, "Z5", Z5, FROM 453_Query_1
ORDER BY ID, SEQ;

Not sure where to add the "ALL" in the first select

[June7]

SELECT ALL ID, "A" ...

Remove the comma after Z5 - sorry for my typo.

[Earthmover]

Thanks, I'm getting somewhere now

Next problem is "The ORDER BY expression (ID) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression."

Here's my first query

SELECT "FunctionKey (CLEAR)" AS A,
"" AS B,
"WaitFor (UNLOCK)" AS C,
"" AS D,
"WaitForCursorPos (12,33)" AS E,
"TypeString (""" & [LOCN] & """)" AS F,
"FunctionKey (ENTER)" AS G,
"" AS H,
"WaitFor (UNLOCK)" AS I,
"" AS J,
"WaitForCursorPos (22,20)" AS K,
"TypeString (453)" AS L,
"FunctionKey (ENTER)" AS M,
"" AS N,
"WaitFor (UNLOCK)" AS O,
"" AS P,
"WaitForCursorPos (6,21)" AS Q,
"Typestring (""" & [PN] & """)" AS R,
"FunctionKey (ENTER)" AS S,
"" AS T,
"WaitFor (UNLOCK)" AS U,
"" AS V,
"WaitForCursorPos (10,21)" AS W,
"TypeString (0.01)" AS X,
"FunctionKey (TAB)" AS Y,
"TypeString (52945)" AS Z,
"FunctionKey (TAB)" AS ZA,
"FunctionKey (TAB)" AS ZB,
"TypeString (pz)" AS ZC,
"FunctionKey (ENTER)" AS ZD,
"WaitFor (UNLOCK)" AS ZE
FROM [453 List];

and here's the 2nd... as far as i can see, both match up. Maybe i don't understand the error
SELECT ALL ID,
"A" As SEQ, A As Action FROM 453_Query_1 UNION ALL SELECT ID,
"B", B FROM 453_Query_1 UNION ALL SELECT ID,
"C", C FROM 453_Query_1 UNION ALL SELECT ID,
"D", D FROM 453_Query_1 UNION ALL SELECT ID,
"E", E FROM 453_Query_1 UNION ALL SELECT ID,
"F",F FROM 453_Query_1 UNION ALL SELECT ID,
"G", G FROM 453_Query_1 UNION ALL SELECT ID,
"H", H FROM 453_Query_1 UNION ALL SELECT ID,
"I", I FROM 453_Query_1 UNION ALL SELECT ID,
"J", J FROM 453_Query_1 UNION ALL SELECT ID,
"K", K FROM 453_Query_1 UNION ALL SELECT ID,
"L", L FROM 453_Query_1 UNION ALL SELECT ID,
"M", M FROM 453_Query_1 UNION ALL SELECT ID,
"N", N FROM 453_Query_1 UNION ALL SELECT ID,
"O", O FROM 453_Query_1 UNION ALL SELECT ID,
"P", P FROM 453_Query_1 UNION ALL SELECT ID,
"Q", Q FROM 453_Query_1 UNION ALL SELECT ID,
"R", R FROM 453_Query_1 UNION ALL SELECT ID,
"S", S FROM 453_Query_1 UNION ALL SELECT ID,
"T", T FROM 453_Query_1 UNION ALL SELECT ID,
"U", U FROM 453_Query_1 UNION ALL SELECT ID,
"V", V FROM 453_Query_1 UNION ALL SELECT ID,
"W", W FROM 453_Query_1 UNION ALL SELECT ID,
"X", X FROM 453_Query_1 UNION ALL SELECT ID,
"Y", Y FROM 453_Query_1 UNION ALL SELECT ID,
"Z", Z FROM 453_Query_1 UNION ALL SELECT ID,
"Z1", Z1 FROM 453_Query_1 UNION ALL SELECT ID,
"Z2", Z2 FROM 453_Query_1 UNION ALL SELECT ID,
"Z3", Z3 FROM 453_Query_1 UNION ALL SELECT ID,
"Z4", Z4 FROM 453_Query_1 UNION ALL SELECT ID,
"Z5", Z5 FROM 453_Query_1
ORDER BY ID, SEQ;

[Earthmover]

Problem solved... I worked it out.
Thanks once again June7

[June7]

There is no ID field in the first query so it is not there for the second.

Maybe do the UNION in first query.

SELECT ID, "FunctionKey (CLEAR)" As Action, "A" AS Seq FROM [453 List]
UNION SELECT ID, "", "B" FROM [453 List];
UNION SELECT ID, "WaitFor (UNLOCK)", "C" FROM [453 List]
UNION SELECT ID, "", "D" FROM [453 List]
UNION SELECT ID, "WaitForCursorPos (12,33)", "E" FROM [453 List]
UNION SELECT ID, "TypeString (""" & [LOCN] & """)", "F" FROM [453 List]
UNION SELECT ID, "FunctionKey (ENTER)", "G" FROM [453 List]
UNION SELECT ID, "", "H" FROM [453 List]
UNION SELECT ID, "WaitFor (UNLOCK)", "I" FROM [453 List]
UNION SELECT ID, "", "J" FROM [453 List]
UNION SELECT ID, "WaitForCursorPos (22,20)", "K" FROM [453 List]
UNION SELECT ID, "TypeString (453)", "L" FROM [453 List]
UNION SELECT ID, "FunctionKey (ENTER)", "M" FROM [453 List]
UNION SELECT ID, "", "N" FROM [453 List]
UNION SELECT ID, "WaitFor (UNLOCK)", "O" FROM [453 List]
UNION SELECT ID, "", "P" FROM [453 List]
UNION SELECT ID, "WaitForCursorPos (6,21)", "Q" FROM [453 List]
UNION SELECT ID, "Typestring (""" & [PN] & """)", "R" FROM [453 List]
UNION SELECT ID, "FunctionKey (ENTER)", "S" FROM [453 List]
UNION SELECT ID, "", "T" FROM [453 List]
UNION SELECT ID, "WaitFor (UNLOCK)", "U" FROM [453 List]
UNION SELECT ID, "", "V" FROM [453 List]
UNION SELECT ID, "WaitForCursorPos (10,21)", "W" FROM [453 List]
UNION SELECT ID, "TypeString (0.01)", "X" FROM [453 List]
UNION SELECT ID, "FunctionKey (TAB)", "Y" FROM [453 List]
UNION SELECT ID, "TypeString (52945)", "Z" FROM [453 List]
UNION SELECT ID, "FunctionKey (TAB)", "ZA" FROM [453 List]
UNION SELECT ID, "FunctionKey (TAB)", "ZB" FROM [453 List]
UNION SELECT ID, "TypeString (pz)", "ZC" FROM [453 List]
UNION SELECT ID, "FunctionKey (ENTER)", "ZD" FROM [453 List]
UNION SELECT ID, "WaitFor (UNLOCK)", "ZE" FROM [453 List]
ORDER BY ID, Seq;