none
or in select

    Question

  • hi!!

    how can I do this:

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

    Thursday, March 15, 2012 12:47 PM

Answers

  • 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).

    Thursday, March 15, 2012 1:53 PM

All replies

  • Perhaps this:

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


    Thursday, March 15, 2012 12:51 PM
    Moderator
  • 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:55 PM
  • 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:56 PM
    Moderator
  • 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 12:58 PM
  • SELECT    1 AS canDoSomething, *
    FROM Users
    WHERE    canEditComplaint = 1 OR canEditUsers = 1 OR CanSeeAllComplaints = 1
        

    Thursday, March 15, 2012 1:45 PM
  • 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).

    Thursday, March 15, 2012 1:53 PM