CASE Statement NOT working...

  • This forum has been a tremendous help, which is why I love coming here. Now, I know this is a simple query, but for some reason, my CASE STATEMENT is not working here:

    SELECT * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    --CASE

    --WHEN ITM.MNR_CD = '100' THEN '15' AND

    --WHEN ITM.MNR_CD = '105' THEN '10' AND

    --WHEN ITM.MNR_CD = '110' THEN '5' AND

    --WHEN ITM.MNR_CD = '111' THEN '5' AND

    --WHEN ITM.MNR_CD = '115' THEN '5' AND

    --WHEN ITM.MNR_CD = '125' THEN '25'

    --ELSE '0'

    --END AS SPIFF_BONUS

    I commented out the case statement here... What am I missing?

    Thank you!

  • What are you trying to accomplish with the CASE statement?

  • donato, are you trying to usee a CASE statement to return a desired value as a column, or as a WHERE condition?

    SELECT

    CASE

    WHEN ITM.MNR_CD = '100' THEN '15' AND

    WHEN ITM.MNR_CD = '105' THEN '10' AND

    WHEN ITM.MNR_CD = '110' THEN '5' AND

    WHEN ITM.MNR_CD = '111' THEN '5' AND

    WHEN ITM.MNR_CD = '115' THEN '5' AND

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END AS SPIFF_BONUS,

    * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    or

    SELECT

    * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    AND CASE

    WHEN ITM.MNR_CD = '100' THEN '15' AND

    WHEN ITM.MNR_CD = '105' THEN '10' AND

    WHEN ITM.MNR_CD = '110' THEN '5' AND

    WHEN ITM.MNR_CD = '111' THEN '5' AND

    WHEN ITM.MNR_CD = '115' THEN '5' AND

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END = @MyParameter,

    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!

  • donato1026 (7/20/2009)


    This forum has been a tremendous help, which is why I love coming here. Now, I know this is a simple query, but for some reason, my CASE STATEMENT is not working here:

    SELECT * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    --CASE

    --WHEN ITM.MNR_CD = '100' THEN '15' AND

    --WHEN ITM.MNR_CD = '105' THEN '10' AND

    --WHEN ITM.MNR_CD = '110' THEN '5' AND

    --WHEN ITM.MNR_CD = '111' THEN '5' AND

    --WHEN ITM.MNR_CD = '115' THEN '5' AND

    --WHEN ITM.MNR_CD = '125' THEN '25'

    --ELSE '0'

    --END AS SPIFF_BONUS

    I commented out the case statement here... What am I missing?

    Thank you!

    I need to assign that particular value to each MNR_CD. Then in my report, I will sum the SPIFF_BONUS per EMP_CD which is the employee code. I'm just trying to sum their bonus amount. So, 100 = $10 and so on...

    Thank you.

  • I think you should look at Lowell's first code block. That looks more like what you want.

  • Lynn Pettis (7/20/2009)


    What are you trying to accomplish with the CASE statement?

    I'm trying to return a desired value as a column... In this case, as SPIFF_BONUS. So, whatever MNR_CD = 100 would return 10 and so on... But I want it as a column so I can use that data later in reporting services.

    Thank you very much!

  • Lynn Pettis (7/20/2009)


    I think you should look at Lowell's first code block. That looks more like what you want.

    Well, although it is much appreciated, neither of those blocks work. It seems there is a syntax error some where, which I don't understand... it looks right to me.

  • SELECT

    CASE

    WHEN ITM.MNR_CD = '100' THEN '15' AND

    WHEN ITM.MNR_CD = '105' THEN '10' AND

    WHEN ITM.MNR_CD = '110' THEN '5' AND

    WHEN ITM.MNR_CD = '111' THEN '5' AND

    WHEN ITM.MNR_CD = '115' THEN '5' AND

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END AS SPIFF_BONUS,

    * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    This is returning the error:

    "Incorrect syntax near the keyword 'AND'."

  • donato1026 (7/20/2009)


    SELECT

    CASE

    WHEN ITM.MNR_CD = '100' THEN '15' AND

    WHEN ITM.MNR_CD = '105' THEN '10' AND

    WHEN ITM.MNR_CD = '110' THEN '5' AND

    WHEN ITM.MNR_CD = '111' THEN '5' AND

    WHEN ITM.MNR_CD = '115' THEN '5' AND

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END AS SPIFF_BONUS,

    * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    This is returning the error:

    "Incorrect syntax near the keyword 'AND'."

    Take the AND's out of the CASE statement.

  • Lynn Pettis (7/20/2009)


    donato1026 (7/20/2009)


    SELECT

    CASE

    WHEN ITM.MNR_CD = '100' THEN '15' AND

    WHEN ITM.MNR_CD = '105' THEN '10' AND

    WHEN ITM.MNR_CD = '110' THEN '5' AND

    WHEN ITM.MNR_CD = '111' THEN '5' AND

    WHEN ITM.MNR_CD = '115' THEN '5' AND

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END AS SPIFF_BONUS,

    * FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

    This is returning the error:

    "Incorrect syntax near the keyword 'AND'."

    Take the AND's out of the CASE statement.

    HA! I knew it was something silly like that! Thank you all!

  • Remove the "AND" and you all set if you need the "case" in select

    SELECT

    CASE

    WHEN ITM.MNR_CD = '100' THEN '15'

    WHEN ITM.MNR_CD = '105' THEN '10'

    WHEN ITM.MNR_CD = '110' THEN '5'

    WHEN ITM.MNR_CD = '111' THEN '5'

    WHEN ITM.MNR_CD = '115' THEN '5'

    WHEN ITM.MNR_CD = '125' THEN '25'

    ELSE '0'

    END AS SPIFF_BONUS

    *

    FROM SO_LN

    INNER JOIN ITM ON SO_LN.ITM_CD = ITM.ITM_CD

    INNER JOIN SO ON SO_LN.DEL_DOC_NUM = SO.DEL_DOC_NUM

    INNER JOIN EMP ON SO.SO_EMP_SLSP_CD1 = EMP.EMP_CD

    WHERE ITM.MNR_CD IN ('100','105','110','111','115','125')

    AND SO.SO_WR_DT BETWEEN '07-01-09' AND '07-31-09'

    AND VOID_FLAG IS NOT NULL

Viewing 11 posts - 1 through 10 (of 10 total)

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