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