Ryan "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 is exactly what I've looked for in the documentation, but I've been unable to find any mentioning of this limitation. The closest I found was this: - The WITH clause must appear at the beginning of a top-level SELECT <http://www.sqlite.org/lang_select.html> statement or at the beginning of a subquery. The WITH clause cannot be prepended to the second or subsequent SELECT statement of a compound select <http://www.sqlite.org/lang_select.html#compound>. There is no mention here that the WITH clause in not visible to subsequent SELECT statements. Knowing this I'll use your "last option" as that seems to do what I'm looking for. Thanks. Staffan On Sat, Jul 12, 2014 at 8:26 PM, RSmith <rsm...@rsweb.co.za> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
