SQL Select column based on variable condition

Question

I'm trying to do this, in two different ways:

SELECT * 
FROM tbl_Parts
WHERE Column_ID = 2807
AND part_author = (case part_private = 'Y' THEN 'domain\username' ELSE part_author END)

And also this way:

SELECT *
FROM tbl_parts
WHERE Column_ID = 2807
  and part_author = IIF(part_private = 'Y','domain\username',part_author)

Ultimately, I'm trying to check each row as I select it to see if it is marked private. If it is, then only select that row if the user logged into the application is the part author. If part_private is 'N', then select it for everyone.

Hope that makes enough sense for someone to see what I'm trying to do.

I don't know how to ask it any better than I did. I'm hoping someone can look at it and "see" what it is I'm trying to do. In the CASE version, it seems that CASE cannot accept checking a column for a value. It wants just a value to evaluate. In the IIF() version, I'm not even sure what is wrong. It looks syntactically correct, but it complains that the "IIF Function requires three arguments", which I say it has. — KWallace
– KWallace, Commented Jul 14, 2015 at 19:01
To form it in a question: I need a query that returns all rows, except if part_private='Y'. If part_private='Y' then I only want to return that row if the user is the author of that part (ie, part_author=[%=logged-in-user-name%]) User name being supplied as a variable in the code that is creating the query. — KWallace
– KWallace, Commented Jul 14, 2015 at 19:06

Greg Viers · Accepted Answer · 2015-07-14 18:31:44Z

1

OR is what you need here. There are two types of records to select. Those which are not private, adn those where the author matches.

SELECT * 
FROM tbl_Parts
WHERE Column_ID = 2807
AND (part_private='N' 
OR 'domain\username'=part_author)

answered Jul 14, 2015 at 18:31

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KWallace Over a year ago

Holy cow. That totally worked. I hadn't even thought of approaching it like that. Very simple, direct and to the point. Thanks.

Collectives™ on Stack Overflow

SQL Select column based on variable condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related