CODEWITHSUNDEEP
collectionsplsqloracle11g
Casey
Casey

Reputation: 223

pl/sql nested table loop

I have a pl/sql nested table collection that contains the below elements:

AG~AG~1~14
US~BRANCH~1~24
NO~NO~2~10
KI~296~2~13
AI~AI~2~21

I have to look at the value in the 3rd subelement with each of these elements(not sure how to put it !) and pick the one that have the highest value. In the above data, the 3rd subelements are 1,1,2,2,2 (immediately following the second ~). Obviously here, the elements containing 2 are higher. So, 3 of them meet the criteria. Further, from the 3 of them, I have to look at the 4th subelement and ultimately pick the one that has the highest value. So, the 4th subelement for these 3 are: 10,13,21 Since 21 is the highest, the final output is to pick the element AI~AI~2~21.

I am struggling how to do this in the best possible way. I tried various combinations of instr and substr and looped them to compare. But, its not modular enough. I could also tokenize the string from each element and push into a global temp table that is valid for the session and then use oracle sql to get the final data. but, I want to avoid having to use a table and maintain it etc and if possible, like to keep it within pl/sql.

declare
TYPE final_score_typ IS TABLE OF varchar2(1000);

l_final_score final_score_typ;

l_final_output varchar2(20);

begin

<code logic that populates the nested table containing the above data>

for j in 1..l_final_score.count loop

    dbms_output.put_line(l_final_score(j));

end loop;

dbms_output.put_line('final output string is:' || l_final_output);


end;

The final output should be printed as AI~AI~2~21 Any pointers would be much appreciated. I could atleast try based on them...Right now, cannot think of good alternatives.

Upvotes: 0

Views: 391

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132570

There is an existing database type you can use for a table of VARCHAR2(1000): `

SYS.DBMS_DEBUG_VC2COLL

You could use it like this:

declare
   l_data SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL
                                       ('AG~AG~1~14'
                                       ,'US~BRANCH~1~24'
                                       ,'NO~NO~2~10'
                                       ,'KI~296~2~13'
                                       ,'AI~AI~2~21'
                                       );
   l_result varchar2(100);
begin
   select column_value 
   into l_result
   from
   ( select column_value 
          , row_number() over
               (order by substr(column_value
                               ,instr(column_value,'~',1,2)+1
                               ,instr(column_value,'~',1,3)
                                    -instr(column_value,'~',1,2)-1
                               ) desc
               ,         substr(column_value,instr(column_value,'~',1,3)+1) desc
               ) as rn
     from table (l_data)
   ) where rn = 1;
   dbms_output.put_line(l_result);
end;

(Or you could use it with Egor's much more elegant regexp_substr code of course.)

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

select min(column_value) keep (dense_rank first order by 
  to_number(regexp_substr(column_value, '[^~]+', 1, 3)) desc, 
  to_number(regexp_substr(column_value, '[^~]+', 1, 4)) desc
) 
into l_final_output 
from table(l_final_score)

Note: type must be global: 

create TYPE final_score_typ AS TABLE OF varchar2(1000);

Upvotes: 1

Related Questions