Home » SQL & PL/SQL » SQL & PL/SQL » how to set extra number of records
how to set extra number of records [message #219479] Wed, 14 February 2007 10:46 Go to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi everyone.

I have a table with following structure and data

invnoice_no product_name qty
1 Mouse 3
1 Keyboard 5


Now, i am looking for a SQL command which give me output like this
====================================
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8

(Total 8 lines/records)
====================================

I have tried the following basic SQL query

Select 'Invoice no 1 --total qty = '|| to_char(sum(qty))
from mytable
where invno=1;


but it gives record only ONE time, not equal to 8 times (why 8? because total quantity is 8 )


any guidence?

Note:
I can do it with the help of function but i need SQL query.

[Updated on: Wed, 14 February 2007 10:48]

Report message to a moderator

Re: how to set extra number of records [message #219492 is a reply to message #219479] Wed, 14 February 2007 11:27 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test_prod;

INVOICE_NO PRODUCT_NAME                QTY
---------- -------------------- ----------
         1 Mouse                         3
         1 Keyboard                      5

SQL> select 'Invoice no 1 --total qty = '||qty as OUTPUT
  2  from (select sum(qty) qty
  3        from test_prod
  4        where invoice_no = 1)
  5  connect by level <= qty;

OUTPUT
-----------------------------------------------------------
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8

Re: how to set extra number of records [message #219501 is a reply to message #219479] Wed, 14 February 2007 11:47 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Dear ebrian.

Thank you very much for your response.

I had never read about the command "connect by" before.
I think basic SQL commands select, from, where, group, order by, fucntions etc are just 10% of all over SQL.

Anyhow, thank you for opening a new window for me.

In connection to above problem, is this possible that i can have blank records in the begining? Infact, my objective is that i am in a supposed situation in which i have to print a "sticker" sheet, on which first two rows of stickers are already removed. So i want to print the total invoice quantity on row 3 of the sheet.

now how can i feed TWO BLANK records at the begining of this output.


Thus my required output is as follow:

OUTPUT
-----------------------------------------------------------
<NULL>
<NULL>
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8


10 records returned



Thanks in advance
Re: how to set extra number of records [message #219526 is a reply to message #219501] Wed, 14 February 2007 14:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
One option would be:

SQL> select * from (
  2     select 'Invoice no 1 --total qty = '||qty as OUTPUT
  3      from (select sum(qty) qty
  4            from test_prod
  5            where invoice_no = 1)
  6      connect by level <= qty
  7     union all
  8     select null from dual
  9     connect by level <= 2)
 10  order by 1 desc;

OUTPUT
-----------------------------------------------------------------


Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8
Invoice no 1 --total qty = 8

10 rows selected.

Re: how to set extra number of records [message #219548 is a reply to message #219479] Wed, 14 February 2007 22:39 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Thank you very much.
Now i have enough idea to meet my objective.

Thanks once again.
Previous Topic: Table Design Suggestions
Next Topic: Explain plan
Goto Forum:
  


Current Time: Fri Dec 09 12:00:21 CST 2016

Total time taken to generate the page: 0.14330 seconds