Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: Return rows, even if tables is empty

    Hi All,

    I had run into the following problem.
    My task is to write a query, which returns with an empty (full of NULLs) row, if the condition is false.

    I'm thinking in outer joins.

    I use Oracle9i Enterprise Edition Release 9.2.0.1.0 .

    My test is here:
    --------------
    create table ATTILA_1 (m1 varchar2(10));
    insert into attila_1 values('a');
    insert into attila_1 values('a');
    insert into attila_1 values('b');

    SELECT
    b.*
    FROM
    dual left outer JOIN ATTILA_1 b
    ON (b.m1='c');

    -----------

    It returns correctly (with 2 or 1 rows) if the string in the condition is 'a' or 'b', but returns with no row (I'd like to see one row, with full of NULLs) if I call it with 'c'... :-(

    My oppinion is that it must be work fine... :-)

    Could you tell me, why is this? Or any idea to solve this task?

    Thanks in advance:
    Attila

  2. #2
    Join Date
    Nov 2003
    Posts
    7
    OK, I found the solution.

    SELECT
    b.*
    FROM
    dual left outer join ATTILA_1 b
    ON ((b.m1=dummy or 1=1 )and b.m1='c')


    I don't know, the other yolution why not works....

    Attila

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    For your information.

    You ran into a known bug in the Oracle version you are using. This is info I've got from Oracle support when I opened a TAR about this.

    <<<ORACLE SUPPORT>>>
    You encountered
    Bug:2829723 - Wrong results from ANSI OUTER JOIN when join condition does not
    reference both side

    Details:
    Wrong results are possible from queries using ANSI
    outer join syntax if there is no join condition referencing
    both sides of the join. (eg: a constant predicate)

    This BUG is fixed in 10i database version, and there is a patch on top of 9.2.0.3
    >>>END ORACLE SUPPORT<<<

    I've tested your script on version 9.2.0.4, and it definitely returns 1 row (with null value). Just wanted to share this with you.

  4. #4
    Join Date
    Nov 2003
    Posts
    7
    Thanks, now I'm relaxed, that I'm not completely stupid.. :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •