Home » SQL & PL/SQL » SQL & PL/SQL » Strange requirement! (Oracle 10g)
icon2.gif  Strange requirement! [message #362303] Mon, 01 December 2008 14:20 Go to next message
gynanda
Messages: 33
Registered: November 2007
Member
Hi All,

I have a simple (?) and strange requirement. I hope someone will give me solution.

I have two columns in my query, which gives only one row output.

Example:

SELECT id, no_prints
FROM c_invoice
WHERE id = 10;

The above query gives only row. But my requirement is to have the same rows as the no_lines column value. For example if no_prints value is 5, my query should get 5 rows as listed below:


id no_prints
10 5
10 5
10 5
10 5
10 5

Thanks in advance.

Cheers,
Nanda

[Updated on: Mon, 01 December 2008 14:22]

Report message to a moderator

Re: Strange requirement! [message #362310 is a reply to message #362303] Mon, 01 December 2008 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which client application do you use?
SQL*Plus returns number of rows.
You can use "compute" SQL*Plus command to add it to your SQL*Plus result.
Any SQL language returns the number of fetched rows.
You can use group by rollup to add it in SQL result.
...

Maybe I misunderstood what you said as you didn't explain what no_lines is, maybe you are searching for ROW_NUMBER function.

In addition, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.


Regards
Michel

[Updated on: Mon, 01 December 2008 14:41]

Report message to a moderator

Re: Strange requirement! [message #362312 is a reply to message #362303] Mon, 01 December 2008 14:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
For me, it seems like another case of row generator usage.
I would prefer the CONNECT BY LEVEL implementation.
Re: Strange requirement! [message #362313 is a reply to message #362303] Mon, 01 December 2008 14:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT dname, deptno
  2  FROM   dept
  3  WHERE  dname = 'ACCOUNTING'
  4  /

DNAME              DEPTNO
-------------- ----------
ACCOUNTING             10

1 row selected.

SCOTT@orcl_11g> SELECT dname, deptno
  2  FROM   (SELECT dname, deptno
  3  	     FROM   dept
  4  	     WHERE  dname = 'ACCOUNTING')
  5  CONNECT BY LEVEL <= deptno
  6  /

DNAME              DEPTNO
-------------- ----------
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10

10 rows selected.

SCOTT@orcl_11g> 

Re: Strange requirement! [message #362328 is a reply to message #362313] Mon, 01 December 2008 16:19 Go to previous messageGo to next message
gynanda
Messages: 33
Registered: November 2007
Member
Thanks for your quick reply and I am really thankful to you people. You are doing great work.

Cheers,
Nanda
Re: Strange requirement! [message #362350 is a reply to message #362303] Tue, 02 December 2008 00:07 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or


SQL> WITH ACCT AS
  2  (SELECT DNAME, DEPTNO
  3       FROM   DEPT
  4       WHERE  DNAME = 'ACCOUNTING'  )
  5  SELECT * FROM ACCT CONNECT BY LEVEL <= DEPTNO;

DNAME              DEPTNO
-------------- ----------
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10
ACCOUNTING             10

10 rows selected.

SQL>
 



Smile
Rajuvan.
Previous Topic: help requred
Next Topic: maximum value of 3 columns as new column
Goto Forum:
  


Current Time: Sat Dec 03 09:43:53 CST 2016

Total time taken to generate the page: 0.06143 seconds