Need assistance with IF ELSE condition in Oracle

I have a list of tables, i need to pass those table based on column name. For example if my table has CLASSID it will go block 2, if table has OBJID it will go block 1, if table has KOPPKT it will go block 3, but if any table has CLASSID and OBJID both, it should go block 2 not block 1.
Similarly if any table has KOPPKT and OBJID it should go block 1 not block 3. Here i am facing issue to send tables into proper block.
The code i am trying to achieve but not getting the desired output.

desc TABLE1;
Name    Null     Type       
------- -------- ---------- 
CLASSID NOT NULL NUMBER(10) 
KOPPKT  NOT NULL NUMBER(10)
ID      NOT NULL NUMBER(10)

desc TABLE2;
Name    Null     Type       
------- -------- ---------- 
CLASSID NOT NULL NUMBER(10) 
OBJID   NOT NULL NUMBER(10) 

desc TABLE3;
Name    Null     Type       
------- -------- ---------- 
KOPPKT  NOT NULL NUMBER(10) 
OBJID   NOT NULL NUMBER(10)

Here Table1 and table2 should go block 2 as it has CLASSID, table3 should go block 1 as it has OBJID

set serveroutput on
declare
v_exporttable VARCHAR2(100):='TABLE1';
v_name varchar2(100);
begin
FOR rec IN
  (
  select  distinct column_name from all_tab_cols
  where table_name = v_exporttable
  ) loop
  if (rec.column_name ='OBJID') then
   DBMS_OUTPUT.PUT_LINE('Table has OBJID'); /*  block 1 */
  elsif (rec.column_name ='CLASSID') then
   DBMS_OUTPUT.PUT_LINE('Table has Classid'); /*  block 2 */
  elsif (rec.column_name = 'KOPPKT') then
   DBMS_OUTPUT.PUT_LINE('Table has KOPPKT'); /*  block 3 */
  end if;
end loop;
end;
/

Passing Table1 first output i got. which means it goes both block 2 and block 3.but i want to pass only block2

PL/SQL procedure successfully completed.

Table has Classid
Table has KOPPKT

Similarly if I pass Table 2 I am getting this. which means it goes both block 1 and block 2.but i want to pass only block2

PL/SQL procedure successfully completed.

Table has OBJID
Table has CLASSID

Similarly if I pass Table 3 I am getting this. which means it goes both block 1 and block 3.but i want to pass only block2

PL/SQL procedure successfully completed.

Table has OBJID
Table has KOPPKT

I have tried with below condition also still it not working

(rec.column_name ='OBJID' and rec.column_name <>'CLASSID')

Answer

Find all the columns of interest for your table with LISTAGG and then check for individual combinations in CASE statement. Put condition on CLASSID before condition on CLASSID and something:

with tab_cols as (
  select table_name,
    listagg(column_name, '|') within group(order by 1) as cols
  from all_tab_cols
  where table_name in (<your list>)
    and column_name in ('CLASSID', 'OBJID', 'KOPPKT')
  group by table_name
)
select table_name,
  case
    when instr(cols, 'CLASSID') > 0 then 2
    when instr(cols, 'OBJID') > 0 then 1
    when instr(cols, 'KOPPKT') > 0 then 3
  end as block_number
from tab_cols

Advertisement

Answer