CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Dillon Wright
Dillon Wright

Reputation: 151

PL/SQL script sub query in variable array

hey guys what im trying to do is pull a list of account ids that are related to a primary account id without joining the table as its gigantic then use that list to pull out the relevant information from the data table i get an error saying it expecting something other then = on line 3

the data table does not contain the primary account id the account_lookup table has account _id and primary account id in the same row IE 

row ID | primary_account_id | account_id
 1     |          1         |     1
 2     |          1         |     2


declare
   acc_id number(10,0) := 5500704;
   p_acc_id number(10,0) := (select Primary_account_id from lookup_tbl where account_id = acc_id); 
   type array_t is table of number(10,0);
   array array_t := (select account_id from lookup_tbl where primary_account_id = p_acc_id);  

end;

select account_id, profit
from data_tbl where account_id in array_t

Upvotes: 1

Views: 1189

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Hey i have modified the code snippet to elimintae the error you are facing. Please try and let me know for any issues.

DROP TYPE p_acc_tab;


-- Creating permament SQL object to eliminate the error occured
CREATE OR REPLACE TYPE 
p_acc_tab 
IS TABLE OF NUMBER; 


DECLARE          
acc_id NUMBER := 5500704;          
p_acc_id p_acc_tab;        
type array_tab        
IS          
TABLE OF NUMBER;          
array_t array_tab;        
BEGIN          
-- First bulk collect as you don want to use join as gigantic data present          
SELECT Primary_account_id           
BULK COLLECT          
INTO p_acc_id          
FROM lookup_tbl          
WHERE account_id = acc_id;                    

--Second bulk collect with EXISTS condition to check for the account_id for respective primary account_id          
SELECT account_id           
BULK COLLECT          
INTO array_t          
FROM lookup_tbl t_tab          
WHERE EXISTS            
(SELECT 1            
FROM TABLE(p_acc_id) tab            
WHERE tab.column_value = t_tab.Primary_account_id            
);        
END;

Upvotes: 1

Shaun Peterson
Shaun Peterson

Reputation: 1790

Untested but try something like the below.

declare
   acc_id number(10,0) := 5500704;
   p_acc_id number(10,0); 

   type array_t is  table of number(10,0);
   my_array array_t;  

   cursor c_blah is
   select account_id, profit
    from data_tbl where account_id in array_t;

begin

  select primary_account_id 
  into p_acc_id
  from lookup_tbl 
  where account_id = acc_id;  

  select account_id 
  bulk collect into my_array
  from lookup_tbl 
  where primary_account_id = p_acc_id;

  for v_row in c_blah loop

     dbms_output.put_line(' account_id = ' || v_row.account_id || ' : ' || v_row.profit);      

  end loop;


end;

Upvotes: 0

Related Questions