I know that if I have a simple table with a collection (nested table) I can do the simple select to get the columns...

in Oracle's simple example:

</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=69467.1 <code>

Example 21 - Collection Unnesting using the TABLE operator
----------------------------------------------------------

SELECT p.FirstName, p.LastName, a.*
FROM People p, TABLE(p.PrevAddrs) a
WHERE p.LastName = 'Ellison';

the result gives:
p.FirstName p.LastName a.Street a.City a.State a.Zip
------------ ----------- -------------- ---------- --------- --------
Mr. Ellison 2 Second Street Orlando FL 34812
Mr. Ellison 3 Third Street Orlando FL 34812
Mrs. Ellison 2 Second Street Orlando FL 34812
Mrs. Ellison 3 Third Street Orlando FL 34812
Larry Ellison 2 Second Street Orlando FL 34812
Larry Ellison 3 Third Street Orlando FL 34812


I am doing something similar...

create or replace type step_status_rec is object (step_work_id number, step_num
number, step_stat varchar2(10), update_date DATE);

create or replace type step_status_type is table of step_status_rec;

CREATE TABLE ROLLUP_WORKFLOW (
WORK_ID NUMBER NOT NULL,
WORK_STATUS CHAR(1) NULL,
STEP_STATUS step_status_type null,
CONSTRAINT ROLLUP_WORKFLOW_PKN
PRIMARY KEY (WORK_ID)
)
NESTED table STEP_STATUS store as STEP_STATUS_TAB
/

This table stores a "work order", which has many steps (each with it's own status). So I store them in a collection in a single row for the work order...


this query shows all steps and their statuses...


select w1.work_id, w2.step_work_id, w2.step_num, w2.step_stat
from rollup_workflow w1, table(w1.step_status) w2
where w1.work_id = w2.step_work_id
order by w1.work_id,w2.update_date asc
/

result: (note, I am storing the table PK inside the collection as well, I thought it was needed for the join from w1 to w2)...

WORK_ID STEP_WORK_ID STEP_NUM STEP_STAT
---------- ------------ ---------- ----------
1 1 0 R
1 1 1
1 1 2
1 1 4
1 1 5
1 1 3
2 2 0 R
2 2 1
2 2 2
2 2 3
2 2 4
2 2 5


however, I am writing a stored procedure which takes this data into a cursor (loop) and then does other work...

My problem is this FAILS:

declare cursor cur1 is select w1.work_id, w2.step_work_id, w2.step_num, w2.step_stat
from rollup_workflow w1, table(w1.step_status) w2
where w1.work_id = w2.step_work_id
and w2.STEP_NUM = p_step_num -- even without this row it fails
order by w1.work_id,w2.update_date asc;
begin
null;
end;
/

from rollup_workflow w1, table(w1.step_status) w2
*
ERROR at line 2:
ORA-06550: line 2, column 32:
PLS-00201: identifier 'W1.STEP_STATUS' must be declared
ORA-06550: line 1, column 24:
PL/SQL: SQL Statement ignored
...

So I tried to rewrite the second half of the "from" statement to...

declare cursor c1 is select select w1.work_id, w2.step_work_id, w2.step_stat
from rollup_workflow w1, table(select step_status from rollup_workflow) w2
where w1.work_id = w2.step_work_id
and w2.STEP_NUM = p_step_num
order by w2.update_date asc;

THIS COMPILES.. however, when I run it.. I get this:

ERROR at line 2:
ORA-02324: more than one column in the SELECT list of THE subquery

The new FROM statement is not bringing back results in an acceptable way.. also should I be using the keyword "THE"? how do I use it?

thanks.