0

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')
Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I guess this answer suit me best. i have changed this to my pl sql block which is giving exact output. thanks a lot
@goldenbutter Hope you can handle the base result with PL/SQL code, so I've posted a clean source of data you need
Hi, can you tell me how i can search exact word-match in instr, for example, if my column name is ID i need to send that into block1, but in CLASSID column i also get ID and sending that block 2 as well.
@goldenbutter You can use regexp_instr('|' || cols || '|', '\|' || <yourcolname> || '\|') for separator "pipe" ('|') like in my answer
1

Conditional aggregation within a SQL statement might be used to determine for the desired block name as a local string variable :

SQL> SET SERVEROUTPUT ON 
SQL> DECLARE
  v_name        VARCHAR2(10);
  v_exporttable VARCHAR2(100);
BEGIN
  SELECT CASE WHEN SUM(CASE WHEN column_name IN ( 'CLASSID', 'OBJID' )
              THEN 1 
              ELSE 0 
          END) = 2 
              THEN 
                   'Block 2' 
              ELSE CASE WHEN SUM(CASE WHEN column_name IN ( 'KOPPKT', 'OBJID' )
                        THEN 1
                        ELSE 0
                         END) = 2
                        THEN 
                          'Block 1' 
                        ELSE 
                          'Block 3'
                    END         
               END 
    INTO v_name           
    FROM user_tab_cols
   WHERE table_name = v_exporttable;

  DBMS_OUTPUT.PUT_LINE('Returning Block Name is '||v_name);
END;
/

Demo

Update : Depending on your last comment which tells Table1 goes wrong side... it should go block 2 , you can rearrange the code as

DECLARE
  v_name        VARCHAR2(10);
  v_exporttable VARCHAR2(100);
BEGIN
  SELECT CASE WHEN SUM(CASE WHEN column_name = 'CLASSID'
              THEN 1 
              ELSE 0 
               END) > 0 
              THEN 
                   'Block 2' 
              ELSE CASE WHEN SUM(CASE WHEN column_name IN ( 'KOPPKT', 'OBJID' )
                        THEN 1
                        ELSE 0
                         END) = 2
                        THEN 
                          'Block 1' 
                        ELSE 
                          'Block 3'
                    END         
               END 
    INTO v_name             
    FROM user_tab_cols
   WHERE table_name = v_exporttable;

  DBMS_OUTPUT.PUT_LINE('Returning Block Name is '||v_name);
END;
/

Demo2

Improve this answer

3 Comments

Hi, thanks for this. i have tested. As per your example also Table1 should go block 2 like Table2 as both has classid but it goes block 3.
Hi @goldenbutter . But in none of the cases tables will incur Block3 then. Moreover, you told that if table has KOPPKT it will go block 3 , and there's no case mentioned whether any table has KOPPKT and CLASSID, which refers TABLE1, at the same time.
I have written this "Similarly if any table has KOPPKT and OBJID it should go block 1 not block 3" :-) anyway Table1 goes wrong side... it should go block 2

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.