RE: Rows multiplied by a value in a column
Frank Zhou, May 02, 2007 - 4:08 pm UTC
Luke,
There are errors in your "insert" statements.
Here is a alternative solution for your sql question.
http://www.jlcomp.demon.co.uk/faq/mult_row.html Frank
SQL> SELECT item_id ,nvl(item_qty, 1) item_qty ,item_value
2 FROM
3 (SELECT item_id ,item_qty ,item_value
4 FROM items
5 )
6 CONNECT BY PRIOR item_id = item_id
7 and PRIOR item_id = item_id
8 and PRIOR item_value = item_value
9 AND level < ABS(item_qty) +1
10 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL;
ITEM_ID ITEM_QTY ITEM_VALUE
---------- ---------- ----------
1 1 hello
2 1 there
2 2 world
2 2 world
why dual ?
A reader, May 03, 2007 - 5:53 am UTC
Tom, I don't understand your answer
"
with data
as
(select level l
from dual, (select max(nvl(item_qty,1)) max_qty from items)
connect by level <= max_qty
)
select *
from items, data
where l <= nvl(item_qty,1)
"
what is dual needed for ?
it's a bit like someone asking you for
"how can I retrieve all emps"
and you answer
"select e.* from emp, dual"
which of course is correct, but
"select * from emp"
would also do it.
"
with data
as
(select level l
from (select max(nvl(item_qty,1)) max_qty from items)
connect by level <= max_qty
)
select *
from items, data
where l <= nvl(item_qty,1)
"
would also do it
May 03, 2007 - 5:44 pm UTC
we needed a set of N rows where N = max(item_qty)
data is a set of N rows, we use that in a cartesian join to get each row in items output up to N times - based on its qty.
and it is not like "from emp, dual" - for that would be a frivolous use of DUAL, where as in my case - it was needed.
so why does it work without frivolity ?
A reader, May 04, 2007 - 4:47 am UTC
sql > create table items
2 (item_id int,item_qty int,item_value varchar2(10));
Table created.
sql > insert into items values (1,NULL,'hello');
1 row created.
sql > insert into items values (2,1,'there');
1 row created.
sql > insert into items values (2,2,'world');
1 row created.
sql > select * from items;
ITEM_ID ITEM_QTY ITEM_VALUE
---------- ---------- ----------
1 hello
2 1 there
2 2 world
3 rows selected.
sql > with data
2 as
3 (select level l
4 from (select max(nvl(item_qty,1)) max_qty from items)
5 connect by level <= max_qty
6 )
7 select *
8 from items, data
9 where l <= nvl(item_qty,1) ;
ITEM_ID ITEM_QTY ITEM_VALUE L
---------- ---------- ---------- ----------
1 hello 1
2 1 there 1
2 2 world 1
2 2 world 2
4 rows selected.
May 04, 2007 - 1:05 pm UTC
i don't know what you mean here.
My comment was:
from emp, dual
to query emp is a frivolous use of dual - it adds NOTHING to the query, it is not part of getting the answer.
However, using my query, dual is an integral component of the answer - it changes the results, it provides the correct results. It is not frivolous, it was necessary in my query.
hmpf
A reader, May 05, 2007 - 3:56 pm UTC
thumbs down
May 08, 2007 - 10:21 am UTC
why so - because you don't like obvious answers?
dual is an integral component of the answer ?
A reader, May 08, 2007 - 7:08 am UTC
test > create table t(n number);
Table created.
test > insert into t values(2);
1 row created.
test > select level from
2 (select max(nvl(n, 1)) max_n from t)
3 connect by level <= max_n;
LEVEL
----------
1
2
2 rows selected.
test > insert into t values(5);
1 row created.
test > select level from
2 (select max(nvl(n, 1)) max_n from t)
3 connect by level <= max_n;
LEVEL
----------
1
2
3
4
5
5 rows selected.
May 10, 2007 - 8:29 pm UTC
what do you think you just demonstrated?
that you can answer the question without dual?
so?
yes, any ONE ROW table will do, I sort of like dual and its "zero consistent gets" in 10g. But, you need a one row table somewhere and I like DUAL - it is 'the' one row table.
I consistently and constantly use DUAL as the one row table source for generating as many rows as you need.
now I know why
A reader, May 11, 2007 - 5:29 am UTC
and that's all I was asking for,
thx
A record ordinal number
Luke, June 10, 2007 - 5:12 am UTC
Hello again, Tom.
Getting from a similar recordset how to identify record ordinal number in the records with the same ITEM_ID
ITEM_ID ITEM_QTY ITEM_VALUE
---------- ---------- ----------
1 hello
2 1 there
2 2 world
2 2 world
Result should look something like this:
ITEM_ID ITEM_QTY ITEM_VALUE X
---------- ---------- ---------- ----------
1 hello 1
2 1 there 1
2 2 world 2
2 2 world 3
Thanks in advance.
Luke
June 10, 2007 - 9:45 am UTC
does not compute, explain logic.
To Luke
Michel Cadot, June 10, 2007 - 10:48 am UTC
Here's 2 maybe solutions:
SQL> select item_id, item_qty, item_value,
2 row_number() over (partition by item_id order by item_qty) x
3 from t
4 /
ITEM_ID ITEM_QTY ITEM_VALUE X
------- -------- -------------------- -----
1 hello 1
2 1 there 1
2 2 world 2
2 2 world 3
4 rows selected.
SQL> select item_id, item_qty, item_value,
2 row_number() over (partition by item_id order by item_value) x
3 from t
4 /
ITEM_ID ITEM_QTY ITEM_VALUE X
------- -------- -------------------- -----
1 hello 1
2 1 there 1
2 2 world 2
2 2 world 3
4 rows selected.
Regards
Michel