Re: Return multiple copies of same row in table with sequence number
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