Re: SQL question

["Steve Landess" <sjl_abc@xxxxxxxxxxx>]

> "Muralidhar Narayana" wrote:
> Subject: SQL question


> Below is my SQL. I have to do a small modification,
> I have to execute this 'YEUXFILE.PQTY-COALESCE(YODIAC.PQTY,0) '
> only when YEUXFILE.PQTY is positive value.
> (ie. when YEUXFILE.PQTY is greater than or equal to zero,
> I have to do that substraction)
>
> Can some one help me to implement this in my already existing solution. I
tried but could not succeed.

Murali, try this:

SELECT
YEUXFILE.PRECID,
YEUXFILE.CFTC,
 YEUXFILE.PEXCH,
YEUXFILE.PFC,
YEUXFILE.ACCOUNT,
YEUXFILE.PFIRM,
YEUXFILE.PSNAME,

CASE
WHEN YEUXFILE.PQTY > 0
YEUXFILE.PQTY-COALESCE(YODIAC.PQTY,0)
 END
 AS PQTY

 FROM QS36F/YEUXFILE

 LEFT OUTER JOIN

(
SELECT PRECID,ACCOUNT,PFIRM,PFC,SUM(COALESCE(YODIAC.PQTY,  0))
AS PQTY
 FROM
QS36F/YODIAC
GROUP BY PRECID,ACCOUNT,PFIRM,PFC
)
 AS

 YODIAC

 ON
YEUXFILE.ACCOUNT = YODIAC.ACCOUNT