July 3, 2006 at 12:11 pm
Right now, if a table has permission-ish bit fields with the possible values 0, 1 and null which may be overridden on following rows, I have to go through this whole rigamarole to figure out the current permissions:
SELECT
MyGroupfield,
-- DENY setting (-2) overrides an ALLOW setting (-1) which overrides a NOCHANGE setting (0)
PermissionField1 = CASE MIN( CASE WHEN isnull(convert(smallint, PermissionField1), 2) = 0 THEN -2 WHEN isnull(convert(smallint, PermissionField1), 2) = 1 THEN -1 ELSE 0 END)
WHEN -2 THEN 0
WHEN -1 THEN 1
WHEN 0 THEN Null
END,
PermissionField2 = CASE MIN( CASE WHEN isnull(convert(smallint, PermissionField2), 2) = 0 THEN -2 WHEN isnull(convert(smallint, PermissionField2), 2) = 1 THEN -1 ELSE 0 END)
WHEN -2 THEN 0
WHEN -1 THEN 1
WHEN 0 THEN Null
END
FROM MyTable
WHERE MyGroupField is not null
GROUP BY MyGroupField
This, as you can see, can get quite messy and can end up requiring a separate view for each potential group field. Is there an existing aggregate for situations like this or a more elegant solution than the transform/calculate/restore approach I am using?
July 3, 2006 at 1:11 pm
Looks like you can dodge all the CASE statements With a NullIf(value, 2):
NullIf(Min(IsNull(CONVERT(smallint, PermissionField1), 2)), 2) AS NewPermissionField1,
Testing (in results,PermissionField1 should equal NewPermissionField1, PermissionField2 should equal PermissionField2):
DECLARE @MyTable table(GrpID int, PermissionField1 bit NULL, PermissionField2 bit NULL)
INSERT @MyTable(GrpID, PermissionField1, PermissionField2)
SELECT 1, NULL, NULL UNION SELECT 1, 0, NULL UNION
SELECT 2, 1, 1 UNION SELECT 2, 0, 0 UNION
SELECT 3, 1, 0 UNION SELECT 3, 1, 0 UNION
SELECT 4, 0, 1 UNION SELECT 4, NULL, NULL
SELECT GrpID,
-- DENY setting (-2) overrides an ALLOW setting (-1) which overrides a NOCHANGE setting (0)
NullIf(Min(IsNull(CONVERT(smallint, PermissionField1), 2)), 2) AS NewPermissionField1,
NullIf(Min(IsNull(CONVERT(smallint, PermissionField2), 2)), 2) AS NewPermissionField2,
PermissionField1 = CASE MIN(
CASE WHEN isnull(convert(smallint, PermissionField1), 2) = 0 THEN -2
WHEN isnull(convert(smallint, PermissionField1), 2) = 1 THEN -1
ELSE 0
END)
WHEN -2 THEN 0
WHEN -1 THEN 1
WHEN 0 THEN Null
END,
PermissionField2 = CASE MIN( CASE WHEN isnull(convert(smallint, PermissionField2), 2) = 0 THEN -2 WHEN isnull(convert(smallint, PermissionField2), 2) = 1 THEN -1 ELSE 0 END)
WHEN -2 THEN 0
WHEN -1 THEN 1
WHEN 0 THEN Null
END
FROM @MyTable
WHERE GrpID is not null
GROUP BY GrpID
Not sure this is the best route to take, but without seeing your data, DDL, and usage, at least this trims some fat off of the query.
-Eddie
Eddie Wuerch
MCM: SQL
July 3, 2006 at 3:21 pm
Thanks. That's a lot cleaner.
I must have latched onto the first idea that made sense and coded it into submission.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply