Post reply

Translation to valid TSQL

  • August 7, 2018 at 3:26 am

    #356974

    I've been trying for the past hour to nest a case in another case.

    How can I say this exactly?

    CASE WHEN CONFIG.PremiumSignShowData>0

    THEN

    CASE SIGN (ISNULL(resultPremium,0)) WHEN '-1' THEN '-' ELSE '+'

    ELSE ' '

    END AS PremiumSign

    Thanks in advance

  • August 7, 2018 at 3:30 am

    #2000554

    You need another END for the inner case:


    SELECT
        CASE WHEN CONFIG.PremiumSignShowData>0
        THEN CASE SIGN (ISNULL(resultPremium,0))
            WHEN '-1' THEN '-'
            ELSE '+'
        END
        ELSE ' '
    END AS PremiumSign

    Adi

  • August 7, 2018 at 3:31 am

    #2000555

    Hi,

    You are missing the END in line no 3 at the end. It should be like:

    SELECT CASE WHEN CONFIG.PremiumSignShowData>0
             THEN
                      CASE SIGN (ISNULL(resultPremium,0)) WHEN '-1' THEN '-' ELSE '+' END
                     ELSE ' '
                END AS PremiumSign

  • August 7, 2018 at 3:39 am

    #2000557

    Done thanks guys.

  • August 7, 2018 at 6:18 am

    #2000570

    Just an FYI, but that code introduces an unnecessary implicit conversion by using a string to represent negative 1.   Here's the code fully indented and just removing the single quotes around -1:
    SELECT
        CASE
            WHEN CONFIG.PremiumSignShowData > 0 THEN
                CASE SIGN (ISNULL(resultPremium, 0))
                    WHEN -1 THEN '-'
                    ELSE '+'
                END
            ELSE ' '
        END AS PremiumSign

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • August 7, 2018 at 7:34 am

    #2000590

    You can actually simplify this.  There is no reason for the nested CASE expressions and you can greatly simplify the sign.

    I'm assuming that CONFIG.PremiumSignShowData is both a BIT field and not nullable.  If that's not the case, you should have a very good reason why.  The rewrite will still work, but it will be a bit more complicated.

    The conditions for showing ' ' are the least complicated, so test that first.  Also, there is no reason to handle NULL values if they are just going to fall into the ELSE condition, because they are going to fall into the ELSE condition anyhow.

    SELECT
    CASE
            WHEN CONFIG.PremiumSignShowData = 0 THEN '  '
            WHEN resultPremium < 0 THEN '-'
            ELSE '+'
    END AS PremiumSign

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 8, 2018 at 8:01 am

    #2000804

    and the moral of this story is to indent your code properly 🙂  If you had done that you would have quickly spotted the missing END

Viewing 7 posts - 1 through 6 (of 6 total)

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