​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
A technique to bring out multiple records for each database record retrieved

 

Let suppose we have a database table containing 2 records A and B as shown below.

 

SQL> select * from tom

/

X

-

A

B

 

We want to bring out , say, 3 identical records for each of them.  Here’s  a technique you can use to do this.

 

selectt,i

from

(

Select x t, column_valuei

From tom, table(cast(multiset(

Select level from dual

Connect by level <  4)

As sys.odciNumberList

)))

 

SQL>/

T              i

=========

A             1

A             2

A             3

B             1

B             2

B             3

 

It’s also possible to bring out a variable number of records if we can specify a different N for each record. Let’s say we want 1 of record 1, 2 of record 2 and 3 for records 3. Consider this.

SQL> select * from tom;

 

X

==

A

AB

ABC

 

 

selectt,i

from

(

Select x t, column_valuei

From tom, table(cast(multiset(

Select level from dual

Connect by level <  length(x) +1)

As sys.odciNumberList

)))


SQL>/

T           I
== ===
A            1
AB          1
AB          2
ABC        1
ABC        2
ABC        3