SQL2005 - DB2 Linked server: Openquery - Case Syntax

[fibonaccii]

I am able to run the statement below

SELECT * from openquery (Linkedname,'Select
RPDOC AS InvoiceNumber,
RPPST AS InvoiceStatus,
SUM(RPAG) AS GrossAmount
FROM F03B11
GROUP BY RPPST, RPDOC')

But When I use the CASE ststement to filter the data further I get an eror message.

SELECT * from openquery (Linkedname,'Select
RPDOC AS InvoiceNumber,
RPPST AS InvoiceStatus,
SUM(RPAG) AS GrossAmount,
OpenAmount = Case
WHEN RPPST='A' Then SUM(RPAAP)
Else 0
End
FROM F03B11
GROUP BY RPPST, RPDOC')


Ques: Can i use CASE statament in openquery. If yes, what can I do to fix it. Pretty sure it is a syntax issue.

Any help is appreciated. Thnx!

 

[RiverGuy]

OpenAmount should be something like

OpenAmount = SUM(
CASE
  WHEN RPPST = 'A' THEN RPAAP
  ELSE 0
END)

Put the "SUM()" outside of the CASE statement.

 

[fibonaccii]

Thnx Guy,

Instead 1st, I created local tables using opequery and then created the view with the case statement in SQL.