Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Creating Dummy Records

601286Feb 13 2009 — edited Feb 15 2011
Hi, wondering if someone can give me some advise..I'm needing to create dummy records off some data, I need 10 total rows for each account number ( Field 2 ).

I've tried a few different ways such as using a ROW_NUMBER() over (partition by FIELD 2) then doing an outer join to

(select rownum r,field 1, field 2 from all_objects
where rownum < 11)

based on row num but I can't get this to work. I can't do this in my data, it has to be in a SQL statement. Anyone have suggestions?


My Data

Field 1 Field 2
10 1029
20 1029
10 1131
20 1131
30 1131
40 1131
50 1131

Desired Result

Field 1 Field 2
10 1029
20 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
10 1131
20 1131
30 1131
40 1131
50 1131
0 1131
0 1131
0 1131
0 1131
0 1131

Comments

isotope
user598283 wrote:
Hi, wondering if someone can give me some advise..I'm needing to create dummy records off some data, I need 10 total rows for each account number ( Field 2 ).

I've tried a few different ways such as using a ROW_NUMBER() over (partition by FIELD 2) then doing an outer join to

(select rownum r,field 1, field 2 from all_objects
where rownum < 11)

based on row num but I can't get this to work. I can't do this in my data, it has to be in a SQL statement. Anyone have suggestions?


My Data

Field 1 Field 2
10 1029
20 1029
10 1131
20 1131
30 1131
40 1131
50 1131

Desired Result

Field 1 Field 2
10 1029
20 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
0 1029
10 1131
20 1131
30 1131
40 1131
50 1131
0 1131
0 1131
0 1131
0 1131
0 1131
Something like this ?
test@XE> --    
test@XE> with t as (
  2    select 10 x, 1029 y from dual union all
  3    select 20, 1029 from dual union all    
  4    select 10, 1131 from dual union all    
  5    select 20, 1131 from dual union all    
  6    select 30, 1131 from dual union all    
  7    select 40, 1131 from dual union all    
  8    select 50, 1131 from dual)             
  9  --                                       
 10  select coalesce(q.x,p.x) as x,           
 11         p.y,                              
 12         p.pos                             
 13  from                                     
 14  (                                        
 15    select distinct 0 x, y, i.pos          
 16      from t,                              
 17           (select level pos from dual connect by level<=10) i
 18  ) p,                                                        
 19  (                                                           
 20    select x, y,                                              
 21           row_number() over (partition by y order by y,x) as rn
 22      from t
 23  ) q
 24  where p.y = q.y(+)
 25  and p.pos = q.rn(+)
 26  order by p.y, p.pos;

         X          Y        POS
---------- ---------- ----------
        10       1029          1
        20       1029          2
         0       1029          3
         0       1029          4
         0       1029          5
         0       1029          6
         0       1029          7
         0       1029          8
         0       1029          9
         0       1029         10
        10       1131          1
        20       1131          2
        30       1131          3
        40       1131          4
        50       1131          5
         0       1131          6
         0       1131          7
         0       1131          8
         0       1131          9
         0       1131         10

20 rows selected.

test@XE>
test@XE>
isotope
Tubby
Based on the data you've provided, this would seem to do it. 
ME_XE?with data as
  2  (
  3     select 10 as col1, 1029 as col2 from dual union all
  4     select 20 as col1, 1029 as col2 from dual union all
  5     select 10 as col1, 1131 as col2 from dual union all
  6     select 20 as col1, 1131 as col2 from dual union all
  7     select 30 as col1, 1131 as col2 from dual union all
  8     select 40 as col1, 1131 as col2 from dual union all
  9     select 50 as col1, 1131 as col2 from dual
 10  ),
 11     max_10_rows as
 12  (
 13     select (10 * d1.col1) as col1, d.col2
 14     from
 15     (select distinct col2 from data) d, (select level as col1 from dual connect by level<=10) d1
 16  )
 17  select nvl(d.col1, 0), m.col2
 18  from max_10_rows m, data d
 19  where m.col1 = d.col1 (+)
 20  and   m.col2 = d.col2 (+);

     NVL(D.COL1,0)               COL2
------------------ ------------------
                10               1029
                20               1029
                10               1131
                20               1131
                30               1131
                40               1131
                50               1131
                 0               1131
                 0               1131
                 0               1131
                 0               1029
                 0               1029
                 0               1029
                 0               1029
                 0               1029
                 0               1029
                 0               1029
                 0               1131
                 0               1131
                 0               1029

20 rows selected.

Elapsed: 00:00:00.10
ME_XE?
Aketi Jyuuzou
In this case,it is effective to use "partitioned Outer Join" B-)
with data as(
select 10 as col1, 1029 as col2 from dual union all
select 20 as col1, 1029 as col2 from dual union all
select 10 as col1, 1131 as col2 from dual union all
select 20 as col1, 1131 as col2 from dual union all
select 30 as col1, 1131 as col2 from dual union all
select 40 as col1, 1131 as col2 from dual union all
select 50 as col1, 1131 as col2 from dual)
select nvl(b.Col1,0) as Col1,b.Col2
  from table(sys.odciNumberList(1,2,3,4,5,6,7,8,9,10)) a
       Left Join
       (select col1,col2,
        Row_Number() over(partition by col2 order by col1) as rn
          from data) b
       partition by (b.col2)
       on (b.rn = a.Column_Value)
order by b.Col2,a.Column_Value;

COL1  COL2
----  ----
  10  1029
  20  1029
   0  1029
   0  1029
   0  1029
   0  1029
   0  1029
   0  1029
   0  1029
   0  1029
  10  1131
  20  1131
  30  1131
  40  1131
  50  1131
   0  1131
   0  1131
   0  1131
   0  1131
   0  1131
Aketi Jyuuzou
We can use model solution,too :8}

I think that we should use "model clause".
Because in this case, values from 1 to 10 are literal.
select col2,col1
  from data
 model
 partition by(col2)
 dimension by(Row_Number() over(partition by col2 order by col1) as rn)
 measures(col1)
 rules(col1[for rn from 1 to 10 INCREMENT 1] 
     = presentv(col1[cv()],col1[cv()],0));
601286
Awesome! Thanks a bunch on your solutions, was stuck on how to join a partition...ended up using that solution. I'll check out the Model - I've seen that however have never used.

Thanks again!!
677036
Experts,

I have a little different requirement but i guess this is the right thread to get its solution.
The query may return any no of rows and i need to add one dummy row to it.

Eg
Select SSN,character from number returns
obtained output
PAN a
NID b

Desired output
PAN a
NID b
NA c

One way to achieve this is

Select SSN,character from number returns
union select 'NA','c' from dual;
PAN a
NID b
NA c


Not that i dislike union but,i am not supposed to use unionl.


Any way to accomplish this?
BluShadow
user8016805 wrote:
Not that i dislike union but,i am not supposed to use union.
Why not? That's what Oracle put it there for.
You can use "UNION ALL" if it helps.

SQL data is "set" based, and "UNION" is a set operator. You should use it to "union" the sets of data together. That's it's purpose.
677036
In one of the functionality,we are working,the clauses of a query are stored separately and are combined at runtime.
If i store union all as part of where clause,syntactical errors are thrown.
In nutshell,i cannt use union.

Any other way to accomplish this?
BluShadow
user8016805 wrote:
In one of the functionality,we are working,the clauses of a query are stored separately and are combined at runtime.
Ooo, do you work for a bank? Can I come and use your system. Good opportunity for SQL injection and transferring money to my own account?
Seriously, that sort of dynamic creation of queries is seriously open to abuse and problems.
If i store union all as part of where clause,syntactical errors are thrown.
So, rewrite the code that's creating the dynamic query to deal with it. Don't try and fix the symptoms, fix the cause.
In nutshell,i cannt use union.
You can and you should, in any well designed application.
Any other way to accomplish this?
Yes, but it's not pretty and not what you'd really want to do (especially on a lot of data), because usually you would just use UNION.
e.g.
SQL> select empno, ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> select distinct
  2         case when lead(empno) over (order by empno) is null then 0 else empno end as empno
  3        ,case when lead(empno) over (order by empno) is null then '!NEW!' else ename end as ename
  4  from emp cross join (select rownum rn from dual connect by rownum <= 2)
  5  order by empno
  6  /

     EMPNO ENAME
---------- ----------
         0 !NEW!
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

15 rows selected.

SQL>
There can be other ways too of generating additional rows, perhaps with the MODEL clause and suchlike, but really they are all over-the-top for something that should be done with a UNION.
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 15 2011
Added on Feb 13 2009
9 comments
13,202 views