Same Null and Not null select doubt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Same Null and Not null select doubt

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-02 : 22:50:38

TblCallerInfo
callerId int
Has_data_Enable bit
For this the value will be like this

callerId Has_data_Enable
1 0
2 1
3 1
4 null
5 null

I will have three search criteria,
1. When Has_data_Enable true
2. When Has_data_Enable False
3. when Has_data_Enable NA.for the final condition I want select all the records from tbl…
I don't how to implement the final Condition.

i want to perform this one single query using Case statment or some conditional statment
guide me pls

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 22:55:22

1. where Has_data_Enable = 1
2. where Has_data_Enable = 0
3. where Has_data_Enable is NULL
by Has_data_Enable NA do you mean you want the rows 4 & 5 ?

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-02 : 22:56:49

i need all the five rows...for the 'NA' condition

quote:
Originally posted by khtan

1. where Has_data_Enable = 1
2. where Has_data_Enable = 0
3. where Has_data_Enable is NULL
by Has_data_Enable NA do you mean you want the rows 4 & 5 ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 22:58:22

then just don't have any where condition.

select * from TblCallerInfo

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-02 : 22:59:23

quote:
Originally posted by khtan

then just don't have any where condition.
select * from TblCallerInfo

KH

Yes u r right..but i want to perform all three condition in one single statment..There i get the problem

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 23:06:46

no problem. How do you intend to pass the variable in for the 3 scenario ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 23:11:00

try out the codes below


declare @TblCallerInfo table
(
	callerId 	int,
	Has_data_Enable bit
)
declare	@data	bit
	
insert into @TblCallerInfo
select 1, 0	union all
select 2, 1	union all
select 3, 1	union all
select 4, null	union all
select 5, null 

select	@data = null	-- To list all use NULL else set @data = 1 or @data = 0
select	*
from	@TblCallerInfo
where	@data is null
or	Has_data_Enable = @data

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-02 : 23:11:45

declare @IsData varchar(20)

set @isdata = 'Yes'
if (@IsData = 'Yes')
set @IsData = 1
if (@IsData = 'No')
set @IsData = 0
if (@IsData = 'NA')
set @IsData = Null

quote:
Originally posted by khtan

no problem. How do you intend to pass the variable in for the 3 scenario ?

KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 23:28:30

See my last post

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-05-02 : 23:29:46

Thanks A lot.it is working fine...

quote:

Originally posted by khtan

try out the codes below


declare @TblCallerInfo table
(
	callerId 	int,
	Has_data_Enable bit
)
declare	@data	bit
	
insert into @TblCallerInfo
select 1, 0	union all
select 2, 1	union all
select 3, 1	union all
select 4, null	union all
select 5, null 

select	@data = null	-- To list all use NULL else set @data = 1 or @data = 0
select	*
from	@TblCallerInfo
where	@data is null
or	Has_data_Enable = @data

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-03 : 02:15:10

Sarakumar, I already suggested you to know SQL by giving useful links. Read them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources