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

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sat, 08 Mar 2008 14:38:29 +0100
Message-ID: <fqu4rh$m6c$1@online.de>


egnew_at_charter.net schrieb:
> 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.

No need for DUAL, use your table instead:#

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table Employees as

   2 select rownum as EmployeeNo,

   3         object_name as EmployeeName,
   4         trunc(dbms_random.value(1, 10)) as CertificateCount
   5 from all_objects where rownum < 100;

Table created.

SQL> alter table Employees

   2 add constraint pk_employees primary key(EmployeeNo);

Table altered.

SQL> select *

   2 from Employees
   3 where EmployeeNo = 78;

EMPLOYEENO EMPLOYEENAME                   CERTIFICATECOUNT
---------- ------------------------------ ----------------
         78 V$FAST_START_SERVERS                          7

SQL> select     level, EmployeeNo, EmployeeName
   2        from Employees

   3 connect by EmployeeNo = 78 and level <= CertificateCount    4 start with EmployeeNo = 78;

      LEVEL EMPLOYEENO EMPLOYEENAME

---------- ---------- ------------------------------
          1         78 V$FAST_START_SERVERS
          2         78 V$FAST_START_SERVERS
          3         78 V$FAST_START_SERVERS
          4         78 V$FAST_START_SERVERS
          5         78 V$FAST_START_SERVERS
          6         78 V$FAST_START_SERVERS
          7         78 V$FAST_START_SERVERS

7 rows selected.

Urs Metzger Received on Sat Mar 08 2008 - 07:38:29 CST

Original text of this message