Single record fetch when duplicate match-CodePudding

I have a master table

BARCODE	ARTICLE	Unit Of Measure	Ordering Unit Of Measure	Name
13000	100	PC	T12	ABC
13001	101	PC	T06	DEF
13001	101	PC	C08	XYZ

Another table items (in some cases we don't have barcode, so we have to deal with article and uom)

BARCODE	ARTICLE	UOM
null	100	PC
null	101	T06

In item table UOM(unit of measure) can be a Unit of measure or ordering unit of measure

Now I created a view based on item and master table but having problem while matching i want to match uom in item table with master if found okay otherwise it should check the ordering unit of measure if match but if we are putting OR operator it return multiple record entry in view

  CREATE OR REPLACE FORCE VIEW "MASTER_ITEM" ("NAME", "BARCODE", "ARTICLE", "UOM", "OUOM") AS 
  SELECT 

m.BARCODE,  
m.ARTICLE,
m.UOM,
p.NAME
FROM MASTER_ITEM m, MASTER p 
WHERE m.BARCODE = p.BARCODE  OR (m.ARTICLE = p.ARTICLE AND (p.UOM = m.UOM OR p.OUOM = m.UOM))
group BY m.BARCODE,m.ARTICLE, m.UOM,p.NAME;

EXPECTED OUTPUT

BARCODE	ARTICLE	Unit Of Measure	Name
null	100	PC	ABC
null	101	T12	DEF

Item table can have a UOM or OUOM based on this we should check in master table anyone of uom or ouom is match but not duplicate if both match

For article 101 in view, it is showing duplicate rows because view query checking the uom as well as ouom

Any way to match first if found don't match second if not match uom then check the ouom?

CodePudding user response：

You may "stack" different UOM columns of items table and assign priority to each them. Then aggregate according to priority using first aggregate function.

create table master_item
(barcode, article, uom, order_uom, name)
  as
select '13000', '100', 'PC', 'T12', 'ABC' from dual union all
select '13001', '101', 'PC', 'T06', 'DEF' from dual union all
select '13001', '101', 'PC', 'C08', 'XYZ' from dual

create table master_
(barcode, article, uom)
as
select cast(null as varchar2(10)), '100', 'PC' from dual union all
select null, '100', 'T12' from dual union all
select null, '101', 'T06' from dual

with mi as (
  select
    article,
    uom,
    name,
    1 as priority
  from master_item

  union all

  select
    article,
    order_uom,
    name,
    2 as priority
  from master_item
)
select distinct
  m.barcode,
  m.article,
  max(m.uom)
    keep(dense_rank first order by priority) as uom,
  max(mi.name) as name
from master_ m
  left join mi
  on m.article = mi.article
  and m.uom = mi.uom
group by m.barcode, m.article

BARCODE	ARTICLE	UOM	NAME
null	100	PC	ABC
null	101	T06	DEF

fiddle

CodePudding user response：

You are creating view named "MASTER_ITEM" using SELECT ... FROM MASTER_ITEM ... - this should end with SQL Error: ORA-01731: circular view definition encountered
If your sample data looks like in the question:

WITH        
    mstr AS
        (
            Select 13000 "BARCODE", 100 "ARTICLE", 'PC' "UOM", 'T12' "ORDERING_UOM", 'ABC' "A_NAME" From dual Union All
            Select 13001, 101, 'PC', 'T06', 'DEF' From dual Union All
            Select 13001, 101, 'PC', 'C08', 'XYZ' From dual
        ),
    items AS
        (
            Select CAST(Null as VARCHAR2(15)) "BARCODE", '100' "ARTICLE", 'PC' "UOM"  From dual Union All
            Select CAST(Null as VARCHAR2(15)), '101', 'T06' From dual
        )

... then your Select statement (with some small corrections) results exactly as expected. Your code should use JOIN sintax instead of the old one and all the problems could then be solved within ON clause of the JOIN. With sample data as above you don't need GROUP BY at all. Below is the code that is based essentialy on your code with just a few adjustments mentioned above:

SELECT      i.BARCODE,  i.ARTICLE, i.UOM, m.A_NAME
FROM        items i
INNER JOIN  mstr m ON (   i.BARCODE = m.BARCODE  OR
                         ( i.ARTICLE = m.ARTICLE AND (m.UOM = i.UOM OR m.ORDERING_UOM = i.UOM)  )   
                      )
--                    
--  R e s u l t
BARCODE         ARTICLE UOM A_NAME
--------------- ------- --- ------
                100     PC  ABC    
                101     T06 DEF

The problem of row multiplications could appear if your mstr table looks like here.

   BARCODE    ARTICLE UOM ORDERING_UOM A_NAME
---------- ---------- --- ------------ ------
     13000        100 PC  T12          ABC    
     13001        101 PC  T06          DEF    
     13001        101 PC  T06          XYZ

In this case article 101 from items table will be joined to both rows of mstr table because the link would be established both by UOM and ORDERING_UOM columns. Group by clause (or distinct) would not help as the names for this article(s) are different. This kind of data doesn't make sense aas it would say that you have same ARTICLE, BARCODE, UOM and ORDERING_UOM with a different A_NAME column. This could raise a question which A_NAME you want to be selected and why?