What's wrong with my code?

Question

HI All,

What wrong with my code?

My case is having error.

Anyone can help please?

SELECT
(Case When MM01.MM021_RATE = '1' then ISNULL(PO101_EXT_COST, 0)*MM01.MM021_FOREIGN_RATE,
Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE) AS Amount,
ISNULL(IN100_INV_CODE,'') AS [Item Code],
ISNULL(IN006_DESC,'') AS Major,
ISNULL(IN007_DESC,'') AS Minor,
ISNULL(IN100_DESC,'') AS Item,
ISNULL(PO100_PO_REQ, 0) AS [PO #],
ISNULL(PO100_EVT_ID, 0) AS [Ev ID],
ISNULL(EV200_EVT_DESC, '') AS [Ev Desc], 
ISNULL(PO101_EXT_COST, 0) AS OriginalAmt,
ISNULL(PO101_PHASE, '') AS Phase,
ISNULL(PO101_STATUS, '') AS Status,
ISNULL(EV870_ACCT_SECURITY,'') AS [ARCtl],
ISNULL(PO102_ACCOUNT,'') AS [GL Account],
MM01.MM021_EFFECTIVE_DATE,
MM01.MM021_RATE,
MM01.MM021_FOREIGN_RATE,
(PO101_ORG_CODE) AS ORG,
(PO101_CURRENCY) AS Currency, (PO100_APPROVAL) AS Approval

FROM PO101_ORD_DTL

LEFT OUTER JOIN PO100_ORDERS ON
PO100_ORG_CODE = PO101_ORG_CODE AND
PO100_PO_REQ = PO101_PO_REQ

LEFT JOIN PO102_DISTR ON
PO102_ORG_CODE = PO101_ORG_CODE AND
PO102_PO_REQ = PO101_PO_REQ AND
PO102_PO_REQ_SEQ = PO101_ORD_SEQ

LEFT OUTER JOIN EV200_EVENT_MASTER ON
PO100_ORG_CODE = EV200_ORG_CODE AND
PO100_EVT_ID = EV200_EVT_ID

LEFT OUTER JOIN MM021_CURRENCY_RATES MM01 ON
PO101_CURRENCY = MM01.MM021_CODE

INNER JOIN
(SELECT  MM021_CODE, MAX(MM021_EFFECTIVE_DATE) AS EFFECTIVE_DATE
FROM     MM021_CURRENCY_RATES
WHERE    MM021_FOREIGN_RATE > 0 AND MM021_TO_CODE = '***'
GROUP BY MM021_CODE) MM02 ON
MM01.MM021_CODE = MM02.MM021_CODE AND
MM01.MM021_EFFECTIVE_DATE = MM02.EFFECTIVE_DATE

LEFT OUTER JOIN IN100_INV_MASTER ON
PO101_ITEM = IN100_INV_CODE AND
PO101_ORG_CODE = IN100_ORG_CODE

LEFT OUTER JOIN IN007_MINOR_GROUP ON
IN100_ORG_CODE = IN007_ORG_CODE AND
IN100_MINOR = IN007_MINOR AND
IN100_MAJOR = IN007_MAJOR

LEFT JOIN IN006_MAJOR_GROUP ON
IN006_ORG_CODE = IN007_ORG_CODE AND
IN006_MAJOR = IN007_MAJOR

LEFT JOIN EV870_ACCT_MASTER ON
PO101_BILLTO=EV870_ACCT_CODE AND
PO101_ORG_CODE=EV870_ORG_CODE

WHERE
(PO101_ORG_CODE = '40' ) AND
(EV200_EVT_DESC = 'Tyrexpo India 2016') AND
(ISNULL(PO100_APPROVAL,'') IN ('A9')) AND
(ISNULL(PO101_STATUS, '') IN ('O','C','H')) AND
(ISNULL(PO101_PHASE, '')  IN ('1')) AND
(PO101_TAX_RES_TYPE <> '8APGST')

Answer 1

Hi Yewee,

You may write CASE expression as follows to comply with the syntax.

Case 
	When MM01.MM021_RATE = '1' then ISNULL(PO101_EXT_COST, 0)*MM01.MM021_FOREIGN_RATE
	Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE
End AS Amount,

-- OR
ISNULL(PO101_EXT_COST, 0) * Case 
								When MM01.MM021_RATE = '1' then MM01.MM021_FOREIGN_RATE
								Else MM01.MM021_RATE
							End AS Amount,

-- OR 
ISNULL(PO101_EXT_COST, 0) * Case MM01.MM021_RATE
								When '1' then MM01.MM021_FOREIGN_RATE
								Else MM01.MM021_RATE
							End AS Amount,

---

Sam Zha
TechNet Community Support

Answer 2

I think you miss 'end' to your case clause

...(Case When MM01.MM021_RATE = '1' then ISNULL(PO101_EXT_COST, 0)*MM01.MM021_FOREIGN_RATE,
Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE end) AS Amount,...

---

Many Thanks & Best Regards, Hua Min

Answer 3

Hi Sam,

I changed my code to the below but then it give me an error "Incorrect Column Expression".

What wrong with my code?

SELECT
(Case
 When MM01.MM021_RATE = '1' then ISNULL(PO101_EXT_COST, 0)/MM01.MM021_FOREIGN_RATE
 Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE
End )AS Amount,
ISNULL(IN100_INV_CODE,'') AS [Item Code],
ISNULL(IN006_DESC,'') AS Major,
ISNULL(IN007_DESC,'') AS Minor,
ISNULL(IN100_DESC,'') AS Item,
ISNULL(PO100_PO_REQ, 0) AS [PO #],
ISNULL(PO100_EVT_ID, 0) AS [Ev ID],
ISNULL(EV200_EVT_DESC, '') AS [Ev Desc], 
ISNULL(PO101_EXT_COST, 0) AS OriginalAmt,
ISNULL(PO101_PHASE, '') AS Phase,
ISNULL(PO101_STATUS, '') AS Status,
ISNULL(EV870_ACCT_SECURITY,'') AS [ARCtl],
ISNULL(PO102_ACCOUNT,'') AS [GL Account],
MM01.MM021_EFFECTIVE_DATE,
MM01.MM021_RATE,
MM01.MM021_FOREIGN_RATE,
(PO101_ORG_CODE) AS ORG,
(PO101_CURRENCY) AS Currency, (PO100_APPROVAL) AS Approval

FROM PO101_ORD_DTL

LEFT OUTER JOIN PO100_ORDERS ON
PO100_ORG_CODE = PO101_ORG_CODE AND
PO100_PO_REQ = PO101_PO_REQ

LEFT JOIN PO102_DISTR ON
PO102_ORG_CODE = PO101_ORG_CODE AND
PO102_PO_REQ = PO101_PO_REQ AND
PO102_PO_REQ_SEQ = PO101_ORD_SEQ

LEFT OUTER JOIN EV200_EVENT_MASTER ON
PO100_ORG_CODE = EV200_ORG_CODE AND
PO100_EVT_ID = EV200_EVT_ID

LEFT OUTER JOIN MM021_CURRENCY_RATES MM01 ON
PO101_CURRENCY = MM01.MM021_CODE

INNER JOIN
(SELECT  MM021_CODE, MAX(MM021_EFFECTIVE_DATE) AS EFFECTIVE_DATE
FROM     MM021_CURRENCY_RATES
WHERE    MM021_FOREIGN_RATE > 0 AND MM021_TO_CODE = '***'
GROUP BY MM021_CODE) MM02 ON
MM01.MM021_CODE = MM02.MM021_CODE AND
MM01.MM021_EFFECTIVE_DATE = MM02.EFFECTIVE_DATE

LEFT OUTER JOIN IN100_INV_MASTER ON
PO101_ITEM = IN100_INV_CODE AND
PO101_ORG_CODE = IN100_ORG_CODE

LEFT OUTER JOIN IN007_MINOR_GROUP ON
IN100_ORG_CODE = IN007_ORG_CODE AND
IN100_MINOR = IN007_MINOR AND
IN100_MAJOR = IN007_MAJOR

LEFT JOIN IN006_MAJOR_GROUP ON
IN006_ORG_CODE = IN007_ORG_CODE AND
IN006_MAJOR = IN007_MAJOR

LEFT JOIN EV870_ACCT_MASTER ON
PO101_BILLTO=EV870_ACCT_CODE AND
PO101_ORG_CODE=EV870_ORG_CODE

WHERE
(PO101_ORG_CODE = ?) AND
(EV200_EVT_DESC = ?) AND
(ISNULL(PO100_APPROVAL,'') IN ('A9')) AND
(ISNULL(PO101_STATUS, '') IN ('O','C','H')) AND
(ISNULL(PO101_PHASE, '')  IN ('1')) AND
(PO101_TAX_RES_TYPE <> '8APGST')

Answer 4

Where do you run this?

When I ran your query in SSMS, I first got an error for the two question marks that appear in the WHERE clause. I assume that these are parameter markers, so that is alright. I fixed that by replacing them by constants. I now got an error about missing table, which tells me that the syntax is correct.

Incorrect column expression is not an SQL error that I recognise, but it may come from your environment.

Answer 5

Hi Yewee,

It seems that you are working with Access. Use IIF instead of CASE, as well as ISNULL.

IIF(MM01.MM021_RATE = '1', IIF(ISNULL(PO101_EXT_COST), 0, PO101_EXT_COST)/MM01.MM021_FOREIGN_RATE, IIF(ISNULL(PO101_EXT_COST), 0, PO101_EXT_COST)*MM01.MM021_RATE) AS Amount,
IIF(ISNULL(IN100_INV_CODE), '', IN100_INV_CODE) AS [Item Code],

---

Sam Zha
TechNet Community Support

Answer 6

Hi All,

I am logging in from Excel worksheet. In Excel worksheet, there is a DATA tab.

Hi Sam, I tried your code but it give me "Incorrect Synatx Near '=' error.

Answer 7

You don't need () in this expression and in some other cases, although I don't think it will give you an error

(Case When MM01.MM021_RATE = '1' then ISNULL(PO101_EXT_COST, 0)*MM01.MM021_FOREIGN_RATE,
Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE) AS Amount, 

Also, if your MM021_RATE column of the character type? If not, I would not use '1', I'd use 1 instead.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 8

HI All,

Thanks.

I will need to add one more Case rule.

If MM01.MM021_RATE = '1' AND MM01.MM021_FOREIGN_RATE <> '1' then (ISNULL(PO101_EXT_COST, 0)/MM01.MM021_FOREIGN_RATE)/2

If MM01.MM021_RATE <> '1' AND MM01.MM021_FOREIGN_RATE = '1' Else ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE

Else ISNULL(PO101_EXT_COST, 0)

How can i do that?

Answer 9

You can embed this into the ELSE clause if need be, or attempt to add another line as you have currently done.

CASE WHEN (MM01.MM021_RATE = '1' AND MM01.MM021_FOREIGN_RATE <> '1') THEN (ISNULL(PO101_EXT_COST, 0)/MM01.MM021_FOREIGN_RATE)/2
ELSE 
	CASE WHEN (MM01.MM021_RATE <> '1' AND MM01.MM021_FOREIGN_RATE = '1') THEN ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE
	ELSE ISNULL(PO101_EXT_COST, 0) END
END AS [Column_Name]

Note that your statements get evaluated in the ORDER IN WHICH THEY ARE WRITTEN, and that you can nest as many as 10 levels of CASE expressions in your code. MDSN (Link)

Personally, I prefer to type this out on another document (like Notepad since you appear to not have access to SSMS?) and just paste it in, so I can clearly see how I wrote my code.

After all, Syntax is the last thing you really want to be fighting, in your quest to learn.

Answer 10

Hi Yewee,

Simply add another when_expression.

CASE
	WHEN MM01.MM021_RATE = '1' AND MM01.MM021_FOREIGN_RATE <> '1' THEN (ISNULL(PO101_EXT_COST, 0)/MM01.MM021_FOREIGN_RATE)/2
	WHEN MM01.MM021_RATE <> '1' AND MM01.MM021_FOREIGN_RATE = '1' THEN ISNULL(PO101_EXT_COST, 0)*MM01.MM021_RATE
	ELSE ISNULL(PO101_EXT_COST, 0)
END

---

Sam Zha
TechNet Community Support