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

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Sat, 08 Mar 2008 16:38:01 +0100
Message-ID: <fqubrl$veo$1@online.de>

egnew_at_charter.net schrieb:

> 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 -

>
OK, here we need DUAL (at least I do):

  select SeqNo, EmployeeNo, EmployeeName, CertificateCount     from Employees, (select level as SeqNo

                        from dual
                     connect by level <= (select max(CertificateCount)
                                            from Employees))
   where SeqNo <= CertificateCount
     and Department = 101

   order by EmployeeNo, SeqNo;

If you already know max(CertificateCount) you should of course replace (select max(CertificateCount) from Employees)) by the known max value.

Urs Metzger Received on Sat Mar 08 2008 - 09:38:01 CST