Get Account details in Oracle using function

Question

we have two tables account and Dept where acocunt level 1 will be coming from acount table and level 2 and level 3 account level coming from dept. table

Now based on the input we need to find the accountkey which is in both tables and it should display which accountkey based on source account id, db id and account level.

Input: [112].[22].[1],[113].[23].[1],[245].[21].[2],[289].[20].[2],[301].[21].[3], [304].[20].[3]

Description:

112 - Account id(level1 accountid or leve2 accountid or level3 accountid),
22 - Database id,
1 or 2 or 3 -account level

Eg:

Account table :

DIM_CUST_KEY level1 account id databaseid 1123 112 22 1234 113 23

Dept table:

DIM_CUST_KEY level2 account id level3 account id databaseid 1587 245 301 21 1576 289 304 20

What i have tried:

`create or replace function get_accountdetails (par_input in varchar2) return 
varchar2 is
v_ret varchar2(20) := '';
begin
select dim_cust_key from dim_cust_acnt a
inner join dim_cust_dept d using (dim_cust_key)

where ( 1 = regexp_substr(par_input, '\d+', 1, 3)  
       and regexp_substr(par_input, '\d+', 1, 1) = level1_account_id 
       and regexp_substr(par_input, '\d+', 1, 2) = a.database_id )
or    ( 2 = regexp_substr(par_input, '\d+', 1, 3)  
      and regexp_substr(par_input, '\d+', 1, 1) = level2_account_id 
      and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
or    ( 3 = regexp_substr(par_input, '\d+', 1, 3)  
      and regexp_substr(par_input, '\d+', 1, 1) = level3_account_id 
      and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
  return v_ret;
end;`

Answer 1

You seems to be a little chaotic in problem description, please format your question and make it more readable. Anyway the solutions is simple. You only need one if statement, where, depending on level, you search in first or second table and proper column:

create or replace function get_accountdetails (par_input in varchar2) return varchar2 is
  v_aid varchar2(10);
  v_db  varchar2(10);
  v_lvl varchar2(10);
  v_ret varchar2(20) := '';
begin
  v_aid := regexp_substr(par_input, '\d+', 1, 1);
  v_db  := regexp_substr(par_input, '\d+', 1, 2);
  v_lvl := regexp_substr(par_input, '\d+', 1, 3);

  if v_lvl = 1 then
     select dim_cust_key
       into v_ret
       from dim_cust_acnt
       where level1_account_id = v_aid and database_id = v_db;
  elsif v_lvl = 2 then
     select dim_cust_key
       into v_ret
       from dim_cust_dept
       where level2_account_id = v_aid and database_id = v_db;
  else
     select dim_cust_key
       into v_ret
       from dim_cust_dept
       where level3_account_id = v_aid and database_id = v_db;
  end if;
  return v_ret;
end;

Here are tables and sample function calls:

create table dim_cust_acnt (dim_cust_key, level1_account_id, database_id) as (
    select 1123, 112, 22 from dual union all
    select 1234, 113, 23 from dual );

create table dim_cust_dept (dim_cust_key, level2_account_id, level3_account_id, database_id) as (
    select 1587, 245, 301, 21 from dual union all
    select 1576, 289, 304, 20 from dual);

select get_accountdetails('[112].[22].[1]') from dual;     -- result: 1123
select get_accountdetails('[289].[20].[2]') from dual;     -- result: 1576
select get_accountdetails('[301].[21].[3]') from dual;     -- result: 1587

Please use proper columns names which you have in your real data, and adjust variable types and length if needed. I think you could also use one joined query, no special function needed, something like below. I used full join, because your examples do not contain matching rows. Probably simple join will be enough.

with t(par_input) as (select '[112].[22].[1]' from dual)
select dim_cust_key
  from dim_cust_acnt a
  full join dim_cust_dept d using (dim_cust_key) 
  cross join t
  where ( 1 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level1_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = a.database_id )
     or ( 2 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level2_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )
     or ( 3 = regexp_substr(par_input, '\d+', 1, 3)  
          and regexp_substr(par_input, '\d+', 1, 1) = level3_account_id 
          and regexp_substr(par_input, '\d+', 1, 2) = d.database_id )

Result:

DIM_CUST_KEY
------------
        1123

If you remove with and cross join parts and add into clause then you can use this query in function instead of if statement.

---

Edit:

Sorry for delay, I did not look at Stack Overflow lately. Here are two examples how to write your functions:

This function returns concatenated string:

select get_details_1('[112].[22].[1],[289].[20].[2],[301].[21].[3]') as list from dual;

LIST
------------------
1123,1576,1587

And second function is pipelined and returns data as predefined collecton of strings, so values are in separate rows.

select column_value 
  from table(get_details_2('[112].[22].[1],[289].[20].[2],[301].[21].[3]'));

COLUMN_VALUE
------------
        1123
        1576
        1587

You can also parse all input data at first, store them in some collection and then use bulk collect in one query. There is many solutions and possibilities, personally I would use pipelined function, but it depeneds on what form of output you need (collection or concatenated string). Also you can add begin ... end block and handle exception when no_data_found. You can present special info then or break execution, it depends on what behavior is expected in such situation.

Function 1:

create or replace function get_details_1 (par_input in varchar2) return varchar2 is
    v_aid varchar2(10);
    v_db  varchar2(10);
    v_lvl varchar2(10);
    v_ret varchar2(20);
    v_all varchar2(200) := '';

    i_cnt int := 0;
begin
    loop
        v_aid := regexp_substr(par_input, '\d+', 1, i_cnt + 1);
        v_db  := regexp_substr(par_input, '\d+', 1, i_cnt + 2);
        v_lvl := regexp_substr(par_input, '\d+', 1, i_cnt + 3);
        i_cnt := i_cnt + 3;
    exit when v_aid is null;
        select dim_cust_key
          into v_ret
          from dim_cust_acnt a
          full join dim_cust_dept d using (dim_cust_key)
          where (v_lvl = 1 and level1_account_id = v_aid and a.database_id = v_db)
             or (v_lvl = 2 and level2_account_id = v_aid and d.database_id = v_db)
             or (v_lvl = 3 and level3_account_id = v_aid and d.database_id = v_db);
       v_all := v_all||','||v_ret;
  end loop;
  return ltrim(v_all, ',');
end;

Function 2:

create or replace function get_details_2 (par_input in varchar2) 
    return sys.odcinumberlist pipelined is

    v_aid varchar2(10);
    v_db  varchar2(10);
    v_lvl varchar2(10);
    v_ret varchar2(20);
    i_cnt int := 0;
begin
    loop
        v_aid := regexp_substr(par_input, '\d+', 1, i_cnt + 1);
        v_db  := regexp_substr(par_input, '\d+', 1, i_cnt + 2);
        v_lvl := regexp_substr(par_input, '\d+', 1, i_cnt + 3);
        i_cnt := i_cnt + 3;
    exit when v_aid is null;
        select dim_cust_key
          into v_ret
          from dim_cust_acnt a
          full join dim_cust_dept d using (dim_cust_key)
          where (v_lvl = 1 and level1_account_id = v_aid and a.database_id = v_db)
             or (v_lvl = 2 and level2_account_id = v_aid and d.database_id = v_db)
             or (v_lvl = 3 and level3_account_id = v_aid and d.database_id = v_db);
       pipe row (v_ret);
  end loop;
  return;
end;