Forum Stats

  • 3,825,167 Users
  • 2,260,475 Discussions
  • 7,896,430 Comments

Discussions

Oracle Query modification

User_CNU6G
User_CNU6G Member Posts: 2 Green Ribbon

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.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,032 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 42,032 Red Diamond

    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.