CODEWITHSUNDEEP
sqloracle-databaserowmultiplication
Marcin Ptak
Marcin Ptak

Reputation: 3

Rows multiplication needed

Problem description: Warehouse... We received 10 pc of one PRODUCT and 3 pc of the same PRODUCT to one container. They have different USER_DEF_NOTE_2 values. table INVENTORY:

SKU_ID;QTY_ON_HAND;CONTAINER_ID;USER_DEF_NOTE_2

SKU1;10;K001;OT 15/2013

SKU1;3;K001;WI 14/2011

I need to print 10 x label with first USER_DEF_NOTE_2 value and 3 with second.

Raporting software input is 1 row = 1 label

How to "multiple rows" in this case?

I did standard join on rownum, and it works fine WITH ONE ROW CASE. (I mean i know how to multiple single row*quantity - but i dont know how to multiply 1*qty1 +2*qty2). My problem is I'm using ROWNUM...and rownum is global.

Any ideas?

Upvotes: 0

Views: 340

Answers (2)

suPPLer
suPPLer

Reputation: 489

Recursive Subquery Factoring clause from 11gR2 to the rescue!

create table test(code varchar2(10), n number);

insert into test values('FIRST', 10);
insert into test values('SECOND', 3);

with t(code, num) as (
  select code, 1 num from test
   union all
  select test.code, t.num + 1 num from t, test
   where t.code = test.code and t.num < test.n  
)
select *
  from t

Try an example here.

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23757

select t.*
from INVENTORY t
join (select level n from dual 
      connect by level <= (select max(QTY_ON_HAND) from INVENTORY)) 
on n <= t.QTY_ON_HAND

Upvotes: 1

Related Questions