Oracle Query modification

Question

ORACLE QUERY

CREATE OR REPLACE FORCE VIEW inventory (
        "ARTICLENO ",
        "EAN",
        "NAME",
        "BRAND"
      ) AS
        SELECT
            m.ARTICLENO,
            m.EAN,
            m.NAME,
            p.BRAN
        FROM
            ITEM   m,
            P_ITEM p
        WHERE
            m.ean = p.ean OR m.ARTICLE=p.ARTICLE;

(problem is in m.article=p.article i want only single record when comare not all)

Table = ITEM: as m

ARTICLENO	EAN	NAME
1	100	a
2	null	x

VIEW = P_ITEM as p

ARTICLENO	EAN	NAME	BRAND
1	100	a	b1
2	null	x	b2
2	null	x	b2
2	null	x	b2

VIEW = INV_ITEM as inv

ARTICLENO	EAN	NAME	BRAND
1	100	a	b1
2	null	x	b2

VIEW = INV_ITEM

DESIRE RESULT:

ARTICLENO	EAN	NAME	BRAND
1	100	a	b1
2	null	x	b2

BUT GETTING

ARTICLENO	EAN	NAME	BRAND
1	100	a	b1
2	null	x	b2
2	null	x	b2
2	null	x	b2

When inserting data in ITEM table I want if Ean is not null then compare by Ean like m.EAN = p.EAN and insert into inv_item

and if Ean is null then compare by article m.ARTICLE = p.ARTICLE but it should be distinct article.

Answer 1

There are some wrong column names (p.BRAN and p.ARTICLE), but it seems you just need to select DISTINCT records...

WITH
    item AS
        (
            Select 1 "ARTICLENO", 100 "EAN", 'a' "NAME" From Dual UNION ALL
            Select 2 "ARTICLENO", Null "EAN", 'x' "NAME" From Dual
        ),
    p_item AS
        (
            Select 1 "ARTICLENO", 100 "EAN", 'a' "NAME", 'b1' "BRAND" From Dual UNION ALL
            Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual UNION ALL
            Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual UNION ALL
            Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual 
        )

SELECT DISTINCT
    m.ARTICLENO,
    m.EAN,
    m.NAME,
    p.BRAND
FROM
    ITEM   m
INNER JOIN
    P_ITEM p ON(m.ean = p.ean OR m.ARTICLENO = p.ARTICLENO)