5 ms·
Composable SQL
- bchammer 2y agoOracle Database can do that with a feature called 'SQL Macros' https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/sql_macro-clause.html https://docs.oracle.com/en/database/oracle/oracle-database/2... SQL> create table test_pallets (pallet_id number); Table created. SQL> insert into test_pallets values (1),(2),(3),(4); 4 rows created. SQL> create table test_boxes (pallet_id number, mass decimal); Table created. SQL> insert into test_boxes values (1,10),(1,20),(2, 5),(4, 5),(4, 50); 5 rows created. SQL> create function pallet_payload_mass( 2 p dbms_tf.table_t, 3 b dbms_tf.table_t 4 ) return clob SQL_Macro as 5 begin 6 return q'{ 7 select 8 p.pallet_id, 9 coalesce(sum(b.mass), 0) as payload_mass 10 from 11 p 12 left outer join 13 b on b.pallet_id = p.pallet_id 14 group by 15 p.pallet_id 16 }'; 17 end; 18 / Function created. SQL> select payload_mass, pallet_id 2 from pallet_payload_mass(test_pallets, test_boxes) 3 order by payload_mass; PAYLOAD_MASS PALLET_ID ------------ ---------- 0 3 5 2 30 1 55 4