Need to compare value in source table with standard values in spec table using MINUS concept of PL/SQL

Question

Team I have

Source table -->SRC_TBL as

TaxCode,ExemCode,CountryCode
TX1,EX1,US
TX1,EX2,UK
TX3,EX1,US
TX2,EX2,UK

3 Spec tables as ---> SPEC_TBL_TX

TaxCode
TX1
TX2
TX4

SPEC_TBL_EX

ExmCode
EX1
EX3
EX4

SPEC_TBL_CTY

Contry
US
IN
SG

Need to compare each source table values against values in each spec table values and update target table as follows

TAR_TBL

TaxCode,ExemCode,CountryCode,ERROR
TX1,EX1,US,VALID
TX1,EX2,UK,INVALID EX code+ Invalid Country
TX3,EX1,US,INVALID TX Code
TX2,EX2,UK,INVALID EX code+ Invalid Country

Need to implement with pl/sql MINUS concept. Will it require any BULK COLLECT logic too for bulk collections?

Answer 1

You can left join your source table with the others and use case-when to determine when the pair was null, i.e., has no example in the corresponding target spec table:

select SRC_TBL.TaxCode, SRC_TBL.ExemCode, SRC_TBL.CountryCode, 
case 
    when SPEC_TBL_TX.TaxCode is null then 'error1'
    else ''
end ||
case
   when SPEC_TBL_EX.ExemCode is null then 
   case when SPEC_TBL_TX.TaxCode is null then '+'
        else ''
   end
   ||
   'error2'
   else ''
end ||
case 
    when SPEC_TBL_CTY.CountryCode is null then
    case when SPEC_TBL_TX.TaxCode is null or SPEC_TBL_EX.ExemCode is null then '+'
         else ''
    end
    ||
    ''
    else ''
end
as ERROR
from SRC_TBL
left join SPEC_TBL_TX
on SRC_TBL.TaxCode = SPEC_TBL_TX.TaxCode
left join SPEC_TBL_EX
on SRC_TBL.ExemCode = SPEC_TBL_EX.ExemCode
left join SPEC_TBL_CTY
on SRC_TBL.CountryCode = SPEC_TBL_CTY.CountryCode

If you need to insert this into a table, then you can insert-select, like

insert target_table(TaxCode, ExemCode, CountryCode, ERROR)
select ...
from ...

The ... above represent that whatever your query is, you can use it to specify what to insert into the target table. If you need to update, then you can update join, see https://oracle-base.com/articles/23c/direct-joins-for-update-and-delete-statements

So I recommend to update join first to update the error of whatever records that already exist and then do an insert-select, but in the insert-select make sure that you left join with the target table by the three identifier columns (TaxCode, ExemCode, CountryCode) you have matching the source table and check in the where clause that the target table's records are null, so you do not have a match yet in the target table, so you need to insert.

You could also do upserts, see How to UPSERT (update or insert into a table?)