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

From: <egnew_at_charter.net>
Date: Sat, 8 Mar 2008 07:09:39 -0800 (PST)
Message-ID: <55ee6bee-c2b4-4b08-8b02-64fc15fbfc70@47g2000hsb.googlegroups.com>


This works well when a specific employeeno is used in the SQL. However, how do I get the certificates for employees where department=101?

 select level, EmployeeNo, EmployeeName

      from Employees
     connect by EmployeeNo = 78 and level <= CertificateCount
     start with EmployeeNo = 78;

Thanks.

On Mar 8, 7:38 am, Urs Metzger <u..._at_ursmetzger.de> wrote:
> eg..._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- Hide quoted text -
>
> - Show quoted text -
Received on Sat Mar 08 2008 - 09:09:39 CST

Original text of this message