> On Sep 14, 2017, at 10:31 AM, R Smith <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
