On 2016/06/20 6:21 PM, Bernard Ertl wrote:
Is it possible to do something like this:
SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END) AS ac,
MyFn(c1,ac,CurrentDate) AS xc1, MyFn(c2,ac,CurrentDate) AS xc2 FROM ...
where c1, c2, CurrentDate, ActiveCol, c3, c4 are all columns in the table(s)
referenced in the FROM
clause?
When I try to run the query, I get errors that ac isn't a valid column. Is
it not possible to reference aliased columns as a parameter to a (custom)
function? I could just put the CASE statement in the (MyFn) function's
parameter field, but I'm calling the function 4 times in a single SELECT
statement. Wouldn't that cause SQLite to evaluate the CASE statement each
time?
You cannot dereference an alias in a same-level query clause directly
since it is hard to predict it's intended value kind (Concats, groups
concats and custom User functions especially) and there is no telling
what data type its result may end up as, and other less important
problems. It's nothing to do with custom functions, you can't even use
an alias in a simple built-in function, even this is illegal:
SELECT (x+5) AS MyX, (MyX * 2) AS My2X... -- Illegal since MyX is not a
valid column. You can see why this is a problem if you imagine the
following:
SELECT (x||'-'||y) AS XY, (XY * 3) AS 3XY, ...
- OR -
SELECT (M + Z) AS MZ, (MZ - M) AS Z, ... - Obviously impossible.
Of course, if you complete the output of the query into a table/set of
which these things can be known, and then select from that set,
everything becomes possible again.
Many ways to skin that cat in SQLite, but the two most common is using a
Query as a table or using a Common Table Expression (CTE). I will try to
make queries here demonstrating both, but I don't have the sort of data
or schema you have, so they are untested. Feel free to ask if you don't
get these to work, we'll try fix it.
Option 1 (Sub-Query):
SELECT c1, c2, CurrentDate, ac, MyFn(c1,ac,CurrentDate) AS xc1,
MyFn(c2,ac,CurrentDate) AS xc2
FROM (
SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4
END) AS ac
FROM ...
) AS Q
WHERE ...
Option 2 (CTE):
WITH Q (c1, c2, cd, ac) AS (
SELECT c1, c2, CurrentDate, (CASE ActiveCol WHEN 0 THEN c3 ELSE c4 END)
FROM ...
)
SELECT c1, c2, cd, ac, MyFn(c1,ac,CurrentDate) AS xc1,
MyFn(c2,ac,CurrentDate) AS xc2
FROM Q
WHERE ...
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
