Solved

not null query returning null value

Posted on 2015-01-28
7
149 Views
Last Modified: 2015-01-29
hi i have the folloing query but returning null value even if there is no value related to other table

SELECT * FROM CAL_OBJ
          WHERE TRIM(OBJ_NAME) NOT IN(SELECT TRIM(OBJ_NAME) FROM cal_book);this query return null
        
 
          select * from cal_book
          where obj_name = 'LHLODS06.FMB';this query return null
          
           select * from cal_obj
          where obj_name = 'LHLODS06.FMB';  this query return value

Open in new window

THE second query suppose to be the one to return value not the last query, is there another way to write the first query
0
Comment
Question by:chalie001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40576803
In case of the second query, what do you mean it returns NULL? You query whole records. If there is no cal_book with the obj_name 'LHLODS06.FMB', you get no records, an empty result set, but not NULL.

If you get an empty result set a NOT IN subquery can't check a value against a set of other values and therefore this also results in an empty result set.

You should rather use NOT EXISTS.

Bye, Olaf.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40576823
Does your obj_name column in cal_obj has null values  ?

check with this :

select * from cal_obj
          where obj_name is null ?
0
 

Author Comment

by:chalie001
ID: 40576903
i whated to say it return blank no value at all
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40576912
do you mean that query returned 0 records ? do not confuse with words blank, no value, null etc
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 40576921
you should use this query to get all form names which exist in the cal_obj table but not in the cal_book table.

  select * from cal_obj a
          where not exists ( select 1 from cal_book b where a.obj_name = b.obj_name );

Test it out.

Thanks,
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40578068
"I wanted to say it return blank no value at all"  That is not the same as returning a null.

Try this example:

create table test_tab
(item_code    varchar2(3),
 price        number(6,2),
 date_shipped date);
 
insert into test_tab
values ('ABC',15, trunc(sysdate - 1));

insert into test_tab
values ('DEF',25, trunc(sysdate));
 
insert into test_tab
values ('GHI',30, null);

commit;

If you run either of these queries:
select * from  test_tab;
or
select * from  test_tab where item_code = 'GHI';

You *WILL* get  a null value returned for column: "date_shipped" in the record for 'GHI'.

If you run this query:
select * from  test_tab where item_code = 'XYZ';

You will get no rows returned.  That is different from "returning a null".
0
 

Author Closing Comment

by:chalie001
ID: 40578140
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question