Different Search with Nullable Column Issue Due to Coalesce

[Coder7]

Good afternoon all. I've read the posted threads on this topic but none match my case and I'd really appreciate your help.

I use coalesce in my search query which, until now, worked great because none of the columns in the search criteria were nullable. My new search criterion (sup_tso_id) is nullable.

If the input parameter contains a value for sup_tso_id, then I want only rows where the sup_tso_id = @sup_tso_id else I want all rows regardless of the value of sup_tso_id which is nullbale (in conjuction with any other search criteria values that are passed as input parameters to the stored procedure).

I can handle this with nested ifs but this query was a maintenance nightmare using nested ifs until I cleaned it up by using coalesce and I'd like to keep it as clean as possible.

A portion of the search stored procedure:

Select *
From header_data
Where tx_id = coalesce(@tx_id,tx_id)
and tx_status = coalesce(@tx_status,tx_status)
and qa_tso_id = coalesce(@qa_tso_id,qa_tso_id)
and eval_id = coalesce(@eval_id,eval_id)
and sup_tso_id = ????????????
order by tx_id.

Thanks for any and all suggestions.

 

[NoCoolHandle]

You might want to consider just using the isnull function.

Collese is a bizzare select case..

It tries the first part, if that is null, then it goes to the next, if it is null then it goes to the next....

ISNull just looks if it is null and if so replaces the null with the 3rd argument...

You could also try (and this should fix your query)
converting the column 'sup_tso_id' to character and using the like with a wildcard

i.e.
Select *
From header_data
Where tx_id = coalesce(@tx_id,tx_id)
and tx_status = coalesce(@tx_status,tx_status)
and qa_tso_id = coalesce(@qa_tso_id,qa_tso_id)
and eval_id = coalesce(@eval_id,eval_id)
and cast(sup_tso_id as varchar(300)) = isnull(@sup_tso_id,'%')


order by tx_id.

 

[Coder7]

Hi NoCoolHandle.

I really appreciate your input, thanks alot!

I like the coalesce so that's staying (for everything but sup_tso_id) but you have given me food for thought with the wildcard with isnull so I'm playing around with that.

Currently I'm not getting any rows back but I'll see if I can figure that out. Sup_tso_id is varchar(10) so I didn't have to cast the data type.

Thanks again for helping.

 

[ESquared]

I think reason this isn't working is because the table you are selecting from can have Nulls in it. So any row that has a Null will be passed over because Null = Null is false. There's also a problem with the last AND clause.

Here's the construction I recommend. It could offer some performance benefits as well, because no function is being used, and no evaluation needs to be made on a column when the local variable is null.

SELECT *
FROM header_data
WHERE
   @tx_id IS NULL OR @tx_id = tx_id
   AND @tx_status IS NULL OR @tx_status = tx_status
   AND @qa_tso_id IS NULL OR @qa_tso_id = qa_tso_id
   AND @eval_id IS NULL OR @eval_id = eval_id
   AND @sup_tso_id IS NULL OR @sup_tso_id = sup_tso_id

I don't know what you are doing with the last '%' thing, but if you are trying to use it as a wildcard it won't work, you need the like operator. What data type is sup_tso_id and do you need to partial-text match it?

Maybe

   AND @sup_tso_id IS NULL OR Convert(varchar(300),sup_tso_id) LIKE @sup_tso_id

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Coder7]

Good morning Esquared.

The coalesces are good as is...none of those columns are nullable so there's no reason to check if the value is null or not.

I was just trying to find a SIMPLE way to handle the sup_tso_id which is a nullable field that I now need to use as an input parameter to meet a new business rule.

NoCoolHandle suggested the wild card using like but I can't figure out how to make that work..now I get no rows returned.

I think, due to time constraints, I'll just have to use some if statements but I bet there is a nice way to do that that's cleaner and easier to maintain.

Appreciate your input. Have a great day.

 

[Pattycake245]

Why not just:

Select *
From header_data
Where tx_id = coalesce(@tx_id,tx_id)
and tx_status = coalesce(@tx_status,tx_status)
and qa_tso_id = coalesce(@qa_tso_id,qa_tso_id)
and eval_id = coalesce(@eval_id,eval_id)
and sup_tso_id = case
when @sup_tso_id <> '' or @sup_tso_id is not null
then @sup_tso_id
else sup_tso_id
end
order by tx_id.

 

[Coder7]

Pattycake,

Now THAT looks promising. I've already added the ifs and am testing but I'll check out your suggestion and revert my sql if I have time (and I just might!) because you're solution is clean and succinct and and probably more efficient.

WTG
THANKS!! I'll let you know.

 

[NoCoolHandle]

use crap
go
create table test
(c1 int identity primary key,
 c2 int ,
c3 int,
c4 int)
go
insert into  test values(1,null,1)
insert into  test values(1,1,null)
insert into  test values(1,2,null)
insert into  test values(2,null,2)
insert into  test values(2,null,null)
insert into  test values(1,null,3)
insert into  test values(4,null,6)
insert into  test values(null,null,1)
go

declare @x varchar(300)
select  * from test
where cast(c4 as varchar(300)) like isnull(@x,'%')

retuns
[blue]
c1 c2 c3 c4
----------- ----------- ----------- -----------
1 1 NULL 1
4 2 NULL 2
6 1 NULL 3
7 4 NULL 6
8 NULL NULL 1
[/blue]
It did drop any records from c4 that were null, but that can be fixed with

declare @x varchar(300)

select  * from test
where isnull(cast(c4 as varchar(300)),'') like isnull(@x,'%')

Returns
[blue]
c1 c2 c3 c4
----------- ----------- ----------- -----------
1 1 NULL 1
2 1 1 NULL
3 1 2 NULL
4 2 NULL 2
5 2 NULL NULL
6 1 NULL 3
7 4 NULL 6
8 NULL NULL 1
[/blue]

 

[ESquared]

I don't understand why you don't like my solution. It covers every eventuality and should be *faster* than all these methods using ISNULL() and COALESCE().

Program for results!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ESquared]

Oh... I forgot all the parentheses to make sure the logic groupings are right... just place them where they belong!

-------------------------------------
A sacrifice is harder when no one knows you've made it.