Selecting NULL - 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

General SQL Server Forums

New to SQL Server Programming

Selecting NULL

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-10 : 03:04:06

Hi,

i need to perform some searching on 10 columns. Since it will return >1 records/lines, i need to filter, if @param1 match with col1, then abandon those Null value in column1 set, and find match @param2 to col1.
@param1='SS',
@param2='' (if '' i set to %)
til 10

Find matching columns:
Row1 : Col1='SS' (matched) Col2=NULL Col3='bb
Row2 : Col1='SS' (matched) Col2='abc' Col3=NULL
Row3 : Col1=NULL (unmatched) Col2='huh'(matched)

problem is when i want to search only if colX is 'something', it returns null value,.. my where clause

WHERE
(d.RecvUDF1 LIKE @UDF1 OR d.RecvUDF1 IS NULL) AND (d.RecvUDF2 LIKE @UDF2 OR d.RecvUDF2 IS NULL) AND (d.RecvUDF3 LIKE @UDF3 OR d.RecvUDF3 IS NULL) AND (d.RecvUDF4 LIKE @UDF4 OR d.RecvUDF4 IS NULL) AND (d.RecvUDF5 LIKE @UDF5 OR d.RecvUDF5 IS NULL ) AND (d.RecvUDF6 LIKE @UDF6 OR d.RecvUDF6 IS NULL) AND (d.RecvUDF7 LIKE @UDF7 OR d.RecvUDF7 IS NULL ) AND (d.RecvUDF8 LIKE @UDF8 OR d.RecvUDF8 IS NULL) AND (d.RecvUDF9 LIKE @UDF9 OR d.RecvUDF9 IS NULL) AND (d.RecvUDF10 LIKE @UDF10 OR d.RecvUDF10 IS NULL )

-i wrote the OR NULL is because i need those nulls value when im not searching for that column..
-i replace '' to '%' because i need to list any other remaining unmatched columns when i found the matching column.

maybe it's quite ridiculous to understand my Q ...coz i couldnt find any other better way to explain

thanks in advance

~~~Focus on problem, not solution~~~

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 03:20:05

You mean something like this?

WHERE 
	(d.RecvUDF1 LIKE @UDF1 OR @UDF1 = '%' ) AND ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-10 : 03:29:52

ermm.. actually i already set the param @udfX to '%'

(d.RecvUDF1 LIKE @UDF1 OR d.RecvUDF1 IS NULL)

maybe i should go try
thanksss

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-10 : 03:42:38

no, it abandoned all the nulls, so couldnt find like this case

find
@udf1='abc'
@udf2=null
@udf3=null

result=0, suppose :-
recvudf1=abc recvudf2=null recvudf3=null
recvudf1=abc recvudf2=jj recvudf3=null
recvudf1=abc recvudf2=null recvudf3=yy

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 03:45:23

It is much better to set @UDFx to NULL when "not using" or "not searching by" that parameter.
Using the % character searching for anything when also using LIKE instead of = .

Then you can write queries like this (searching for any part of the column that matches the parameter.

SELECT * FROM MyTable
WHERE (Col1 LIKE '%' + @Param1 + '%' OR @Param1 IS NULL)
AND (Col2 LIKE '%' + @Param2 + '%' OR @Param2 IS NULL)
AND (Col3 LIKE '%' + @Param3 + '%' OR @Param3 IS NULL)
AND (Col4 LIKE '%' + @Param4 + '%' OR @Param4 IS NULL)
AND (Col5 LIKE '%' + @Param5 + '%' OR @Param5 IS NULL)
AND (Col6 LIKE '%' + @Param6 + '%' OR @Param6 IS NULL)
AND (Col7 LIKE '%' + @Param7 + '%' OR @Param7 IS NULL)
AND (Col8 LIKE '%' + @Param8 + '%' OR @Param8 IS NULL)
AND (Col9 LIKE '%' + @Param9 + '%' OR @Param9 IS NULL)
AND (Col10 LIKE '%' + @Param10 + '%' OR @Param10 IS NULL)

or like this if you want the columns to match the parameter exactly

Then you can write queries like this (searching for any part of the column that matches the parameter.

SELECT * FROM MyTable
WHERE (Col1 = @Param1 OR @Param1 IS NULL)
AND (Col2 = @Param2 OR @Param2 IS NULL)
AND (Col3 = @Param3 OR @Param3 IS NULL)
AND (Col4 = @Param4 OR @Param4 IS NULL)
AND (Col5 = @Param5 OR @Param5 IS NULL)
AND (Col6 = @Param6 OR @Param6 IS NULL)
AND (Col7 = @Param7 OR @Param7 IS NULL)
AND (Col8 = @Param8 OR @Param8 IS NULL)
AND (Col9 = @Param9 OR @Param9 IS NULL)
AND (Col10 = @Param10 OR @Param10 IS NULL)

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-10 : 21:25:32

thanks peso,
b4 this i've tried both of your method, problems :-
i.for method 1, it return inprecise result like search 12, returns 1212, 2212, He12ko, etc
ii.for method 2, it search exactly, but abandoned many result that are suppose to be like

Find :-
@param1='abc'
@param2=null (user didnt enter any searching criteria='', so i set to null, if i set to %, it return incorrect search result also.
@param3=null
@param4=null

couldnt find this row, when im trying to match if any col1='abc' without considering the rest of the 9 columns....consider this
col1=abc
col2=nn
col3=null
col4=null

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 01:28:00

Replace all the ANDs with OR!

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-11 : 02:13:14

cannot.. that will return all the non 'abc' col1

~~~Focus on problem, not solution~~~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 02:26:05

select * from YourTable
where isnull(@param1, char(1)) = isnull(col1, char(1))
and isnull(@param2, char(1)) = isnull(col2, char(1))
and isnull(@param3, char(1)) = isnull(col3, char(1))
and isnull(@param4, char(1)) = isnull(col4, char(1))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 03:00:09

quote:
Originally posted by maya_zakry

thanks peso,
b4 this i've tried both of your method, problems :-
i.for method 1, it return inprecise result like search 12, returns 1212, 2212, He12ko, etc
ii.for method 2, it search exactly, but abandoned many result that are suppose to be like

LIKE '12'

and

= '12'

will get the same result.

So what is the problem?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-11 : 11:22:42

Would it be possible for you to supply DDL, smple data and the expected result set?

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-11 : 20:04:17

quote:
Originally posted by Peso

select * from YourTable
where isnull(@param1, char(1)) = isnull(col1, char(1))
and isnull(@param2, char(1)) = isnull(col2, char(1))
and isnull(@param3, char(1)) = isnull(col3, char(1))
and isnull(@param4, char(1)) = isnull(col4, char(1))

Peter Larsson
Helsingborg, Sweden

PESO!
this work so far.. i've tested with my problem case, it works just fine.. just could u explain what is char(1)? it replace null with char(1)? thank u so much..

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-11 : 20:14:24

wooppss.. im happy to soon.. other issue arised!
couldnt find this row :-
col1=1 col2=2 col3=null
col1=1 col2=SS col3=123

only return this row :- (means it replace other cols with null, so will find all the null cols, whereas i want to ignore whateva values in the columns if i only want to match col1 with param1 only... uwawaaaa....
col1=1 col2=null col3=null

~~~Focus on problem, not solution~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-11 : 20:41:06

what do i do to ignore any other column values if i just wanna match X param to X column? What should i set my 10 params in my SP, so that it could match only columns that match the NON ''/null/0 values?

@param1=maya
@param2=''
@param3=''

i suppose my result should be :-
col1=maya col2=cat col3=null
col1=maya col2=null col3=null
col1=maya col2=cat col3=dog

obviously, in this case i want to search for col1=maya, without considering what values in col2,col3--->col10. My problem is the @param1 til @param10 is still sent from the codebehind as 'something' or '' (nothing) which i try to reset in my sp as :-

IF @RecvUDF1='0' OR @RecvUDF1=''
SET @UDF1=NULL --- i also replace with '%' but it includes the null when i dont want nulls
ELSE
SET @UDF1=@RecvUDF1

BUT, with all the solution ive tried, it only return :-
col1=maya col2=null col3=null (reset param to null) OR

col1=maya col2=cat col3=null (reset param to '%')
col1=maya col2=null col3=null
col1=maya col2=cat col3=dog
col1=null col2=null col3=null

~~~Focus on problem, not solution~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources