Re: Return multiple copies of same row in table with sequence number

From: Charles Hooper <hooperc2000_at_yahoo.com>
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

Original text of this message