September 2, 2016 at 9:14 am
I have a rule engine that generates the SQL from a script and thus the reason for the question and why I would rather not do it the standard way. So here is how I would do the query the standard way: (Field1, Field2, Field3, Field4, Field5 = int - These fields contain the same information but the values can be found in any of the columns.)
Select * from Table1
WHERE Field1 = 10 OR Field2 = 10 OR Field3 = 10 or Field4 = 10 or Field5 = 10
The rule engine let's the user define the operator, so it could be something like
Select * from Table1
WHERE Field1 BETWEEN 1 AND 10 OR Field2 BETWEEN 1 AND 10, etc.
or
Select * from Table1
WHERE Field1 IN (1,2,3,4,5) OR Field2 IN (1,2,3,4,5), etc.
So the problem comes in that the rule engine builds out the rule and it is more difficult to get the operator and value ahead of time, so what I was hoping to do was somehow write the SQL like this:
Select * from Table1
WHERE (Field1,Field2,Field3,Field4,Field5) = 10
Any ideas on how I can compare all 5 fields to whatever operator and value would follow?
September 2, 2016 at 9:21 am
You almost have it.
create table #test
(col1 int,
col2 int,
col3 int)
insert into #test values (null, 1, 2), (10, 11, 2), (1, 2, 3)
select *
from #test
where 1 in (col1, col2, col3)
September 2, 2016 at 9:47 am
But how would do a BETWEEN 1 AND 5
OR (Field1,Field2,Field3,Field4,Field5) > 10?
September 2, 2016 at 10:36 am
IN only works for equalities. But what did you want to be BETWEEN 1 and 5? Columns 1-5, or something else?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 2, 2016 at 11:07 am
In any event, for multiple-column testing, you're probably going to have to write some inline table-valued functions like this one. Go ahead and get the multiple ORs out of the way one time and then just pass the appropriate columns to it. Should be easy to adapt this to greater than or less than comparisons.
CREATE FUNCTION dbo.BetweenTest
(
@Lowint,
@Highint,
@Col1int,
@Col2int,
@Col3int,
@Col4int,
@Col5int
)
RETURNS TABLE
AS
RETURN
(
Select CASE WHEN @Col1 between @Low and @High
or @Col2 between @Low and @High
or @Col3 between @Low and @High
or @Col4 between @Low and @High
or @Col5 between @Low and @High
THEN 'Y'
ELSE 'N'
END as BetweenResult
)
With this function in place, your generated code should look like this:
SELECT *
FROM Table1
CROSS APPLY dbo.BetweenTest(1,10,Field1,Field2,Field3,Field4,Field5)
WHERE BetweenResult = 'Y'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 2, 2016 at 12:18 pm
I am not so sure this will work for all cases. Suppose you want to restrict something to two disjoint ranges (1 -> 5 and 10 -> 20). What would the expected output be? How do you eliminate all of the values from 6 through 9? Or are you going to accept any value between the minimum of fields 1-5 and the maximum of fields 1-5?
In reality, you may be better off normalizing a little, and pushing the Fieldn values into a single column in a new table that you would join back to the original on the primary key.
September 2, 2016 at 1:17 pm
Don't take unnecessary shortcuts when writing queries. Keep them clear and simple.
You seem to be trying to create a query that would allow to search on every column, instead of searching by attribute. Even if that's something that people might find useful, you should restrict it to certain attributes. This kind of filtering shouldn't be a common practice and for that reason there's no reason to use shortcuts.
September 2, 2016 at 2:12 pm
He's having a rules engine generate his SQL queries. He liked the functionality of IN, but it won't work for multiple columns at once. I assume he just wants to keep the generated queries compact or the engine could generate a long string of OR statements like the BetweenTest function contains.
I wrote the function to simply do a BETWEEN just to get him started, not to solve every conceivable problem. To handle disjoint ranges the function could be written to accept a table valued function with every range or individual value allowable. But the key is to just set up whatever functions are necessary to make his rules generator practical.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 2, 2016 at 5:12 pm
Thank you for your answers. I personally did not think there was a way to do it, but I did want to get others input as sometimes I can miss something obvious. I need to play around with the different options suggested and see if I can find a way.
One other thing I am playing with at the moment is to tag the SQL where this would be included and then see if I can extract that section, loop over it for the amount of fields and put the OR statement between them and then put it back into the SQL.
I am also wanting to keep it as easy to read through the code as possible and that's why (Field1,Field2,etc) would have been so nice, but oh well!
September 3, 2016 at 7:19 am
Douglas, I hope you realize that just because SQL doesn't provide a function straight out of the box, there is nothing that prevents you from creating user-defined functions that meet your needs. The BetweenTest function was just one example. It was written for five columns, but could have been written for fifty if need be. You could convert it to a greater than or less than comparison just as easily.
If that is unfamiliar ground for you, many people here will be happy to help you learn how. If that's something you would like to do, I would recommend you read the following article about how to post problems to help others help you with quick, tested solutions.
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Either way, best of luck to you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 4:27 pm
I am still trying to figure out the best way to do this. The problem with a function is that I don't know the values to pass into a function. This is because the query is built from a script and it loops over a unit separator delimiter and then figures out whether it will be a BETWEEN statement, an IN, NOT IN, >, <, etc. It also deals with ranges and exclusions, so this one field name could generate the following:
where ( Field1 IN ( 20 ) OR ( Field1 BETWEEN 1 AND 10 ) ) AND ( Field1 NOT BETWEEN 5 AND 6 )
In which I would want to duplicate as follows:
where (
( Field1 IN ( 20 ) OR ( Field1 BETWEEN 1 AND 10 ) ) AND ( Field1 NOT BETWEEN 5 AND 6 )
OR ( Field2 IN ( 20 ) OR ( Field2 BETWEEN 1 AND 10 ) ) AND ( Field2 NOT BETWEEN 5 AND 6 )
OR ( Field3 IN ( 20 ) OR ( Field3 BETWEEN 1 AND 10 ) ) AND ( Field3 NOT BETWEEN 5 AND 6 )
OR ( Field4 IN ( 20 ) OR ( Field4 BETWEEN 1 AND 10 ) ) AND ( Field4 NOT BETWEEN 5 AND 6 )
)
I have put in values that I use to extract the entire line via regular expression and I have it duplicating and changing the field name out, just need to do major testing to see if it causes any problems anywhere and works in all cases.
September 6, 2016 at 4:44 pm
I see your issue now. I'm afraid I don't have any brilliant insights. I hope the code you are generating above runs satisfactorily for you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 4:19 pm
Thank you for all the ideas though, this website is awesome. I am not sure whether to select any as an answer since it didn't really answer my particular need. If you feel otherwise, let me know.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply