Cast and Tables

[andyhutch119]

Hi all
I have created an index by table that once processed is populated with a resultset. I would like to process this table to find certain criteria and check for duplicates.One way i,ve seen to do this is by using cast in order to create a Sql statement. Problem is everything I have tried has not worked. Would anyone have an example of this type of casting.
Much appreciated.

 

[sem]

Can you send brief description of your attempts? Just to prevent suggesting already failed techniques.

Regards, Dima

http://seminihin.narod.ru

 

[andyhutch119]

here is a snippet of the code.I,ve only incuded the relevant parts.
-- Create Record
Type call_flow_Rec is record(v_call_string varchar(4));

-- Create table of call_flow records
Type call_flow_Table is table of call_flow_rec
index by binary_integer;
v_CallFlow Call_flow_table;
i_Call_Index number:= 0;
-- Initiate
Call_Table_type call_flow_table;

-- Once processed the table of records is populated
-- I then tried this to create a cast type query

select distinct count(*) into v_Question
from table(cast(Call_table_type as call_flow_table)) as data
where data.v_call_string like '3%';
-- I get expression is wrong type

 

[sem]

I suppose that cast can be used with DATBASE-wide objects, not with that declared within packages, because SQL-engine doesn't even suspect existance of such table:

create or replace type call_flow_Rec as object(v_call_string varchar(4));

create or replace type call_flow_Table is table of call_flow_rec

declare
Call_table_type call_flow_table;
v_Question number;
begin
select distinct count(*) into v_Question
from table(cast(Call_table_type as call_flow_table)) as data
where data.v_call_string like '3%';
end;

Regards, Dima

http://seminihin.narod.ru