> On Sep 14, 2017, at 10:31 AM, R Smith <rsm...@rsweb.co.za> wrote: > > I think that's actually one of the things one of the posters tried and found > not to work. The problem is the WITH syntax in theory is just-like > constructing a temporary table and using the values from there, but in > reality (AFAIK) it's just a mathematical construct that re-routes the query, > so anything that is a parameter to a function (or a function itself) in the > WITH clause gets re-evaluated every time the main query refers to the WITH > table.
Yes, I tried using WITH and it behaves as you describe — like a type of high-level macro that gets expanded every place it's used. So it's useless for CSE. > Perhaps if you can force the WITH into building an in-memory table with the > results from, say a recursive function, it would indeed be a possibility That would almost certainly have more overhead (in my case) than the redundant function calls. What I'd like would be something like a "let" statement in a functional language, which would bind a name to a value that's computed once no matter how many times it's referenced in the SELECT. For example: LET foo = computeFoo(table.col) IN SELECT derivedvalue(foo) FROM table WHERE sometest(foo) AND anothertest(foo) —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users