Post reply

Sql Query Help

  • November 13, 2013 at 2:38 pm

    #280197

    I have query where i am using case statement and having issues. A person could select two options and if they select two options i do not want both output instead just THEN 'TWO ITEMS SELECTED' and when only only selection picked then output the corresponding result.

    SELECT ID,

    LAST_NAME,

    FIRST_NAME,

    CASE WHEN EXISTS (SELECT count(SELECTION) FROM ITEMS) >= 2 THEN 'Two Items selected'

    WHEN SELECTON = -1 THEN 'A'

    WHEN SELECTON = -2 THEN 'B'

    WHEN SELECTON = -3 THEN 'C'

    WHEN SELECTON = -4 THEN 'D'

    WHEN SELECTON = -5 THEN 'E'

    END AS item_selected

    output would be like:

    last_name first_name item_selected

    ------------ ------------- ----------------------

    doe john Two Items selected

    Not like:

    last_name first_name item_selected

    ------------ ------------- ------------------

    doe john a

    doe john b

  • November 13, 2013 at 2:56 pm

    #1666221

    There's something wrong with your query. You're comparing a boolean value (EXISTS()) against an integer value.

    To get better help, please post DDL for tables involved and sample data in the way of INSERT INTO statements. For information on how to do this, check the article linked in my signature.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 13, 2013 at 4:17 pm

    #1666238

    Something like below. If the LAST_NAME and FIRST_NAME are in the same table (shouldn't be, from a design standpoint), then you can remove the outer query and join and uncomment the LAST_NAME and FIRST_NAME references in the inner query:

    SELECT

    i_sel.ID, n.LAST_NAME, n.FIRST_NAME, i_sel.item_selected

    FROM (

    SELECT i.ID,

    --LAST_NAME,

    --FIRST_NAME,

    CASE WHEN COUNT(i.SELECTION) >= 2 THEN 'Two Items selected'

    WHEN MAX(i.SELECTION) = -1 THEN 'A'

    WHEN MAX(i.SELECTION) = -2 THEN 'B'

    WHEN MAX(i.SELECTION) = -3 THEN 'C'

    WHEN MAX(i.SELECTION) = -4 THEN 'D'

    WHEN MAX(i.SELECTION) = -5 THEN 'E'

    END AS item_selected

    FROM ITEMS i

    GROUP BY i.ID --, LAST_NAME, FIRST_NAME

    ) AS i_sel

    INNER JOIN NAMES n ON

    n.ID = i.ID

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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