Oracle Database can do that with a feature called 'SQL Macros'<p><a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/sql_macro-clause.html" rel="nofollow">https://docs.oracle.com/en/database/oracle/oracle-database/2...</a><p>SQL> create table test_pallets (pallet_id number);<p>Table created.<p>SQL> insert into test_pallets values (1),(2),(3),(4);<p>4 rows created.<p>SQL> create table test_boxes (pallet_id number, mass decimal);<p>Table created.<p>SQL> insert into test_boxes values (1,10),(1,20),(2, 5),(4, 5),(4, 50);<p>5 rows created.<p>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 /<p>Function created.<p>SQL> select payload_mass, pallet_id
2 from pallet_payload_mass(test_pallets, test_boxes)
3 order by payload_mass;<p>PAYLOAD_MASS PALLET_ID
------------ ----------
0 3
5 2
30 1
55 4