CODEWITHSUNDEEP
sqloracle-databasejoin
Qasim0788
Qasim0788

Reputation: 41

How to compare two table values using PLSQL

I have to compare two tables values;

TABLE_A     TABLE_B 

ID  TYPE    ID  TYPE
12345       12345   3
67891       12345   7
36524       67891   3
            67891   2
            67891   5
            36524   3

Logic: I have to compare table_A id with Table_B id

if found 3&7 good else found 3 only avg else if found 7 only bad

These good, bad and avg should go back to table A type values.

could any one help me how to write this code in PLSQL.

Upvotes: 0

Views: 328

Answers (2)

San
San

Reputation: 4538

Assuming that you are considering type 3 and 7 only for your calculations, you can use following merge statement, no need of PL-SQL

merge into table_a a
using (select id, case (listagg(type, ',') within group (order by type))
                    when '3,7' then 'Good'
                    when '3'   then 'Avg'
                    when '7'   then 'Bad'
                    else null
                  end new_type
         from table_b
        where type in (3,7)
       group by id) b
   on (a.id = b.id)
 when matched then
   update set type = new_type;

For Oracle versions prior to 11 g release 2, use following:

merge into table_a a
using (select id, case (trim(both ',' from min(decode(type, 3, 3, null))||','||min(decode(type, 7, 7, null))))
                when '3,7' then 'Good'
                when '3'   then 'Avg'
                when '7'   then 'Bad'
                else null
              end new_type
     from table_b
    where type in (3,7)
   group by id) b
   on (a.id = b.id)
 when matched then
   update set type = new_type;

It has been assumed that there are unique combination of id an type in table_b.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I am interpreting what you mean as saying that you want to output 'good' when TableB contains both 3 and 7, 'avg' when it contains only 3, and so on. Here is a way to get this result:

select a.id,
       (case when sum(case when b.type = 3 then 1 else 0 end) > 1 and
                  sum(case when b.type = 7 then 1 else 0 end) > 0
             then 'good'
             when sum(case when b.type = 3 then 1 else 0 end) > 1
             then 'avg'
             when sum(case when b.type = 7 then 1 else 0 end)
             then 'bad'
        end) as logic
from tableA a left outer join
     tableB b
     on a.id = b.id
group by a.id;

Upvotes: 0

Related Questions