March 16, 2009 at 9:50 am
EvilPostIT (3/16/2009)
Success. See what you think of this.
...(
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid-(8*((sc.colid-1)/8)))))>0 and sc.colid<8
OR
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid+1-(8*((sc.colid)/8))))/2)>0 and sc.colid>=8
)
Well since power(2, X+1)/2 is the same as power(2, X) then you don't need the two OR branches as they both do the same thing.
Further, since X-(8*(X/8)) is the same thing as X % 8 then you should be able to change this entire clause to:
(substring(sp.columns,((sc.colid-1)/8)+1,1) & power(2,((sc.colid-1) % 8) ) )>0
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2009 at 10:40 am
These OR branches are doing slightly different things and therefore i dont think i can do this using 1 statement.
This branch is bringing back the first byte from the columns field (as shown by the "sc.colid<8" ie bit 1: the "ALL" permission and bit 2-8: colid 1-7) and then just linking based upon the ordinal position -1, thus off-setting the ordinal position to make up for the fact the first bit is not the first column but actually the "ALL" permission.
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid-(8*((sc.colid-1)/8)))))>0 and sc.colid<8
Whereas this branch effectivly resets the number of the colid so that no offset is required and can be a true match for the rest of the bytes going forward. ("sc.colid>=8" ie bit 1-8: colid 1-8)
(substring(sp.columns,((sc.colid)/8)+1,1) & power(2,(sc.colid+1-(8*((sc.colid)/8))))/2)>0 and sc.colid>=8
Plus i am changing the colid column so that it is the ordinal position within each byte that has been cut out with the substring. (Always 1-8)
By doing it this way i get round the power arithmatic overflow issue.
Let me know what you think.
March 16, 2009 at 11:05 am
Ah, OK. I did not know about the "All" thing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply