isnull - SQL Server Forums

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

Transact-SQL (2000)

isnull

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 04:46:32

can someone tell me if there is an equivalent to this statement

Table_1 = isNull (@table,Records.Table_1 or is null)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 04:49:42

I have no idea what you are trying to accomplish.
Please explain. What is the "or is null" for? I have to ask since this is not a valid syntax.

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 04:54:04

i need to return all rows where table_1 = @table. if @table is null then im using the value of table_1 in the current row, unfortunatley this doesnt return rows where table_1 = null which i need. i now i could do something like

Table_1 = isNull (@table,Records.Table_1) or table_1 is null

but i only want the the null values of table_1 returned if @table is null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 04:57:03

IF @Table IS NULL
select * from table1 where tablecolumn is null
else
select * from table1 where tablecolumn = @table

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 05:06:53

thanks. However Table_1 is not the only column im searching on. im searching on 15 different columns which require the same operation as i explained in my second post. what i need is something like the following which isnt possible

select * from records where Table_1 = @table or if @table = null begin Table_1 is null end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 05:49:04

That does not make ANY sense at all.
Please post some sample data and your expected output.

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 06:12:11

cant see why. all i need is if @table1 is null then return back all rows regardless of whether table_1 has a value or not. if @table1 isnt null then return back only the rows that = @table1 Youve almost answered the question in your previous post but i cant use it that way as it is part of a complex query. The conditional syntax needs to be part of the where clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 06:14:44

Well then. It seems you need no more help from us with this problem?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 06:16:12

How about this?

Select * from Table
Where (@Table IS NULL or Table_1 = @table)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 06:19:19

I think he has tried that in the post made 04/18/2007 : 04:54:04
But who knows?

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 06:23:28

thanks harsh, but i only want the nulls returned if @table is null. its a shame sql cant compare nulls other wise this would be easy.

e.g but not valid

select * from records where Table_1 = @table or if @table = null begin Table_1 is null end

here im only checking if table_1 is null if @table1 = null

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 06:23:48

Rico,

Please clarify your problem. Please let me know what output you want in each of the following cases:

table_1		@table		output
---------------------------------------
Null		Null		?
Null		Not Null	?
Not Null	Null		?
Not Null	Not Null	?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 06:25:51

One more try:

Select * from Table
Where ((@Table IS NULL and Table_1 is NULL) or Table_1 = @table)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 06:26:19

You get an A+ for trying Harsh

I asked for sample data 04/18/2007 : 05:49:04, but OP refused to give...

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 06:26:44

table_1 @table output
---------------------------------------
Null Null 1
Null Not Null 0
Not Null Null 1
Not Null Not Null only if the values match

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 06:28:07

quote:
Originally posted by rico_bano

cant see why.
Youve almost answered the question in your previous post but i cant use it that way as it is part of a complex query.

The query is most certainly not complex! It is the problem description that is vague, so all we can do until you give us some sample data, are guesses. And so we have done for couple of hours now...

Imagine this! If you have given us sample data directly, this would hve been solved in matter of minutes...

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 06:31:48

Oh man!!

What does 1 and 0 mean? If both are NULLs, do you want only records where table_1 is NULL or you want records where table_1 is NULL as well as not Null?

Post sample data (covering all possible combinations) as Peter asked.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 06:32:11

select *, case when @table is null or @table = table_1 then 1 else 0 end
from sometable

But this you yourself has suggested in this topic, and Harsh...

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-04-18 : 07:01:43

sorry harsh i was using a truth table to display the results e.g 1 = true, 0 = false etc etc

this is the statement i now have

Table_1 = case when @table is not null then @table else "something goes here to return all rows" end

what could i place in the else clause that returns all rows regardless of table_1's value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 07:04:04

We can't read minds!

Now, for the THIRD time

select * from sometable
where @table is null or @table = table_1

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 07:06:36

Why do you keep ignoring the suggestions made?

Are you serious at all about this?

If you want all records when @table is null and only matching records when @table is not null then what's wrong with the solution I posted on 04/18/2007 : 06:16:12

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources