This discussion is archived
4 Replies Latest reply: Feb 3, 2013 8:38 PM by 886968 RSS

SQL doesn't work

886968 Newbie
Currently Being Moderated
Hi,

I have two SQL command, first command doesn't work (provide no record found) but the second one is working.
I don't know why.
Any body can help me to explain.

Command (1)
-----------
select *
from tb_po1
where
row_id_doc is not null and
row_id_doc not in(select row_id from v_doc_tb_po_req1_x);

Command (2)
-----------
select *
from tb_po1
where
row_id_doc is not null and
row_id_doc in(
select p.row_id_doc
from
tb_po1 p,
v_doc_tb_po_req1_x v
where
p.row_id_doc is not null and
p.row_id_doc = v.row_id (+) and
v.row_id is null
);

Thanks in advance!
Guntur
  • 1. Re: SQL doesn't work
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: SQL doesn't work
    937454 Newbie
    Currently Being Moderated
    There is nothing wrong with the first sql. It returns zero rows.
  • 3. Re: SQL doesn't work
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Gunthur,
    Guntur K wrote:
    Hi,

    I have two SQL command, first command doesn't work (provide no record found) but the second one is working.
    Chances are, they are both working. Sometimes "no rows found" is the right answer.
    I don't know why.
    Any body can help me to explain.
    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem.
    See the forum FAQ.
    Command (1)
    -----------
    select *
    from tb_po1
    where
    row_id_doc is not null and
    row_id_doc not in(select row_id from v_doc_tb_po_req1_x);
    One possible explanation is that v_doc_tb_po_req1_x.row_id is sometimes NULL. NOT IN won't be TRUE if the sub-query returns NULLs.
  • 4. Re: SQL doesn't work
    886968 Newbie
    Currently Being Moderated
    Yes, you are correct.
    Thank you very much.

    This works if my code as follows :

    select *
    from a_tb_po1
    where
    row_id_doc is not null and
    row_id_doc not in(select row_id from a_v_doc_tb_po_req1_x where row_id is not null);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points