I write the following code in PL/SQL and it works:
declare
type deliveryStat_o IS record (
warehouseName varchar2(20), shipMode char(30), thirty_days number, sixty_days number,
ninety_days number, oneTwenty_days number, veryLate number
);
type deliveryStat_t is TABLE OF deliveryStat_o;
statTable deliveryStat_t;
begin
SELECT w_warehouse_name, sm_type, 1 AS thirty_days, 1 AS sixty_days, 1 AS ninety_days,
1 AS oneTwenty_days, 1 AS veryLateDelivery
bulk collect into statTable
FROM catalog_sales, warehouse, ship_mode, date_dim
WHERE cs_ship_date_sk = d_date_sk
AND cs_warehouse_sk = w_warehouse_sk
AND cs_ship_mode_sk = sm_ship_mode_sk
GROUP BY w_warehouse_name,
sm_type ;
end;
How can I do this inside a table function that returns the nested collection statTable. I understand that this can probably be accomplished using explicit cursors; however, is it possible to do it without using a cursor?
For context, I'm starting with this as the base
To convert that to allow a table function, then those types need to be SQL types, hence
Now that this is done, the query must return a table of objects, so
which can now be easily converted to a table function
If you're returning a LOT of rows, then consider a pipelined function instead to avoid the memory overhead of collecting all the rows into the nested table