This message went to the wrong address, apologies, herewith the repost:
On 2014/04/06 20:23, Dominique Devienne wrote:
On Sat, Apr 5, 2014 at 11:46 AM, RSmith <rsm...@rsweb.co.za> wrote:
WITH csvrec(i,l,c,r) AS (
SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv
UNION ALL
SELECT i,
instr(c,',') AS vLen,
substr(c,instr(c,',')+1) AS vRem,
substr(c,1,instr(c,',')-1) AS vCSV
FROM csvrec
WHERE vLen>0
)
SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt
WHERE t.ID=rt.i AND rt.r<>''
ORDER BY t.ID
LIMIT 100
Very interesting. Thanks for sharing that. But can this CTE be turned
into a view? Or does one need to retype the whole "algorithm" every
time one needs "join" on the "virtual" unrolled CSV field table? And
assuming such a "CTE view" can de defined, what if one selects from
the "CTE view" with a WHERE clause, to get only the CSV fields of a
single row of scvrec, would that prevent the whole "tmpcsv" result-set
for every row of csvrec to be generated?
Hi Dominique, there is no restriction I know of in the view (result set) of any
sql query, It's much like a table and should honour
more or less any query requested of it - but please know I have no testing to
confirm this with the specific CTE.
As to the second part of the question, the query in the case I posted does the
entire result set, as is caused by the line loading
the first record set into the recursive table, namely:
SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv
There is however nothing stopping you from adding a WHERE clause behind that to
simply pick out single records or specific sets of
it to work with, etc.
If the answer to either question above is true, then a specialized
vtable would be both more convenient and faster, no?
Your CTE has the great benefit to work out of the box though, unlike a
vtable, so it's a great example nonetheless. Thanks again for that.
I can't agree more, and please know I simply made up this bit of CTE SQL
because the question was asked and responses ranged from
very difficult to impossible in SQL, (oh yes, and it was fun to do) , but I
would never really dream of actually implementing this
in anything real for both performance and scalability reasons, however, the OP
had a specific problem in that they were faced with a
DB with specific tables and layout which, for legacy reasons, couldn't be
adjusted to 1NF and did not wish to go VT but just needed
to have that one instance of getting that specific data to be de-CSV'd - which
the CTE will do very well out of the box, nothing
else needed - problem solved and easy win, but I wouldn't actually develop a
new DB with it (obviously).
(FWIW - It's pretty fast, only marginally slower than one of the CSV add-ons I
use, which is really a feather in the cap of the CTE
implementation in SQLite)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
