Oracle Query modification

User_CNU6G

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.

User_CNU6G

Frank Kulash

Hi, @User_CNU6G

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

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

Maybe you just need SELECT DISTINCT.

Frank Kulash

By the way

CREATE OR REPLACE FORCE VIEW inventory (
        "ARTICLENO ",

Avoid using names that require double-quotes; they're hard to use and cause lots of mistakes. A name like "ARTICLENO ", with a trailing space, is especially confusing.