Return multiple copies of same row in table with sequence number

#21

egnew (AT) charter (DOT) net schrieb:

Quote:

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 (DOT) de> wrote:
eg... (AT) charter (DOT) 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

#22

Quote:

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

Urs Metzger

select level seqno,employeeno, employeename, certificatecount
from employees
where department=101
connect by level<=certificatecount
and prior employeeno = employeeno
and prior dbms_random.value is not null

Best regards

Maxim

#23

Quote:

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

Urs Metzger

select level seqno,employeeno, employeename, certificatecount
from employees
where department=101
connect by level<=certificatecount
and prior employeeno = employeeno
and prior dbms_random.value is not null

Best regards

Maxim

#24

Quote:

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

Urs Metzger

select level seqno,employeeno, employeename, certificatecount
from employees
where department=101
connect by level<=certificatecount
and prior employeeno = employeeno
and prior dbms_random.value is not null

Best regards

Maxim

#25

Quote:

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

Urs Metzger

select level seqno,employeeno, employeename, certificatecount
from employees
where department=101
connect by level<=certificatecount
and prior employeeno = employeeno
and prior dbms_random.value is not null

Best regards

Maxim

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Return multiple copies of same row in table with sequence number

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Return multiple copies of same row in table with sequence number - 03-08-2008 , 09:38 AM

Re: Return multiple copies of same row in table with sequence number - 03-08-2008 , 10:12 AM

Re: Return multiple copies of same row in table with sequence number - 03-08-2008 , 10:12 AM

Re: Return multiple copies of same row in table with sequence number - 03-08-2008 , 10:12 AM

Re: Return multiple copies of same row in table with sequence number - 03-08-2008 , 10:12 AM