Boris Popov wrote:
I didn't get much traction on this issue last time around, so I apologize to
those who may think my persistence is annoying.
Why is it that this works,
SELECT DISTINCT * FROM (SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
FROM GR_ADDRESS t1
WHERE t1.ID = 1 UNION ALL SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
FROM GR_ADDRESS t1
WHERE t1.ID = 2) t1
but this doesn't?
SELECT DISTINCT * FROM (SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
FROM GR_ADDRESS t1
WHERE (t1.ID = 1)) UNION ALL (SELECT t1.ID, t1.STREET, t1.HOUSE_NUM
FROM GR_ADDRESS t1
WHERE (t1.ID = 2))
Don't you think its strange?
Thanks!
-Boris
Boris,
That is just the way the SELECT statement syntax is specified as
http://www.sqlite.org/lang_select.html shows.
What you have is
SELECT DISTINCT * FROM table_list
Where your table list is a single table, and that table is the result of
a subselect
(SELECT ...)
In the first case you give the subselect an alias name, t1, and in the
second you do not.
The first subselect is parsed as
(
SELECT ... FROM ... WHERE ...
UNION ALL
SELECT ... FROM ... WHERE ...
}
This is a select with a single instance of
SELECT ...
[compound-op select]*
Notice that the second select statement is not a subselect, and it
can't be surrounded by (). That syntax is only allowed for subselects
that are acting as table.
This is what you are trying to do with your second statement:
(
SELECT ... FROM ... WHERE ...
)
UNION ALL
(
SELECT ... FROM ... WHERE ...
}
and that syntax is not allowed so it generates a syntax error.
HTH
Dennis Cote
