Conditional logic in a view

Question

Post reply

Conditional logic in a view

Robb.Hagan

Right there with Babe

Points: 772
More actions
September 20, 2008 at 10:45 am

#212899

I am looking for a way to augment a view that will control/limit the data a certain group of users can see but allow all data through for everyone else.
Background
We have implemented a view in our system that controls data from a table that is presented to the user. It filters out records that have been stamped as "deleted".
I am being asked to provide a way to further limit the data a certain group can see based on a field in the table the view accesses. The view needs to conditionally evaluate this field if the user is a member of this group.
I am attempting to use the IS_MEMBER() function to determine the user's access but I am not sure how to factor in the additional WHERE clause criteria.
SELECT *
FROM table
WHERE deletedate IS NULL
AND CASE WHEN IS_MEMBER('role') = 1 THEN ????
Is this possible?
Thanks.
Robb

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Case

When IS_MEMBER('role') = 1 Then 1 -- is a member

Else 0 -- not a member

End = 1

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jack Corbett (9/22/2008)
Case When IS_MEMBER('role') = 1 Then 1 -- is a member Else 0 -- not a member End = 1

Also known as....

IS_Member('role')=1

The case is not useful in this case (since those are the values already returned by IS_Member()).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Yeah Matt, I thought that as well, but also thought the OP may not have posted all the logic needed so I tried to give an idea of the Case Logic needed.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 4

Thanks for the replies.

I am not sure I gave enough info or was misleading in my example.

My solution at this point is this:

SELECT *

FROM table

WHERE deletedate IS NULL

AND IS_Member('role') = 0

OR ID IN (SELECT ID FROM table WHERE field = 1 and deletedate IS NULL)

This will return all records for non-members of the role or only the records based on the subquery for members.

Robb

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Surely this is all you need?

[font="Courier New"]SELECT t.*

FROM TABLE t

WHERE (IS_MEMBER('role') = 0 AND t.deletedate IS NULL) OR IS_MEMBER('role') = 1

--OR ID IN (SELECT ID FROM table WHERE field = 1 and deletedate IS NULL)

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 6

ChrisM-

The original purpose of this view was to exclude records stamped as delete - deletedate IS NULL.

So these records need to be exlcuded no matter which role the user is in.

Thanks.

Robb

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jack Corbett (9/22/2008)
Yeah Matt, I thought that as well, but also thought the OP may not have posted all the logic needed so I tried to give an idea of the Case Logic needed.

Ahh - gotcha. Fair enough.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

rhagan (9/22/2008)
ChrisM-
The original purpose of this view was to exclude records stamped as delete - deletedate IS NULL.
So these records need to be exlcuded no matter which role the user is in.
Thanks.
Robb

Sorry Rob, my mistake. So you mean...

[font="Courier New"]SELECT t.*

FROM TABLE t

WHERE t.deletedate IS NULL

AND ((IS_MEMBER('role') = 0

OR t.ID IN (SELECT x.ID FROM TABLE x WHERE x.field = 1 AND x.deletedate IS NULL))

[/font]

Or are TABLE and TABLE the same table?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 9

ChrisM-

The same table as the view references contains the field that will determine the what the user can see.

Example:

SELECT t.*

FROM TABLE t

WHERE t.deletedate IS NULL

AND ((IS_MEMBER('role') = 0

OR t.ID IN (SELECT t.ID FROM TABLE t WHERE t.field = 1 AND t.deletedate IS NULL))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

rhagan (9/22/2008)
ChrisM-
The same table as the view references contains the field that will determine the what the user can see.
Example:
SELECT t.*
FROM TABLE t
WHERE t.deletedate IS NULL
AND ((IS_MEMBER('role') = 0
OR t.ID IN (SELECT t.ID FROM TABLE t WHERE t.field = 1 AND t.deletedate IS NULL))

Now I've gotcha...you mean

[font="Courier New"]SELECT t.*

FROM TABLE t

WHERE t.deletedate IS NULL

AND (IS_MEMBER('role' = 0 OR t.field = 1)

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Robb.Hagan Right there with Babe Points: 772 More actions · Answer 11

Robb.Hagan

Right there with Babe

Points: 772

September 22, 2008 at 10:18 am

#875129

ChrisM-

Bingo! That works.

Much cleaner.

Thanks.

Robb

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Thanks for the feedback Robb 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden