It sounds like you guys have a solution, but I wanted a POC for this that we could check into our library, so I came up with this\u2026<\/p>\n
\r\nSQL> create or replace type expr_skulist_type as object(skulist number,\r\n 2 item varchar2(25),\r\n 3 int_action_date date,\r\n 4 int_action_type varchar2(1),\r\n 5 tran_level number,\r\n 6 item_level number);\r\n 7 \/\r\n\r\nType created.\r\n\r\nSQL> create or replace function expr_skulists return skulists pipelined as\r\n 2 l_expr_skulist_type expr_skulist_type;\r\n 3 begin\r\n 4 for r in (select skulist,src.item,int_action_date,int_action_type, src.tran_level, src.item_level\r\n 5 from expr_integration.stg_out_skulist_detail src\r\n 6 inner join rms14.item_master on(src.item=item_master.item)\r\n 7 where item_master.status ='A'\r\n 8 --and src.int_action_date > to_timestamp('2016-11-15 14:28:57', 'YYYY-MM-DD HH24:MI:SS')\r\n 9 and rownum <= 10) loop\r\n10 l_expr_skulist_type := expr_skulist_type(r.skulist,r.item,r.int_action_date,r.int_action_type,r.tran_level,r.item_level);\r\n11 pipe row(l_expr_skulist_type);\r\n12 end loop;\r\n13 end;\r\n14 \/\r\n\r\nFunction created.\r\n\r\nSQL> select * from table(expr_skulists());\r\n\r\n SKULIST ITEM INT_ACTIO I TRAN_LEVEL ITEM_LEVEL\r\n---------- ------------------------- --------- - ---------- ----------\r\n 1044 75991377 02-AUG-16 A 2 2\r\n 1044 75996974 02-AUG-16 A 2 2\r\n 1044 75996987 02-AUG-16 A 2 2\r\n 1044 75996990 02-AUG-16 A 2 2\r\n 1044 75997009 02-AUG-16 A 2 2\r\n 1044 75997025 02-AUG-16 A 2 2\r\n 1044 75997038 02-AUG-16 A 2 2\r\n 1044 75997041 02-AUG-16 A 2 2\r\n 1044 75997054 02-AUG-16 A 2 2\r\n 1044 75997070 02-AUG-16 A 2 2\r\n\r\n10 rows selected.\r\n\r\nSQL>\r\n<\/pre>\nThe trick is to sort the select, and as you work your way through the dataset, check if the skulist and item has changed, \u201creset\u201d your logic. As you process a given skulist and item, you can calculate the logic as you process each row. For example, is the int_action_date_greater the previous date? Have you already seen tran_level = 1 and this one is 2? Based on what you find, you either call the pipe row() line, or not.<\/p>\n
Thanks,<\/p>\n
Steve<\/p>\n","protected":false},"excerpt":{"rendered":"
It sounds like you guys have a solution, but I wanted a POC for this that we could check into our library, so I came up with this\u2026 SQL> create or replace type expr_skulist_type as object(skulist number, 2 item varchar2(25),…<\/p>\n