January 13, 2010 at 11:58 am
Greetings folks. I'm not sure how to best pose my question, so a little background info may help. Im working on a VB search form where users can click in option boxes to alter the SQL that is generated and sent to SQL Server. Lets say I have 3 options, 'Option1..Option3'. On the form, the user could choose any one, all, or any combination of options. So, if they choose option1 only, the SQL WHERE clause will be appended with '" AND t.field1 IS NOT NULL "'. If they choose option1 and option2, I would have to append it with two lines. In the VB code, I have string variables for each of the options, and have assigned to them incrementing powers of 2. Since the option boxes have a value of either -1 or 0, I can then sum the values of the chosen options to arrive at one value. My string variables are like this...
Dim strOption1 as Integer
Dim strOption2 as Integer
Dim strOption3 as Integer
Dim strOption1SQL as string
Dim strOption2SQL as string
Dim strOption3SQL as string
Dim strFinalWhere as string
Dim OptionSum as Integer
' spaces in the single quotes are only for differentiation from doubles
strOption1SQL = " AND t.field1 IS NOT NULL " '
strOption2SQL = " AND t.field2 = ' 'some value' ' "
strOption3SQL = " AND t.field3 = ' 'some value' ' "
strOption1 = OptionBox1.value*2
strOption2 = OptionBox2.value*4
strOption3 = OptionBox3.value*8
OptionSum = strOption1 + strOption2 + strOption3
So, if the user chooses Option1 and Option2, the value of the OptionSum variable will be 6, since if Option3 is not selected, the value of strOption3 will be 0*8. So, my final where clause building looks like this.
Select Case True
Case OptionSum = 2
strFinalWhere = strOption1SQL
Case OptionSum = 4
strFinalWhere = strOption2SQL
Case OptionSum = 6
strFinalWhere = strOption1SQL & strOption2SQL
Case OptionSum = 8
strFinalWhere = strOption3SQL
Case OptionSum = 10
strFinalWhere = strOption1SQL & strOption3SQL
Case OptionSum = 12
strFinalWhee = strOption1SQL & strOption2SQL & strOption3SQL
End Select
So far so good. Problem is that yesterday someone wanted 1 more option added, which would have made the max value of OptionSum = 30. Basically I need to figure out the combinations of options that = all even numbers up to 30. I did this manually, and it was not fun. I already know that someone else is wanting a fifth option which means I have to figure out the combinations of all options up to 62. I think it is time to find a way to do this automatically, so here is where I am
First, I created a table to hold the options, their 2^id value, and the SQL
IF OBJECT_ID('TempDB..#options','u') IS NOT NULL
DROP TABLE #options
CREATE TABLE #options
(
ID INT IDENTITY(1,1),
VBOption VARCHAR(50),
VBOptionVal INT,
VB_SQL VARCHAR(200)
)
INSERT INTO #options (VBOption,VB_SQL)
SELECT 'Option1','" AND t.field1 IS NULL "' UNION ALL
SELECT 'Option2','" AND t.field2 = ''some val'' "' UNION ALL
SELECT 'Option3','" AND t.field3 IS NOT NULL "' UNION ALL
SELECT 'Option4','" AND t.field4 = ''some val'' "'
Now, update the 2^id value
UPDATE o
SET VBOptionVal = POWER(2,n)
FROM #options o INNER JOIN tally t
ON o.id = t.N
WHERE t.n <= o.id
AND o.VBOptionVal IS NULL
SELECT * FROM #options
I then created and populated table to hold all the even numbers possible given the number of available options, in this case 2 through 62, with a column to hold all the option combination that sums to the value. The greatest option value, in this case 62, is easy to figure out, so I populated it to give an example of what I'm needing.
--First create the table and populate the even numbers up to 62
DECLARE @MaxVal INT
IF OBJECT_ID('TempDB..#all_evens','u') IS NOT NULL
DROP TABLE #all_evens
SELECT @MaxVal = ISNULL(@MaxVal,0) + POWER(2,n)
FROM #options o INNER JOIN tally t
ON o.id = t.n
WHERE t.n <= o.id
SELECT
OptionVal = n
INTO #all_evens
FROM tally t
WHERE t.n <= @MaxVal
AND n%2 = 0
ALTER TABLE #all_evens
ADD ValsToInclude CHAR(100)
Now update the record for 62 since that one is easy.
DECLARE @MaxVal INT,@string VARCHAR(100)
SELECT
@MaxVal = ISNULL(@MaxVal,0) + POWER(2,n),
@string = ISNULL(@string,'') + CAST(POWER(2,n) AS VARCHAR) + ', '
FROM #options o INNER JOIN tally t
ON o.id = t.n
WHERE t.n <= o.id
UPDATE e
SET ValsToInclude = SUBSTRING(@string,1,LEN(@string)-1)
FROM #all_evens e
WHERE e.OptionVal = @MaxVal
SELECT @MaxVal,@string
SELECT * FROM #all_evens
WHERE ValsToInclude IS NOT NULL
So I am left with a table of even numbers, and I need to populate the ValsToInclude column with the appropriate string of powers of 2. My results should look like this...
OptionValsValsToInclude
22
44
62,4
88
102,8
124,8
142,4,8
1616
182,16
204,16
222,4,16
.....
622,4,8,16,32
I hope this makes sense to someone, and again, once I can get a string of values, I will be good to go, since the power of 2 value will be unique for each record, and I can join on it to build the VB script.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2010 at 12:20 pm
I should note that the post only included inserting 4 options, where the max value of all of them would only be 30, not the 62 used as an example, but the idea is the same. Sorry for the confusion.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2010 at 12:20 pm
Would it work to use bitwise operators instead? It might be a little easier to add new items later:
DECLARE @OptionSum int
DECLARE @strFinalWhere varchar(max)
SELECT @OptionSum = 12
, @strFinalWhere = ''
--You're not using 1, so we don't need this (and 3, 5, 7, etc are thus not an option
--IF (@OptionSum & 1) = 1
--BEGIN
--SET @StrFinalWhere = @StrFinalWhere + ' and 1 '
--END
IF (@OptionSum & 2) = 2
BEGIN
SET @StrFinalWhere = @StrFinalWhere + ' and 1'
END
IF (@OptionSum & 4) = 4
BEGIN
SET @StrFinalWhere = @StrFinalWhere + ' and 2'
END
IF (@OptionSum & 8) = 8
BEGIN
SET @StrFinalWhere = @StrFinalWhere + ' and 3'
END
IF (@OptionSum & 16) = 16
BEGIN
SET @StrFinalWhere = @StrFinalWhere + ' and 4'
END
--Trim off the first 5 characters so that the where clause is valid (does not look like "WHERE AND")
select RIGHT(@StrFinalWhere, LEN(@StrFinalWhere) - 5)
Chad
January 13, 2010 at 12:42 pm
I would use a function to get the "binary pattern". Something like the following:
CREATE FUNCTION tvf_binarySplit(@a int)
returns TABLE
as
RETURN
SELECT (@a & power(2,n)) val
FROM
(SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
)a
WHERE (@a & power(2,n)) >0
Test scenario:
DECLARE @t TABLE (opt int,res varchar(50))
INSERT INTO @t (opt)
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 8 UNION ALL
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 14 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 20 UNION ALL
SELECT 62
SELECT
opt,
stuff((SELECT ',' + cast(val AS varchar(50)) FROM dbo.tvf_binarySplit(opt) FOR XML path('')),1,1,'') AS ValsToInclude
FROM @t
/* result set
optValsToInclude
22
44
62,4
88
102,8
124,8
142,4,8
1616
182,16
204,16
622,4,8,16,32
*/
January 13, 2010 at 1:04 pm
Take a look at this, see if it gets you going in the right direction:
if object_id(N'tempdb..#Options') is not null
drop table #Options;
create table #Options (
Val int primary key,
RuleStr varchar(max) not null);
insert into #Options (Val, RuleStr)
select 0, 'Col1 = @Col1' union all
select 2, 'Col2 = @Col2' union all
select 4, 'Col3 = @Col3' union all
select 8, 'Col4 = @Col4';
declare @Sum int, @Where varchar(max);
select @Sum = 12;
;with Rules (RuleVal) as
(select (@Sum & power(2, Number))
from dbo.Numbers
where Number <=20)
select @Where =
(select 'and ' + RuleStr + ' '
from #Options
inner join Rules
on Val = RuleVal
for XML path(''), type).value('.[1]','varchar(max)');
select @Where = '1=1 ' + @Where;
select @Where;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2010 at 1:16 pm
@Gus:
Just as a side note: AFAIK varchar(max) columns tend to have a bad influence on performance due to the handling of more than one page (8k).
I guess, varchar(8000) will do the job as well and should perform better...
(I remember having a short "discussion" with Jeff a while ago on one thread regarding varchar(max) vs. varchar(8000) within a string split function...)
January 13, 2010 at 1:23 pm
@Gus (again): Another thought:
If you'd add AND (@Sum & power(2, Number)) > 0 to your WHERE clause in the Rules CTE you'd get rid of the repetitive Col1=@Col1 part in your resulting @where variable, making it even shorter.
January 13, 2010 at 1:28 pm
lmu92 (1/13/2010)
I would use a function to get the "binary pattern". Something like the following:
CREATE FUNCTION tvf_binarySplit(@a int)returns TABLE
as
RETURN
SELECT (@a & power(2,n)) val
FROM
(SELECT 1 AS n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
)a
WHERE (@a & power(2,n)) >0
Test scenario:
DECLARE @t TABLE (opt int,res varchar(50))INSERT INTO @t (opt)
SELECT 2 UNION ALL
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 8 UNION ALL
SELECT 10 UNION ALL
SELECT 12 UNION ALL
SELECT 14 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 20 UNION ALL
SELECT 62
SELECT
opt,
stuff((SELECT ',' + cast(val AS varchar(50)) FROM dbo.tvf_binarySplit(opt) FOR XML path('')),1,1,'') AS ValsToInclude
FROM @t
/* result set
optValsToInclude
22
44
62,4
88
102,8
124,8
142,4,8
1616
182,16
204,16
622,4,8,16,32
*/
That is exactly what I was trying to do. I'm new to 2008, so I'm going to have to look into what the XML part is doing. Also never used & before, but all of you have used it, so that is another one to work on. I thank all of you for such prompt responses.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2010 at 1:39 pm
You're very welcome, Greg!
The FOR XML PATH clause is available since 2K5.
I tried to explain how it works here . Not sure if it helps to understand though...
Regarding "&": It's the "Bitwise AND". Please see BOL for details.
January 13, 2010 at 1:40 pm
lmu92 (1/13/2010)
@Gus:Just as a side note: AFAIK varchar(max) columns tend to have a bad influence on performance due to the handling of more than one page (8k).
I guess, varchar(8000) will do the job as well and should perform better...
(I remember having a short "discussion" with Jeff a while ago on one thread regarding varchar(max) vs. varchar(8000) within a string split function...)
I'd have to see the tests that were done on performance comparisons between the two. In the tests I've done, and in the documentation I've seen on the MAX-size data types, they go beyond the page into an extended space when they need to, but not before that. It's possible it makes a difference, but I haven't seen one in relatively extensive performance tests.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2010 at 1:43 pm
lmu92 (1/13/2010)
@Gus (again): Another thought:If you'd add
AND (@Sum & power(2, Number)) > 0to your WHERE clause in the Rules CTE you'd get rid of the repetitive Col1=@Col1 part in your resulting @where variable, making it even shorter.
The problem is assigning value 0 to the Col1 test. Start the table at 2 and that goes away.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2010 at 2:04 pm
GSquared (1/13/2010)
I'd have to see the tests that were done on performance comparisons between the two. In the tests I've done, and in the documentation I've seen on the MAX-size data types, they go beyond the page into an extended space when they need to, but not before that. It's possible it makes a difference, but I haven't seen one in relatively extensive performance tests.
´
This link is the post where Jeff did some performance testing between varchar(8000) and varchar(max).
Regarding my other post (related to the WHERE clause in your Rules CTE): You're absolutely right. The code will work for odd numbers as well as long as the table starts with 0, otherwise it won't. My fault. Sorry about that!
January 13, 2010 at 2:11 pm
lmu92 (1/13/2010)
You're very welcome, Greg!The FOR XML PATH clause is available since 2K5.
I tried to explain how it works here . Not sure if it helps to understand though...
Regarding "&": It's the "Bitwise AND". Please see BOL for details.
That is a nifty little tool. The BOL example leaves some room for improvement, but there are scads of stuff found in a google search. Basically this is the meat of what I am needing. I'm still trying to figure out why it works, but it works.
DECLARE @int INT
SELECT @int = 46
SELECT
n,
@int & n
FROM tally t
WHERE n IN (2,4,8,16,32)
AND @int & n = n
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 13, 2010 at 2:24 pm
I'm still trying to figure out why it works, but it works.
Let's see what I can do...
Using your example, 46(base10) can be written as 101110(base2). Agreed?
Let's start with n=2: 2(base10) can be written as 000010(base2). Agreed, too?
The bitwise AND (or "&") does exactly what it says:
___101110
&__000010
___-------
=__000010 (which is 2 in decimal system).
Note: Sorry for the underscores... Required for display purposes...
January 13, 2010 at 8:08 pm
lmu92 (1/13/2010)
I'm still trying to figure out why it works, but it works.
Let's see what I can do...
Using your example, 46(base10) can be written as 101110(base2). Agreed?
Let's start with n=2: 2(base10) can be written as 000010(base2). Agreed, too?
The bitwise AND (or "&") does exactly what it says:
___101110
&__000010
___-------
=__000010 (which is 2 in decimal system).
Note: Sorry for the underscores... Required for display purposes...
I first noticed the powers of two scheme in BOL while looking at the PERMISSIONS function and the associated bitmaps, a couple of years ago. I used it in my Access forms to get user permissions for executing procedures. I guess I never realized that any even number is simply the sum of powers of 2, and when used only once, there is only one possible combination of those powers that makes up every even number. What a perfect and simple system! Now that I "see" it, I can't believe I never saw it before, and it all seems so clear now. I did not fully understand how the code I posted above worked until I put the base2 values on top of each other, like you did.
210
46101110
10
4100
46101110
100
81000
46101110
1000
1610000
46101110
00000
32100000
46101110
100000
Thanks for the help in getting it. My whole way of thinking must change now.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply