asked on

Cursor expressions need to suppress the statement 2 in the query output.

Hi Team ,

Iam using a cursor expression in my query . In the query output I need to exclude the CURSOR STATEMENT : 2 statement fron the output and show only the column values . Any help is really appreciated.

Executive CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

FIRST_NAME
--------------------
Steven
Neena
Lex

Finance CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

FIRST_NAME
--------------------
Nancy
Daniel
John%
Ismael
Jose Manuel
Luis

6 rows selected.

Query :

SELECT
d.department_name ,
cursor (select first_name from employees e where e.department_id=d.department_id) employeeInfo
from departments d

Qlemo

What should the final result be? The obvious choices are

to run a traditional inner join, generating a line with department name and first name for each first name and department:
```
Executive    Steven
Executive    Neena
Finance      Nancy
Finance      Daniel
```
Open in new window
to collect multiple first names into a comma separated list:
```
Executive    Steven,Neena
Finance      Nancy,Daniel
```
Open in new window

ASKER CERTIFIED SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Swaminathan K

ASKER

I want to Executive
Steven
Neena
Sales
Vinod
Gopal

Alex [***Alex140181***]

If it's "pure" SQLPLUS, you may try this (adept to your needs/setup; I took the "old" HR schema):

set pages 0;
break on department_name skip 1;
select a.department_name,
       b.first_name || ' ' || b.last_name ename
  from hr.employees b,
       hr.departments a
 where b.department_id = a.department_id
 order by a.department_name;
/

Open in new window

Swaminathan K

ASKER

thanka a lot