On Mon, Jun 20, 2016 at 10:21 AM, Bernard Ertl <bern...@interplansystems.com > 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? > Try something like this: select c1, c2, CurrentDate, ac, MyFn(c1,ac,CurrentDate) as xc1, MyFn(c2,ac,CurrentDate) from (select c1, c2, CurrentDate, (case ActiveCol when 0 then c3 else c4 end) as ac from test) Other possibilities exist to avoid restating the case expression mutiple times, but this should work. Haven't tested it with real data because, well, I don't have real data with which to test. -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
