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

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