Creating filters using OR

Question

Creating filters using OR

Luke L

SSC-Dedicated

Points: 35547
More actions
October 15, 2007 at 12:32 pm

#199226

So I've got this report, and it brings back rows of data..
event, type, Initiator, Responder
23, 10, Bob, Steve
24, 15, Other, Bill
25, 10, Brian, Bob
26, 16, Other, Sue
etc. the Other's correspond to NULLS that come from an outer join because I don't know who they are. The other names all come from a known list of names.
To limit round trips to the database, I pull the data and then am using filters on the report, however I can't just use a filter on Initiator in (@InitList) because then the Other (NULLS) never get shown in the report. BTW, this is a multiselect Drop Down list that users should be able to view all or select individual ones. I'm populating it from another query to list all of my know users.
I suppose I could just use a union and include other in that list, but I was hoping to avoid that as it seemed like a bit of a hack.
Any other ideas?
thanks.
-Luke.
To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

It already looks like somewhere you are using ISNULL or COALESCE to convert your NULLS to "Other". Do that before your filter criteria is applied and include the "Other" in your selection list. The user would have to choose "Other" when they multi-select, but if you sort that to the top of the list, it should be pretty easy to understand.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Yeah, That's kinda what I thought the answer was going to be.

Thanks.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Just watch out for performance problems. If you are doing all of your filtering after you get the recordset, you will not see any additional performance problems, but if you have a select statement running against your database with a where similar to:

WHERE COALESCE(MyField,'') <> ''

you will end up with a table scan (SQL has to evaluate the function on every row to make the comparison so it cannot use an index).

Good luck

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

yup, that's the reason why I have it in filtering the results on the page and not in the recordset. also, That way I can pull the records once and cache them and let the users refilter them 100 different ways without another database call.

To help us help you read this[/url]For better help with performance problems please read this[/url]