Home » SQL & PL/SQL » SQL & PL/SQL » Doubt on a query
Doubt on a query [message #226743] Mon, 26 March 2007 06:56 Go to next message
dr46014
Messages: 48
Registered: February 2007
Member
in my table there is are 2 fields field called client_id and effective_end date.i want to select records from the table where effective_end_date is NULL and client_id is NULL and i want also that client id should not contain 'AXONMOBIL'.For that i m writing the query like this but it is showing in correct results...

Select * from table where client_id is NOT NULL and Client_id not in ('AXONMOBIL') and effective_end_date is NULL

but it is showing incorrect results....
Can anyone please modify it
Re: Doubt on a query [message #226745 is a reply to message #226743] Mon, 26 March 2007 07:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
i want to select records from the table where effective_end_date is NULL and client_id is NULL


contradicts with

 i want also that client id should not contain 'AXONMOBIL' 


in sql this will equate to
select * from <table_a> where client_id is null and effective_end_Date is null and client_id <> 'AXONMOBIL'


This will never work.

Secondly the sql you have supplied

Select * from table where client_id is NOT NULL and Client_id not in ('AXONMOBIL') and effective_end_date is NULL


you don't need the "is not null condition" on client_id because "not in will ignore null values".

So now what is your question ?

P.S : Please from next time when you post any query or problem give us a create/insert script with the data and the expected output and a "brief but clear explanation" why the output is ..... This will be really helpful.

[Updated on: Mon, 26 March 2007 09:23]

Report message to a moderator

Re: Doubt on a query [message #226749 is a reply to message #226745] Mon, 26 March 2007 07:24 Go to previous messageGo to next message
dr46014
Messages: 48
Registered: February 2007
Member
i want to select records from the table where effective_end_date is NULL and client_id is NULL



this statement is rather false...client id is not null...
means i want to select the records where client id is not null and it also doesnot contain AXONMOBIL
Re: Doubt on a query [message #226751 is a reply to message #226749] Mon, 26 March 2007 07:49 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
nvl(client_id,'foo') not in ('foo','AXONMOBIL');
Re: Doubt on a query [message #226757 is a reply to message #226751] Mon, 26 March 2007 08:18 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
client_id <> 'Whatever'

Null values are not not-equal-to, so will be dropped.
Previous Topic: exception
Next Topic: BLOB column
Goto Forum:
  


Current Time: Fri Dec 09 17:11:54 CST 2016

Total time taken to generate the page: 0.15681 seconds