July 20, 2009 at 12:09 pm
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!
July 20, 2009 at 12:18 pm
What are you trying to accomplish with the CASE statement?
July 20, 2009 at 12:21 pm
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
July 20, 2009 at 12:22 pm
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.
July 20, 2009 at 12:25 pm
I think you should look at Lowell's first code block. That looks more like what you want.
July 20, 2009 at 12:27 pm
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!
July 20, 2009 at 12:28 pm
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.
July 20, 2009 at 12:30 pm
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'."
July 20, 2009 at 12:33 pm
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.
July 20, 2009 at 12:36 pm
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!
July 21, 2009 at 1:00 pm
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