Re: Return multiple copies of same row in table with sequence number
Date: Sat, 8 Mar 2008 06:26:39 -0800 (PST)
Message-ID: <1c7a2dd6-51a4-4bd8-9e9b-734e769940c9@u72g2000hsf.googlegroups.com>
Urs Metzger:
Thank you very much. Your solution is what I needed.
Thanks, Sidney
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 - 08:26:39 CST