Outer join problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jain-neeraj@lycos.com
    Guest replied
    Re: Outer join problem

    Thanks a lot, Jon and Romeo. Your explainations cleared the fog and I
    was finally able to see the missing link (CHARGE.CODE = CODE.CODE(+)).

    rolympia@hotmai l.com (Romeo Olympia) wrote in message news:<42fc55dc. 0406182242.23de 9a2e@posting.go ogle.com>...
    What exactly do you want done? We could make your output come out but
    it doesn't necessarily mean that we're meeting your logical
    requirements. Some more explanation would be helpful.
    >
    Also, if you are in 9i at least, consider using ANSI OUTER JOIN
    syntax. Some restrictions using the (+) operator may be cirvumvented
    by this.
    >
    But to get you started. Written in the traditional Oracle outer join
    syntax. Again (not sure if this meets your "business" need).
    >
    SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
    CHARGE.CODE, CHARGE.CD_TYP, CHARGE.RECEIVER
    FROM PAYER,
    (select CHARGE.*, CD_TYP
    from CHARGE, OUTCHARGE, CODE
    where OUTCHARGE.PERIO D=CHARGE.PERIOD
    AND OUTCHARGE.CUST= CHARGE.CUST
    AND OUTCHARGE.SEQ1= CHARGE.SEQ1
    AND OUTCHARGE.SEQ2= CHARGE.SEQ2
    and CHARGE.CODE = CODE.CODE(+)) CHARGE,
    (select * from CONTROL1
    union select * from CONTROL2) TMP_CONTROL
    WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
    AND CHARGE.PERIOD(+ )=PAYER.PERIOD
    AND CHARGE.CUST(+)= PAYER.CUST
    AND CHARGE.BA(+)=PA YER.BA
    AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O
    >
    PERIOD CUST BA CTRL_NO CODE CD_TYP
    RECEIVER
    ---------- ---------- ---------- ---------- ---------- ----------
    ----------
    1 1 1 10 20 21
    99
    1 1 2 10 20 21
    90
    1 1 3 10
    >
    >
    >
    >
    jain-neeraj@lycos.co m wrote in message news:<30b5aacb. 0406180835.55c8 8998@posting.go ogle.com>...
    Hi,

    We have a problem in our mobile calls billing software. To solve it, I
    need an outer join in a complicated query. Following are the
    simplified tables with sample data:

    create table CONTROL1 (CTRL_NO number(2));
    insert into CONTROL1 values (10);

    create table CONTROL2 (CTRL_NO number(2));
    -- does not have any data

    create table CODE (CODE number(2), CD_TYP number(2));
    insert into CODE values (20, 21);

    create table PAYER (PERIOD number(2), CUST number(2), BA number(2),
    CTRL_NO number(2));
    insert into PAYER values (1,1,1,10);
    insert into PAYER values (1,1,2,10);
    insert into PAYER values (1,1,3,10);

    create table CHARGE (PERIOD number(2), CUST number(2), BA number(2),
    CTRL_NO number(2), SEQ1 number(2), SEQ2 number(2), CODE number(2),
    RECEIVER number(2));
    insert into CHARGE values (1,1,1,10,4,5,2 0,99);
    insert into CHARGE values (1,1,2,10,4,6,2 0,90);

    create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
    number(2), SEQ2 number(2));
    insert into OUTCHARGE values (1,1,4,5);
    insert into OUTCHARGE values (1,1,4,6);

    QUERY is:

    SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
    CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
    FROM PAYER, CHARGE, OUTCHARGE, CODE,
    (select * from CONTROL1
    union select * from CONTROL2) TMP_CONTROL
    WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
    AND CHARGE.PERIOD(+ )=PAYER.PERIOD
    AND CHARGE.CUST(+)= PAYER.CUST
    AND CHARGE.BA(+)=PA YER.BA
    AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O
    AND CHARGE.CODE=COD E.CODE
    AND OUTCHARGE.PERIO D=CHARGE.PERIOD
    AND OUTCHARGE.CUST= CHARGE.CUST
    AND OUTCHARGE.SEQ1= CHARGE.SEQ1
    AND OUTCHARGE.SEQ2= CHARGE.SEQ2;

    Output should be (3 rows):
    (1,1,1,10,20,21 ,99)
    (1,1,2,10,20,21 ,90)
    (1,1,3,10,null, null,null)

    When I use the above query, I get only the first 2 rows, not the
    third. What mistake am I doing?

    Thanks in advance.

      Leave a comment:

    • Romeo Olympia
      Guest replied
      Re: Outer join problem

      What exactly do you want done? We could make your output come out but
      it doesn't necessarily mean that we're meeting your logical
      requirements. Some more explanation would be helpful.

      Also, if you are in 9i at least, consider using ANSI OUTER JOIN
      syntax. Some restrictions using the (+) operator may be cirvumvented
      by this.

      But to get you started. Written in the traditional Oracle outer join
      syntax. Again (not sure if this meets your "business" need).

      SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
      CHARGE.CODE, CHARGE.CD_TYP, CHARGE.RECEIVER
      FROM PAYER,
      (select CHARGE.*, CD_TYP
      from CHARGE, OUTCHARGE, CODE
      where OUTCHARGE.PERIO D=CHARGE.PERIOD
      AND OUTCHARGE.CUST= CHARGE.CUST
      AND OUTCHARGE.SEQ1= CHARGE.SEQ1
      AND OUTCHARGE.SEQ2= CHARGE.SEQ2
      and CHARGE.CODE = CODE.CODE(+)) CHARGE,
      (select * from CONTROL1
      union select * from CONTROL2) TMP_CONTROL
      WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
      AND CHARGE.PERIOD(+ )=PAYER.PERIOD
      AND CHARGE.CUST(+)= PAYER.CUST
      AND CHARGE.BA(+)=PA YER.BA
      AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O

      PERIOD CUST BA CTRL_NO CODE CD_TYP
      RECEIVER
      ---------- ---------- ---------- ---------- ---------- ----------
      ----------
      1 1 1 10 20 21
      99
      1 1 2 10 20 21
      90
      1 1 3 10




      jain-neeraj@lycos.co m wrote in message news:<30b5aacb. 0406180835.55c8 8998@posting.go ogle.com>...
      Hi,
      >
      We have a problem in our mobile calls billing software. To solve it, I
      need an outer join in a complicated query. Following are the
      simplified tables with sample data:
      >
      create table CONTROL1 (CTRL_NO number(2));
      insert into CONTROL1 values (10);
      >
      create table CONTROL2 (CTRL_NO number(2));
      -- does not have any data
      >
      create table CODE (CODE number(2), CD_TYP number(2));
      insert into CODE values (20, 21);
      >
      create table PAYER (PERIOD number(2), CUST number(2), BA number(2),
      CTRL_NO number(2));
      insert into PAYER values (1,1,1,10);
      insert into PAYER values (1,1,2,10);
      insert into PAYER values (1,1,3,10);
      >
      create table CHARGE (PERIOD number(2), CUST number(2), BA number(2),
      CTRL_NO number(2), SEQ1 number(2), SEQ2 number(2), CODE number(2),
      RECEIVER number(2));
      insert into CHARGE values (1,1,1,10,4,5,2 0,99);
      insert into CHARGE values (1,1,2,10,4,6,2 0,90);
      >
      create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
      number(2), SEQ2 number(2));
      insert into OUTCHARGE values (1,1,4,5);
      insert into OUTCHARGE values (1,1,4,6);
      >
      QUERY is:
      >
      SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
      CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
      FROM PAYER, CHARGE, OUTCHARGE, CODE,
      (select * from CONTROL1
      union select * from CONTROL2) TMP_CONTROL
      WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
      AND CHARGE.PERIOD(+ )=PAYER.PERIOD
      AND CHARGE.CUST(+)= PAYER.CUST
      AND CHARGE.BA(+)=PA YER.BA
      AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O
      AND CHARGE.CODE=COD E.CODE
      AND OUTCHARGE.PERIO D=CHARGE.PERIOD
      AND OUTCHARGE.CUST= CHARGE.CUST
      AND OUTCHARGE.SEQ1= CHARGE.SEQ1
      AND OUTCHARGE.SEQ2= CHARGE.SEQ2;
      >
      Output should be (3 rows):
      (1,1,1,10,20,21 ,99)
      (1,1,2,10,20,21 ,90)
      (1,1,3,10,null, null,null)
      >
      When I use the above query, I get only the first 2 rows, not the
      third. What mistake am I doing?
      >
      Thanks in advance.

        Leave a comment:

      • Jon Armstrong
        Guest replied
        Re: Outer join problem

        Neeraj,

        It would be interesting to have a more complete functional description of
        these tables / fields and the functional intent of the query.

        Having said that, you'll never get the 3rd row in the result with your query
        since that would require CHARGE.CODE to be null and also
        CHARGE.CODE=COD E.CODE to be true. Since that expression is never true,
        that's the basic reason for the behavior you are seeing.

        You'll similarly have the same problem with comparisons of the other columns
        of table "charge".

        Try this (just as a test) or use the non-standard (+) syntax if you wish:

        SELECT payer.period, payer.cust, payer.ba, charge.ba,
        payer.ctrl_no, charge.code, code.cd_typ, charge.receiver
        FROM payer LEFT JOIN charge
        ON charge.period = payer.period
        AND charge.cust = payer.cust
        AND charge.ba = payer.ba
        AND charge.ctrl_no = payer.ctrl_no,
        outcharge,
        code,
        (SELECT *
        FROM control1
        UNION
        SELECT *
        FROM control2) tmp_control;

        Regards... Jon

        Jon Armstrong



        <jain-neeraj@lycos.co mwrote in message
        news:30b5aacb.0 406180835.55c88 998@posting.goo gle.com...
        Hi,
        >
        We have a problem in our mobile calls billing software. To solve it, I
        need an outer join in a complicated query. Following are the
        simplified tables with sample data:
        >
        create table CONTROL1 (CTRL_NO number(2));
        insert into CONTROL1 values (10);
        >
        create table CONTROL2 (CTRL_NO number(2));
        -- does not have any data
        >
        create table CODE (CODE number(2), CD_TYP number(2));
        insert into CODE values (20, 21);
        >
        create table PAYER (PERIOD number(2), CUST number(2), BA number(2),
        CTRL_NO number(2));
        insert into PAYER values (1,1,1,10);
        insert into PAYER values (1,1,2,10);
        insert into PAYER values (1,1,3,10);
        >
        create table CHARGE (PERIOD number(2), CUST number(2), BA number(2),
        CTRL_NO number(2), SEQ1 number(2), SEQ2 number(2), CODE number(2),
        RECEIVER number(2));
        insert into CHARGE values (1,1,1,10,4,5,2 0,99);
        insert into CHARGE values (1,1,2,10,4,6,2 0,90);
        >
        create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
        number(2), SEQ2 number(2));
        insert into OUTCHARGE values (1,1,4,5);
        insert into OUTCHARGE values (1,1,4,6);
        >
        QUERY is:
        >
        SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
        CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
        FROM PAYER, CHARGE, OUTCHARGE, CODE,
        (select * from CONTROL1
        union select * from CONTROL2) TMP_CONTROL
        WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
        AND CHARGE.PERIOD(+ )=PAYER.PERIOD
        AND CHARGE.CUST(+)= PAYER.CUST
        AND CHARGE.BA(+)=PA YER.BA
        AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O
        AND CHARGE.CODE=COD E.CODE
        AND OUTCHARGE.PERIO D=CHARGE.PERIOD
        AND OUTCHARGE.CUST= CHARGE.CUST
        AND OUTCHARGE.SEQ1= CHARGE.SEQ1
        AND OUTCHARGE.SEQ2= CHARGE.SEQ2;
        >
        Output should be (3 rows):
        (1,1,1,10,20,21 ,99)
        (1,1,2,10,20,21 ,90)
        (1,1,3,10,null, null,null)
        >
        When I use the above query, I get only the first 2 rows, not the
        third. What mistake am I doing?
        >
        Thanks in advance.



        -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
        http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
        -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

          Leave a comment:

        • jain-neeraj@lycos.com
          Guest started a topic Outer join problem

          Outer join problem

          Hi,

          We have a problem in our mobile calls billing software. To solve it, I
          need an outer join in a complicated query. Following are the
          simplified tables with sample data:

          create table CONTROL1 (CTRL_NO number(2));
          insert into CONTROL1 values (10);

          create table CONTROL2 (CTRL_NO number(2));
          -- does not have any data

          create table CODE (CODE number(2), CD_TYP number(2));
          insert into CODE values (20, 21);

          create table PAYER (PERIOD number(2), CUST number(2), BA number(2),
          CTRL_NO number(2));
          insert into PAYER values (1,1,1,10);
          insert into PAYER values (1,1,2,10);
          insert into PAYER values (1,1,3,10);

          create table CHARGE (PERIOD number(2), CUST number(2), BA number(2),
          CTRL_NO number(2), SEQ1 number(2), SEQ2 number(2), CODE number(2),
          RECEIVER number(2));
          insert into CHARGE values (1,1,1,10,4,5,2 0,99);
          insert into CHARGE values (1,1,2,10,4,6,2 0,90);

          create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
          number(2), SEQ2 number(2));
          insert into OUTCHARGE values (1,1,4,5);
          insert into OUTCHARGE values (1,1,4,6);

          QUERY is:

          SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
          CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
          FROM PAYER, CHARGE, OUTCHARGE, CODE,
          (select * from CONTROL1
          union select * from CONTROL2) TMP_CONTROL
          WHERE PAYER.CTRL_NO=T MP_CONTROL.CTRL _NO
          AND CHARGE.PERIOD(+ )=PAYER.PERIOD
          AND CHARGE.CUST(+)= PAYER.CUST
          AND CHARGE.BA(+)=PA YER.BA
          AND CHARGE.CTRL_NO( +)=PAYER.CTRL_N O
          AND CHARGE.CODE=COD E.CODE
          AND OUTCHARGE.PERIO D=CHARGE.PERIOD
          AND OUTCHARGE.CUST= CHARGE.CUST
          AND OUTCHARGE.SEQ1= CHARGE.SEQ1
          AND OUTCHARGE.SEQ2= CHARGE.SEQ2;

          Output should be (3 rows):
          (1,1,1,10,20,21 ,99)
          (1,1,2,10,20,21 ,90)
          (1,1,3,10,null, null,null)

          When I use the above query, I get only the first 2 rows, not the
          third. What mistake am I doing?

          Thanks in advance.
          Tags: None
          Previous template Next
          Working...