Hi again,
"sub-select" method :
- allows you to do only a part of what you can do in CTE,
- becomes more and more difficult to read as you add tables and treatment
in your sql.
With CTE in SQLite, it would be possible to:
- decompose your SQL treatment in clean intermediate steps,
- make your global algorithm "visible",
- do easily things currently impossible in 1 query with sub-selects.
As an example :
"""
With
-- aggregate detail informations you need
product as (select ....)
, target(item, date, target) as (select ....)
, actual(item, date, actual ) as (select ....)
-- prepare a virtual 'result' comparison table, step1
, comparison(item, date, actual, target) as (
select item, date, sum(0.0), sum(target) from target
UNION ALL
select item, date, sum(actual), sum(0.0) from actuals)
-- compactify on the same line target and actual figures
, result(item, date, actual, target) as (
select item, date, sum(actual), sum(target)
from comparison
group by item, date)
-- final request like if everything was simple from the start
select * from results inner join product on results.item = product.item
"""
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
