Re: Return multiple copies of same row in table with sequence number
Date: Fri, 7 Mar 2008 19:00:32 -0800 (PST)
Message-ID: <a16e4dec-0ba7-4738-bb55-635cb7fa9277@47g2000hsb.googlegroups.com>
On Mar 7, 8:20 pm, eg..._at_charter.net wrote:
> I want to return multiple copies of data from a table but do not
> understand how to accomplish this. The following code works to return
> a fixed number of copies but I cannot determine how to replace the
> constant with a variable. How do I do this and is there an easier
> way?
>
> I need to print numbered certificates for an employee. I can use the
> following sql to generate a fixed number of certificates but the
> number I need is in a field on the record.
>
> select x.SeqNo,e.EmployeeNo,e.EmployeeName
> from Employees e,
> (select rownum SeqNo from dual connect by level <= 3) x
> where e.EmployeeNo = 78
> order by SeqNo
>
> This returns:
>
> 1 78 Joe
> 2 78 Joe
> 3 78 Joe
>
> What I need is:
>
> select x.SeqNo,e.EmployeeNo,e.EmployeeName
> from Employees e,
> (select rownum SeqNo from dual connect by level <=
> e.CertificateCount) x
> where e.EmployeeNo = 78
> order by SeqNo
>
> However the above SQL returns ORA-00904: "H"."LICENSECOUNT": invalid
> identifier.
>
> I have tried a number of other approaches but none have worked.
>
> Thanks.
Slight modification to your query:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(select rownum SeqNo from dual connect by level <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;
In the above, make certain that 1000 is larger than all values of
e.CertificateCount. You may find that the above approach using
CONNECT BY LEVEL will cause excessive CPU consumption if there are
many rows in the Employees table, or if the value following <= is very
large. A work around would be to either reference one of your large
tables (large number of rows, with few columns) in the inline view, or
to build a single column counter table:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_BIG_TABLE
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;
-or-
CREATE TABLE MY_COUNTER AS
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_COUNTER
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Mar 07 2008 - 21:00:32 CST