Need Help: Filter records with a combination of values

Question

Post reply

Need Help: Filter records with a combination of values

iam777here4u

Ten Centuries

Points: 1057
More actions
March 8, 2012 at 10:15 pm

#252238

Good Day,
I have a doubt regarding one of my query statement. My requirement is to get minimum value record from a table by filtering it with a combination of values. I have this function which have three nullable Params @Mod1 varchar(3), @Mod2 varchar(3), @MOD3 varchar(3). This function has to get value from a table with fields ID, Code, Modifier1, Modifier2, Rate. I have below combinations which i need to consider while fetching records
When there are two Modifiers, combinations are:
Modifier1----------------Modifier2
---------------------------------------------------
@Mod1--------------------@Mod2
@Mod2--------------------@Mod1
@Mod1--------------------NULL or Any value
Null or Any value--------@Mod1
@Mod2--------------------Null or Any value
Null or Any value--------@Mod2
NULL-----------------------NULL
When there are three Modifiers, combinations are:
Modifier1----------------Modifier2
---------------------------------------------------
@Mod1--------------------@Mod2
@Mod1--------------------@Mod3
@Mod2--------------------@Mod1
@Mod2--------------------@Mod3
@Mod3--------------------@Mod1
@Mod3--------------------@Mod2
@Mod1--------------------NULL or Any value
Null or Any value--------@Mod1
@Mod2--------------------Null or Any value
Null or Any value--------@Mod2
@Mod3--------------------Null or Any value
Null or Any value--------@Mod3
NULL-----------------------NULL
Which will be the best way to check all these combinations in WHERE clause? I have read somewhere that using 'OR' in this case may cause table scan. Maybe another way is case statement but i haven't given a thought about that.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 1

Hi,

Can you please provide some more information?

Some dummy data and the DML required to reproduce the problem you are trying to solve would be most useful.

Cheers,

Jim.

SQL SERVER Central Forum Etiquette[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Please read the first article I reference in my signature block below. Follow the instructions on what and how to post the information we need to help you. Also, include the expected results based on your sample data.

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 3

hi iam777here4u,

I think you can create where condition from UI (.net code). If Modifier values are not null.

Suppose Modifier1 and Modifier2 has values and Modifier3 is null you create where Modifier1 =2 and Modifier2=3 alone

Regards

Guru

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Take a look at Gail's blog post on catch all queries. It sounds like you are doing something similair??

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

iam777here4u Ten Centuries Points: 1057 More actions · Answer 5

Hi I did the following:

The idea is to get all records of possible combinations: Say @Mod1 = AA, @Mod2 = AB, @MOD3 = AC

I have table with columns, ID, Modifier1, Modifier2, Rate

It contains values like:

1, AA, AB, 1.00

2, CC, AA, 2.00

3, AB, AC, 3.00

4, NULL, NULL, 4.00

5, HH, HI, 5.00

etc

When I pass AA and AB for @mod1 and @Mod2 respectively. I want all those records as results except the 5th one.

For the time being I used IN statement:

select * from table where

Modifier1 in (@Mod1, @Mod2, @MOD3)

Modifier2 in (@Mod1, @Mod2, @MOD3)

if no records found

select * from table where

((Modifier1 in (@Mod1, @Mod2, @MOD3) and Modifier 2 is null)

OR (Modifier2 in (@Mod1, @Mod2, @MOD3) and Modifier1 is null))

If no record found

select * from table where

Modifier1 is null

Modifier2 is null

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 6

Hi,

Also, to get round having null values you could use COALESCE:

SELECT *

FROM Table

WHERE ISNULL(Modifier1, COALESCE(@Mod1, @Mod2, @MOD3)) IN (@Mod1, @Mod2, @MOD3)

OR ISNULL(Modifier2, COALESCE(@Mod1, @Mod2, @MOD3)) IN (@Mod1, @Mod2, @MOD3)

So if Modifier1 is NULL then the COELESCE function will return the first non null value out of @Mod1, @Mod2, @MOD3..

Does that help?

What business problem are you trying to solve anyway?

SQL SERVER Central Forum Etiquette[/url]