Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

not null query returning null value

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
chalie001
Asked:
chalie001
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Does your obj_name column in cal_obj has null values  ?

check with this :

select * from cal_obj
          where obj_name is null ?
0
 
chalie001Author Commented:
i whated to say it return blank no value at all
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
do you mean that query returned 0 records ? do not confuse with words blank, no value, null etc
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
"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
 
chalie001Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now