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.

On Mar 7, 8:20*pm, eg... (AT) charter (DOT) net wrote:
Slight modification to your query:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(select rownum SeqNo from dual connect by level <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

In the above, make certain that 1000 is larger than all values of
e.CertificateCount. You may find that the above approach using
CONNECT BY LEVEL will cause excessive CPU consumption if there are
many rows in the Employees table, or if the value following <= is very
large. A work around would be to either reference one of your large
tables (large number of rows, with few columns) in the inline view, or
to build a single column counter table:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_BIG_TABLE
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

-or-
CREATE TABLE MY_COUNTER AS
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;

select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_COUNTER
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Mar 7, 8:20*pm, eg... (AT) charter (DOT) net wrote:
Slight modification to your query:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(select rownum SeqNo from dual connect by level <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

In the above, make certain that 1000 is larger than all values of
e.CertificateCount. You may find that the above approach using
CONNECT BY LEVEL will cause excessive CPU consumption if there are
many rows in the Employees table, or if the value following <= is very
large. A work around would be to either reference one of your large
tables (large number of rows, with few columns) in the inline view, or
to build a single column counter table:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_BIG_TABLE
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

-or-
CREATE TABLE MY_COUNTER AS
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;

select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_COUNTER
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Mar 7, 8:20*pm, eg... (AT) charter (DOT) net wrote:
Slight modification to your query:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(select rownum SeqNo from dual connect by level <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

In the above, make certain that 1000 is larger than all values of
e.CertificateCount. You may find that the above approach using
CONNECT BY LEVEL will cause excessive CPU consumption if there are
many rows in the Employees table, or if the value following <= is very
large. A work around would be to either reference one of your large
tables (large number of rows, with few columns) in the inline view, or
to build a single column counter table:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_BIG_TABLE
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

-or-
CREATE TABLE MY_COUNTER AS
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;

select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_COUNTER
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Mar 7, 8:20*pm, eg... (AT) charter (DOT) net wrote:
Slight modification to your query:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(select rownum SeqNo from dual connect by level <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

In the above, make certain that 1000 is larger than all values of
e.CertificateCount. You may find that the above approach using
CONNECT BY LEVEL will cause excessive CPU consumption if there are
many rows in the Employees table, or if the value following <= is very
large. A work around would be to either reference one of your large
tables (large number of rows, with few columns) in the inline view, or
to build a single column counter table:
select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_BIG_TABLE
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

-or-
CREATE TABLE MY_COUNTER AS
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;

select x.SeqNo,e.EmployeeNo,e.EmployeeName
from Employees e,
(SELECT
ROWNUM SeqNo
FROM
MY_COUNTER
WHERE
ROWNUM <= 1000) x
where e.EmployeeNo = 78
and x.SeqNo <= e.CertificateCount
order by SeqNo;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

egnew (AT) charter (DOT) net schrieb:

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

egnew (AT) charter (DOT) net schrieb:

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

egnew (AT) charter (DOT) net schrieb:

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

egnew (AT) charter (DOT) net schrieb:

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

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 (DOT) de> wrote: