On 2014/07/12 19:29, Staffan Tylen wrote:
The following statement is flagged as invalid, so what's the correct way of
coding it?
WITH A AS (SELECT 'A'),
B AS (SELECT 'B')
SELECT *
FROM A
UNION
SELECT *
FROM B
;
Hi Staffan,
What is wrong with it? Depends what you intended to do? Can you say in normal English what you would like to achieve or have the SQL
engine return from the proposed query?
To demonstrate what I mean, your current query basically says to the engine: "Imagine there's 2 boxes in the sky, called A and B
without form, and we try to stuff a value into each, then show me what is all in box A and then, for my next query (which should be
appended or UNION'd to the previous query), get a list of all things in a Table named B (which doesn't exist in the DB as far as we
can tell)."
There is no plausible way to do this.
A simple fix as an example might be:
WITH A(x) AS (SELECT 'a'), B(x) AS (SELECT 'b')
SELECT A.*, B.* FROM A,B
Which adds a form to both WITH table specifications, populates it with values and then ask to list the values next to each other. It
is important to understand that A and B does not exist in the DB, they are just constructs of the imagination of the query and only
valid within the scope of the single select following the construct.
Another possible solution might be:
WITH A(x) AS (SELECT 'a' UNION SELECT 'b')
SELECT A.* FROM A
Which adds form and a union in the table with which added records will be
produced.
Depends what you want. One thing to note, the structure of a WITH statement as
given in the documentation boils down to the form:
WITH table(schema) AS (Initial SELECT Statement [UNION Recursive-Select]) final
SELECT stmt.
(Ref: http://www.sqlite.org/lang_with.html)
After your final Select statement, the constructed "WITH" table no longer exists, it's scope is only visible to the select following
the declaration, so anything after a UNION is a new select and as such cannot refer to anything inside the previous select's
constructs or clauses. This, by the way, is true for any union'd select, you cannot refer to defines from inside the select
preceding the union, it's not just a "WITH" construct quirk. (Normally anyway, it may differ slightly between engines, if anyone
knows more specific, please add a thought).
So to be sure, your query can be interpreted:
WITH A AS (SELECT 'A'), -- Make tables A and B without form and try to
insert literals, which
B AS (SELECT 'B') -- is a bad convention but still possible in SQLite.
SELECT *
FROM A -- read only from the one table named A
UNION
SELECT * -- start a new select
FROM B -- What table B? This second select has no reference to such a
table.
;
As a last option, if you absolutely need to Union the values, a sub-select will do the job of keeping the "with" construct relevant
to the entire select query, maybe something like this:
WITH
A AS (SELECT 'a'),
B AS (SELECT 'b')
SELECT * FROM (
SELECT * FROM A
UNION
SELECT * FROM B
);
Hope this helps!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
