Dynamic FROM example to look forward to...

[juandent]

select depno, sum(salary) as total_sal, sum(bonus) as total_bonus from
(
select e.empno,
e.ename,
e.salary,
e.deptno,
b.type,
e.salary * case 
when b,type = 1 then .1
when b.type = 2 then .2
else .3
end as bonus
from emp e, emp_bonus b
where e.empno = b.empno
and e.deptno =20
) x
group by deptno

The inner select (the one FROM takes its input) can be immediately solved today by the following:

	struct Bonus : alias_tag
	{
		static constexpr std::string get() { return "bonus"; }
	};
	using als_e = alias_e<Employee>;
	using als_b = alias_b<EmpBonus>;


	auto statement = storage.prepare(select(columns(
		alias_column<als_e>(&Employee::m_empno),
		alias_column<als_e>(&Employee::m_ename),
		alias_column<als_e>(&Employee::m_salary),
		alias_column<als_e>(&Employee::m_deptno),
		alias_column<als_b>(&EmpBonus::m_type),
		as<Bonus>(mul(alias_column<als_e>(&Employee::m_salary),
			case_<double>().
			when(c(alias_column<als_b>(&EmpBonus::m_type)) == 1, then(0.1)).
			when(c(alias_column<als_b>(&EmpBonus::m_type)) == 2, then(0.2)).else_(0.3).end()))),
		where(c(alias_column<als_e>(&Employee::m_empno)) == alias_column<als_b>(&EmpBonus::m_empno)
			and c(alias_column<als_e>(&Employee::m_deptno)) == 20)));

	auto sql = statement.expanded_sql();
	auto rows = storage.execute(statement);

What about the outer select? This is an example of a dynamic FROM!

Regards,
Juan Dent

[juandent]

The schema for the above example is:

struct Employee
{
	int m_empno;
	std::string m_ename;
	std::string m_job;
	std::optional<int> m_mgr;
	std::string m_hiredate;
	double m_salary;
	std::optional<double> m_commission;
	int m_deptno;
};

struct Department
{
	int m_deptno;
	std::string m_deptname;
	std::string m_loc;

};

struct EmpBonus
{
	int m_id;
	int m_empno;
	std::string m_received;	// date
	int m_type;
};

using namespace sqlite_orm;

auto storage = make_storage("SQLCookbook.sqlite",
	make_table("Emp",
		make_column("empno", &Employee::m_empno, primary_key(), autoincrement()),
		make_column("ename", &Employee::m_ename),
		make_column("job", &Employee::m_job),
		make_column("mgr", &Employee::m_mgr),
		make_column("hiredate", &Employee::m_hiredate),
		make_column("salary", &Employee::m_salary),
		make_column("comm", &Employee::m_commission),
		make_column("deptno", &Employee::m_deptno),
		foreign_key(&Employee::m_deptno).references(&Department::m_deptno)),
	make_table("Dept",
		make_column("deptno", &Department::m_deptno, primary_key(), autoincrement()),
		make_column("deptname", &Department::m_deptname),
		make_column("loc", &Department::m_loc)),
	make_table("Emp_bonus",
		make_column("id", &EmpBonus::m_id, primary_key(), autoincrement()),
		make_column("empno", &EmpBonus::m_empno),
		make_column("received", &EmpBonus::m_received),
		make_column("type", &EmpBonus::m_type),
		foreign_key(&EmpBonus::m_empno).references(&Employee::m_empno)));