This content has been marked as final. Show 5 replies
Can someone please explain CAST, MULTISET and OdciNumberList in small example with a table?Can't imagine a smaller example with all ingredients:
Multiset generates a system-generated collection which is cast'ed to the known collection sys.odcinumberlist.
SQL> select * from table(cast(multiset(select empno from emp) as sys.odcinumberlist)) / COLUMN_VALUE ------------ 7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 14 rows selected.
The TABLE function takes this collection as input and returns the collection elements.
How about another small example with OdciNumberList, CAST MULTISET & CONNECT BY LEVEL?Not sure what you want to see: Examples can be endless ;)
SQL> select * from table (cast (multiset (select level from dual connect by level <= 10) as sys.odcinumberlist)) / COLUMN_VALUE --------------- 1 2 3 4 5 6 7 8 9 10 10 rows selected.
I asked the following questions a while ago. The right answer is there but, it's important to understand what the SQL is actually doing to improve my knowledge.
Not sure what you want to see: Examples can be endless
If you can interpret it, it will be the end of my confusion...
PL/SQL LOOP or INSERT MONTH
For example in above link,
select months_between(to_date(end_date,'YYYYMMDD'),to_date(start_date,'YYYYMMDD'))+1 from payments where no_months_1 = 6
But, if you used in multiset and connect by level, that 7 becomes 1,2,3,4,5,6,7 ? am I correct?
and then in below, column_value -1 makes that 1, a 0 to include start_month and then add_months one by one.
Am I interpreting correctly?
Edited by: 788729 on Aug 24, 2010 5:52 AM
select add_months( to_date(t1.start_date, 'YYYYMMDD') , t2.column_value - 1) as ELIGIBLE_MONTH,