--As of 1/27/2019 it hasn't been answered
version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Hello All,
I have a scenario , to apply logic to insert data into a table/array in the following order, The CURSOR overflow_cursor below will give you an idea of what to insert into row 7-10 , if you get an overflow it goes into 7-10 , else use phone type 4 . Thanks
1)when phone type = 2 , the 1st in rank will be stored in row 1 , if nothing is found store (act,_id_i null, null)
2)when phone type = 2 ,t he 2nd in rank will be stored in row 2, if nothing is found store (act,_id_i null, null)
3) same for when phone type = 6 the 1st in rank will be stored in row 3 , if nothing is found store (act,_id_i null, null)
4) same for when phone type = 6 the 2nd in rank will be stored in row 4 , if nothing is found store (act,_id_i null, null)
5) same for when phone type =1 the 1st in rank will be stored in row 5 , if nothing is found store (act,_id_i null, null)
6)same for when phone type =1 the 2nd in rank will be stored in row 6, if nothing is found store (act,_id_i null, null)
7)same for when phone type =1 the 3rd in rank will be stored in row 7 , if nothing is found store (act,_id_i null, null)
--Here comes the tricky part
8) when phone type = 2 , the 3rd in rank will be stored in row 8 , if nothing is found store (when phone type = 4 , the 1st in rank will be stored in row 8) , else store the null filler values
9) when phone type = 2 or overflown to 6 , the 4th in rank for phone type 2 or 3rd in rank for phonetype = 6 will be stored in row 9 , if nothing is found store (when phone type = 4 , the 1st in rank will be stored in row 9) , else store the null filler values.
10 )when phone type = 2/6/1, the 3rd in rank will be stored in row 10 , if nothing is found store then (when phone type = 4 , the 1st in rank will be stored in row 10) , else store the null filler values
CREATE TABLE Phone_number (
Account_id NUMBER (9) ,
line_number NUMBER (9) ,
phone_type NUMBER (9)) ;
---------------------------------------------------------------
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,373328502,6);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,81662056,6);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,132988269,6);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,384357868,4);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,896022318,4);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,511235417,4);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,686112444,4);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,50404003,2);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,35274449,2);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,753008213,2);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,428712611,2);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,593723617,2);
Insert into PHONE_NUMBER (ACCOUNT_ID,LINE_NUMBER,PHONE_TYPE) values (102,593523617,1);
--------------------------------------------------
--PLSQL logic
DECLARE
CURSOR main_cursor is SELECT ROWNUM, d.*
FROM (SELECT account_id,
line_number,
phone_type,
DENSE_RANK ()
OVER (PARTITION BY phone_type ORDER BY line_number DESC)
AS phone_rank
FROM Phone_number
WHERE account_id = 102) d ;
--this query will get over flow
CURSOR overflow_cursor is
SELECT ROWNUM, f.*
FROM (SELECT account_id,
line_number,
phone_type,
DENSE_RANK ()
OVER (PARTITION BY phone_type ORDER BY line_number DESC)
AS phone_rank
FROM Phone_number
WHERE account_id = 102) f
WHERE (phone_type = 1 AND phone_rank > 2)
OR (phone_type = 2 AND phone_rank > 2)
OR (phone_type = 3 AND phone_rank > 3) AND ROWNUM <= 3;
BEGIN
delete from phone_number_gtt;
FOR rec in main_cursor
--First_row
loop
--snippet of code in progress
if (rec.phone_type = 2 and rec.phone_rank = 1 )
then
insert into phone_number_gtt Values(rec.account_id , rec.line_number, rec.phone_type);
ELSE
insert into phone_number_gtt Values(123 , null, null);
end if;
if (rec.phone_type = 2 and rec.phone_rank = 2 )
then
insert into phone_number_gtt Values(rec.account_id , rec.line_number, rec.phone_type);
ELSE
insert into phone_number_gtt Values(123 , null, null);
end if;
--What you have in cursor
ROWNUM ACCOUNT_ID LINE_NUMBER PHONE_TYPE
---------- ---------- ----------- ----------
1 104 777341292 2
2 104 530196018 6
3 104 628956064 6
4 104 435935414 6
DESIRED OUTPUT
--Expected final result (in a GTT or in a collection)
ROWNUM ACCOUNT_ID LINE_NUMBER PHONE_TYPE RANK
---------- ---------- ----------- ---------- ----------
1 104 777341292 2 1
2 104 null null
3 104 530196018 6 1
4 104 628956064 6 2
5 104 null null
6 104 null null
7 104 null null
8 104 435935414 6 3
9 104 null null
10 104 null null