asked on

Using a wildcard in combination with the CASE function

I've recently started to try and transition a multitude of complex Access databases to an SQL backend due to performance issues. It's my mission to fully utilize a "thin client" approach. In doing so, I'm running into a wall.

As a little background, i've built a table that i'll use to send all of my settings to that views will pull from for filtering with the intent of having a final data set sent back to Access that won't require additional querying. This table is "tblUsers_Settings".

In the Access front end, I have multiple combo boxes that when selected, are used to filter. If they are left blank, I want all records returned. The following filter works as long as the 'rPROID' has a value. If it has no value, I get no results, whereas I need to get all results if 'rPOID' has no value (basically no filter criteria was selected). I've temporarily put in the value 66 as a place holder. I've tried using '%', like '%', and a few other misc. attempts with no results.

Any suggestions on what the 66 should be replaced with to achieve the desired results? Is there a method better than CASE that is more appropriate?

= CASE WHEN (SELECT uValue FROM dbo.tblUsers_Settings AS tblUsers_Settings_2 WHERE (uUser = USER_NAME()) AND (uSetting = 'rPROID')) = '' THEN 66 ELSE (SELECT uValue FROM dbo.tblUsers_Settings AS tblUsers_Settings_2 WHERE (uUser = USER_NAME()) AND (uSetting = 'rPROID')) END

Jim Horn

>all of my settings to that views will pull from for filtering
Filtering implies a WHERE clause, whereas CASE is used mostly in SELECT to change a value based on other conditions.

>The following filter works as long as the 'rPROID' has a value. If it has no value,
In a query's WHERE clause, with the rPROID value as a parameter @rPROID, that would be ...

SELECT blah, blah, blah
FROM tblUsers_Settings
WHERE (rPROID = @rPROID OR @rPROID IS NULL)

Open in new window

Couple of articles that may help you:

1.

SQL Server CASE Solutions, with a wompload of images and sample code.

2.

Migrating your Access Queries to SQL Server Transact-SQL, which is a grab-bag of things to know when rewriting Access queries in SQL Server.

Level7Sensei

ASKER

I think I need to clarify.

Looking up the rPROID value doesn't give an error. There is always a value, but that value could be a number or it could be an empty '' value.

I'm only loading the rPROID value so that it can be used as a filter in primary query. For example, if rPROID returns "A", i want all results matching "A". If rPROID returns '', i want all results with no filtering.

Not sure what you mean by "@rPROID" - not familiar enough with SQL yet.

Also might be mixing up terminology, so I've attached a screenshot.
Capture.PNG

Jim Horn

> i want all results matching "A"
Define 'matching'. column_name = "A", or column_name that contains an "A" in it?

SELECT blah, blah, blah
FROM tblUsers_Settings
WHERE (rPROID LIKE '%' + @rPROID + '%' OR @rPROID IS NULL)

Open in new window

>Not sure what you mean by "@rPROID" - not familiar enough with SQL yet.
You're going to have to get there quick. Parameters are basically the same as [Enter your date here] constructs in SQL Server, where Access can call what's called a Stored Procedure, which can handle multiple queries, temp tables, etc. and return a set. Parameters are basically variables that can be passed into the SP/query.

For example, if the below were executed in SQL Server to create a stored procedure, running exec customers_by_state 'NV' would return all the sales where the customer is in Nevada.

CREATE PROC customers_by_state(@state varchar(2)) AS

SELECT *
FROM Sales s
   JOIN Customer c ON s.customer_id = c.id
WHERE c.state = @state
GO

Open in new window

SQL Server is a database only and does not have the ability to interact with forms like Access does, by including sql like [Forms]![MyFormName]![MyFieldName], so values are passed by using parameters.

Level7Sensei

ASKER

PROID is a column (attribute), as demonstrated in the attached screenshot.
rPROID is a report value derived from the "Select uValue FROM..." that is used to filter PROID.

If rPROID = "A", i need all records where attribute PROID contains the value "A"

If rPROID = '', i need all records regardless of their value.

ASKER CERTIFIED SOLUTION

Level7Sensei

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Level7Sensei

ASKER

The solution lied in the syntax of the "if blank" component of the CASE statement. I had to define the full table+criteria to do this, rather than only define the filter criteria.