Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
or in select

Answered or in select

  • Thursday, March 15, 2012 12:47 PM
     
     
    Sign In to Vote
    0

    hi!!

    how can I do this:

        SELECT ( canEditComplaint or canEditUsers or CanSeeAllComplaints)     AS canDoSomething, *
                    FROM Users

     

All Replies

  • Thursday, March 15, 2012 12:51 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1
     
    Perhaps this:
    SELECT CASE WHEN canEditComplaint or canEditUsers or CanSeeAllComplaints THEN 1 ELSE 0 END     AS canDoSomething, *
                FROM Users

     
  • Thursday, March 15, 2012 12:55 PM
     
     
    Sign In to Vote
    0

    nope

    An expression of non-boolean type specified in a context where a condition is expected, near 'or'.

    funny,an '&' works instead of or

     
  • Thursday, March 15, 2012 12:56 PM
    Moderator
     
      Has Code
    Sign In to Vote
    1

    According to your data types following Coalesce function can also help

    SELECT coalesce(canEditComplaint, canEditUsers, CanSeeAllComplaints) AS canDoSomething, * FROM xUsers

    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

     
  • Thursday, March 15, 2012 12:58 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    If this is a bit or integer data type then this should work:

    SELECT
	CASE
		WHEN canEditComplaint = 1 OR canEditUsers = 1 OR CanSeeAllComplaints = 1 THEN 1
		ELSE 0
	END AS canDoSomething, *
FROM
	Users

    - Krishnakumar S
     
  • Thursday, March 15, 2012 1:45 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    1
     
    SELECT    1 AS canDoSomething, *
    FROM Users
    WHERE    canEditComplaint = 1 OR canEditUsers = 1 OR CanSeeAllComplaints = 1
        
     
  • Thursday, March 15, 2012 1:53 PM
     
     Answered
    Sign In to Vote
    2

    This is because & is the bitwise and operator in T-SQL.  If you wanted to do bitwise or, you could use operator | instead.  As long as these columns are BIT data types, this will work as expected:

    SELECT ( canEditComplaint | canEditUsers | CanSeeAllComplaints)     AS canDoSomething, *

    FROM Users.

    If you forsee needing this in more than one query, I suggest making this a computed column in the Users table, like so:
    ALTER TABLE Users ADD canDoSomething AS ( canEditComplaint | canEditUsers | CanSeeAllComplaints).