November 8, 2013 at 10:20 am
Hi all,
I've had a trawl through the forum but can't find anything that will help although I'm sure this isn't the first time this hasn't come up. I'm trying to develop a table which stores criteria for conditions, this is to replace a function which currently does this
Currently 5 values are passed to the function which returns an integer value based on the criteria of some of the values of the parameters passed
i.e case when condition 1 = blah and condition2 = x then 1 or condition 1 = nah and condition 3 = yyy then 5 etc
Initially I built a table with the columns using the parameters as headers (sorry about the spacing)
e.g
TeamID----Condition1---Condition2---Condition3---Condition4
1------------blah-----------x
1------------nah-----------yyy
2------------blah
3----------------------------------------------------test
However, this starts to get very complicated when the you get WHERE conditions that both return true values -
select from tblrules where (condition1 = blah and condition2 = x) OR (condition4 = test)
this will return a 1 and 3 as both conditions are true.
A function can handle this but I can't seem to do this against a table using OR statements. Can anyone advise another way of doing this using a table.
November 8, 2013 at 10:47 am
It seems like your issue is that there is an additional 'test' condition, that overrides the other conditions?
You could forget the 'or' logic and include all iterations including the test condition in your table, making the combinations of conditions unique.
If there's one unique condition that determines a test and the test value is always the same for any test, I'd separate out that bit of logic from the table and move it into your query. Put all of your other conditions into your table and join appropriately, then use a case statement in your final select to check the test condition and override the regular value if necessary.
November 11, 2013 at 2:15 am
Thanks sestell, any chance you have any examples of this?
Originally this code was written as a function so if I can I'd like to keep the query using a function but its checking against a table instead of case statements within the function itself. I'm just wondering if this is possible or am I looking at doing quite a big rework of how this is done.
November 11, 2013 at 6:30 am
If you are going to go to all the trouble of rewriting the logic to use a table, I'm not sure why you'd want to wrap it back up in a function. There would be no need and your performance will suffer.
November 11, 2013 at 6:43 am
The function at present simply takes in 5 parameters and based on 1 or more of these values, the case statement within the function will return the ID where the criteria meets. My plan would be to pass the parameters to the function as normal and instead of the case statement, select against the table based on the parameters and the where clauses
AS a CASE statement will return the first value where the condition is true, I'll simply to the same using a MAX or FIRST etc
If you think I'm going about it the wrong way by all means let me know, constructive criticsm is always appreciated
November 11, 2013 at 5:25 pm
I think that you'll probably be able to get a better answer if you provide some additional information:
- DDL (CREATE TABLE) for your source table
- INSERT statement(s) to populate the source with some sample data
- Show us what you expect your results to be when you pass in the criteria table you've specified.
This sounds like a variation of a dynamic search stored procedure. If that is what you're trying to do, you might want to have a look at this article:
How to Design, Build and Test a Dynamic Search Stored Procedure [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 12, 2013 at 9:19 am
Hi Dwain
Insert Statement:
INSERT INTO [Criteria]
([TeamID]
,[WorkGroup]
,[StatusCode]
,[Flag]
,[Condition1]
,[Condition2]
,[condition3])
VALUES
(<TeamID, int,>
,<WorkGroup, varchar(10),>
,<StatusCode, int,>
,<Flag, varchar(4),>
,<Condition1, varchar(10),>
,<Condition2, varchar(10),>
,<condition3, varchar(10),>)
In this table I have 4 values which determine the criteria, I then have 3 condition columns which hold values such as IN or NOT EXISTS or <= etc depending on what makes up the criteria of the rule.
So in the function version of the code, the parameters are passed and the function uses multiple case statements, I could have something like
CASE
WHEN @WorkGroup = 'xxxxx' THEN 1
WHEN @Flag = 'YES' and StatusCode < 6 THEN 2
END
The solution that I'm currently going for would select off the table, the above could would look something like this
SELECT TeamID from Criteria
WHERE WorkGroup = @Workgroup
OR
SELECT TeamID from Criteria
WHERE Flag = @Flag and Condition1 = '<' and StatusCode = 6
This gets very long winded as you can have multiple conditions that are simple one liners in a case statement but would be one row each in a table version.
Let me know if you have anymore questions
November 12, 2013 at 5:37 pm
mitzyturbo (11/12/2013)
Hi DwainInsert Statement:
INSERT INTO [Criteria]
([TeamID]
,[WorkGroup]
,[StatusCode]
,[Flag]
,[Condition1]
,[Condition2]
,[condition3])
VALUES
(<TeamID, int,>
,<WorkGroup, varchar(10),>
,<StatusCode, int,>
,<Flag, varchar(4),>
,<Condition1, varchar(10),>
,<Condition2, varchar(10),>
,<condition3, varchar(10),>)
In this table I have 4 values which determine the criteria, I then have 3 condition columns which hold values such as IN or NOT EXISTS or <= etc depending on what makes up the criteria of the rule.
So in the function version of the code, the parameters are passed and the function uses multiple case statements, I could have something like
CASE
WHEN @WorkGroup = 'xxxxx' THEN 1
WHEN @Flag = 'YES' and StatusCode < 6 THEN 2
END
The solution that I'm currently going for would select off the table, the above could would look something like this
SELECT TeamID from Criteria
WHERE WorkGroup = @Workgroup
OR
SELECT TeamID from Criteria
WHERE Flag = @Flag and Condition1 = '<' and StatusCode = 6
This gets very long winded as you can have multiple conditions that are simple one liners in a case statement but would be one row each in a table version.
Let me know if you have anymore questions
Unfortunately, that wasn't what I was asking for. This article by SQL MVP Jeff Moden might help clarify:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Did you read the article I originally suggested on creating a dynamic search stored procedure? The concept still sounds similar to me. You could pass in your table of search criteria and write the SP on that basis (probably).
If I had sample data in a source table, along with some specific examples of how you want to pass in your search criteria and what they'd return (like in Jeff's article), then I could help you further. Otherwise, I'm afraid a theoretical discussion on this topic may not yield fruit.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2013 at 9:41 am
dwain, first off, I've just finished the article you sent on. Its similar to what I'm planning to build alright but slightly different. The dynamic search stored procedure will be similar to how I lay out my rules / conditions but they will be using values based in a table (Criteria) instead of case statements. But I can definitely incorporate the format of that stored procedure into my code in a similar fashion
Secondly, apologies for the format of the initial query. Let me give you a brief understanding of why I'm doing it this way. I'm looking to build a way for users to make these rules configurable. So, instead of storing the rules within code i.e. case statements, I'm looking to build a rules table. This will be populated by an excel spreadsheet which the users can add, edit or delete rules in the sheet as they see fit.
I have reworked it below:
--Create A Table With The Stored Criteria
Create Table Criteria
(
TeamID int,
WorkGroup varchar(10),
StatusCode int,
Flag varchar(4),
Condition1 varchar(10),
Condition2 varchar(10),
Condition3 varchar(10)
)
--Insert Test Values
INSERT INTO Criteria VALUES
(9, 'Dev', 0,'', 'IN', NULL, NULL)
INSERT INTO Criteria VALUES
(9, 'Fee', 0, '', 'IN', 'NOT IN', NULL)
INSERT INTO Criteria VALUES
(9, 'Prod', 0, '', 'IN', 'NOT IN', NULL)
INSERT INTO Criteria VALUES
(2, '',6, 'Yes', 'IN', '>', 'IN' )
INSERT INTO Criteria VALUES
(5, '',4,'No', 'IN', '>', 'IN')
INSERT INTO Criteria VALUES
(3, 'P01', 0,'No', 'IN', 'NOT IN', 'NULL')
INSERT INTO Criteria VALUES
(3, 'P02', 0,'Yes', 'IN','NOT IN', 'NULL')
INSERT INTO Criteria VALUES
(6, 'L02', 4,'Yes', 'IN','<=', 'NOT IN')
INSERT INTO Criteria VALUES
(6, 'L03', 4,'Yes', 'IN','<=', 'NOT IN')
So now what I want to do is return the teamid based on matching criteria. Currently this is being done by passing the Workgroup, StatusCode and Flag values to the function, where the team id is returned based on case statements e.g
SELECT CASE WHEN @Workgroup IN ('Dev') THEN 9
WHEN @Flag IN ('YES') AND @StatusCode > 6 THEN 2
WHEN @Flag IN ('NO') AND @StatusCode > 4 THEN 5
etc
--Due to time constraints I can't write out all the different rules as they currently stand, this is just a sample few.
So to replace this, I want to pass the values to the function as before but replace the case statments by selecting off the table. So based on something similar to the case statements above, I would write the code against the table like this:
--For the sake of testing lets test against one set of values
--This should return no team ID as it shouldn't meet any of the criteria
DECLARE @WorkGroup varchar(10),
@StatusCode int,
@Flag varchar(4)
SET @WorkGroup = 'P02'
SET @StatusCode = 2
SET @Flag = 'NO'
--The first rule is to determine where the workgroup is equal to the workgroup value
SELECT TeamID From Criteria WHERE Workgroup = @Workgroup AND Condition1 = 'IN' AND Condition2 IS NULL
OR
--The second rule is checking where the flag equals the @flag, the status code is <= @status code
--and the Workgroup is not in @workgroup
Flag = @Flag AND @StatusCode < StatusCode
And WorkGroup NOT IN (@WorkGroup) AND Condition2 = '<=' AND Condition3 = 'NOT IN'
This returns two teamid's of 6 as there are 2 rows which match the criteria. I know this is just down to the way in which I've written the select but this is just a basic idea of how I'm setting up my data, I'm still in the prototype stage of the solution I'm building.
Basically I want to know if I'm going about this the right way as I can see the table getting larger and more complex if we start introducing new conditions and therefore adding more OR statements.
Let me know if you need anything more, happy for any help you can add
November 13, 2013 at 6:50 pm
Mitzy - You are lucky! I almost missed this in the flood of response emails I got last night.
This probably isn't perfect but may give you an idea.
DECLARE @SQL NVARCHAR(MAX) = N''
,@SQLParms NVARCHAR(MAX) = N'';
SELECT @SQL = 'SELECT TeamID FROM Criteria WHERE ' + CHAR(10) +
STUFF(
(
SELECT
CASE
WHEN Condition1 IS NOT NULL
THEN 'OR WorkGroup ' + Condition1 + ' (' + QUOTENAME(Workgroup, '''') + ')' + CHAR(10)
ELSE ''
END +
CASE WHEN Condition2 IS NOT NULL
THEN 'OR StatusCode ' + Condition2 + ' (' + CAST(StatusCode AS VARCHAR(5)) + ')' + CHAR(10)
ELSE ''
END +
CASE WHEN Condition3 IS NOT NULL AND Condition3 <> 'NULL'
THEN 'OR Flag ' + Condition3 + ' (' + QUOTENAME(Flag, '''') + ')' + CHAR(10)
-- Check your data for how I'm handling this condition
WHEN Condition3 = 'NULL'
THEN 'OR Flag ' + 'IS NULL' + CHAR(10)
ELSE ''
END
FROM #Criteria
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
), 1, 3, '');
PRINT @SQL
EXEC sp_executesql @SQL, @SQLParms;
Note that Dynamic SQL can't be used in a FUNCTION, so this code would need to be in a stored procedure. You can still pass your table into a SP.
Edit: Note that I didn't do it, but every leading apostrophe that starts a character string should also have N in front of it. Like:
SELECT @SQL = N'SELECT TeamID FROM Criteria WHERE ' + CHAR(10)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 14, 2013 at 1:57 am
Cheers Dwain, thanks for this
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply