Post reply

select boolean resultvalue based on column value

  • September 14, 2010 at 11:14 am

    #224058

    I have a table which has a column thumbid of type int

    I want to select if the column thumbid holds a value larger than 0.

    If the column value is larger than 1, result value should be 'true', if its 0 it should be 'false'.

    I tried this:

    select (thumbid=0) as resultvalue FROM locations

    but that doesnt work...

  • September 14, 2010 at 11:36 am

    #1220872

    you'll want to use a case statement:

    select

    CASE

    WHEN thumbid=0

    THEN 'false'

    ELSE 'true'

    END as resultvalue ,

    CASE

    WHEN thumbid=0

    THEN 0

    ELSE 1

    END as resultvalueAsInt

    FROM locations

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 14, 2010 at 12:09 pm

    #1220897

    thanks!

  • September 14, 2010 at 1:16 pm

    #1220920

    petervdkerk (9/14/2010)

    I have a table which has a column thumbid of type int

    I want to select if the column thumbid holds a value larger than 0.

    If the column value is larger than 1, result value should be 'true', if its 0 it should be 'false'.

    I tried this:

    select (thumbid=0) as resultvalue FROM locations

    but that doesnt work...

    If thumbid is a numeric datatype, and you just want to know if it has a value > 0, then you can simply do:

    DECLARE @test-2 table (c int);

    INSERT INTO @test-2

    SELECT 0 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4;

    SELECT c,

    IsGreaterThanZero = CONVERT(bit, c)

    FROM @test-2;

    Values greater than zero are converted to a bit value of one.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 4 posts - 1 through 3 (of 3 total)

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