RE: SQL question

[Chris Payne <CPayne@xxxxxxxxxxxxxxx>]

If you want to return YEUXFILE.PQTY - COALESCE(YODIAC.PQTY,0) when
YEUXFILE.PQTY >= 0 and YEUXFILE.PQTY when YEUXFILE.PQTY < 0 you can use the
case statement.

SELECT 
 YEUXFILE.PRECID,
 YEUXFILE.CFTC, 
 YEUXFILE.PEXCH,
 YEUXFILE.PFC,
 YEUXFILE.ACCOUNT, 
 YEUXFILE.PFIRM,
 YEUXFILE.PSNAME,
 CASE
  WHEN YEUXFILE.PQTY >= 0 THEN YEUXFILE.PQTY - COALESCE(YODIAC.PQTY,0)
  WHEN YEUXFILE.PQTY <  0 THEN YEUXFILE.PQTY
 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


-----Original Message-----
From: Chris Payne [mailto:CPayne@xxxxxxxxxxxxxxx] 
Sent: Tuesday, August 31, 2004 1:12 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: SQL question

Ok, I reformatted your query a bit so that it was easier to read. What do
you want to happen when YEUXFILE.PQTY is < 0? If you just want to not return
the records you could add a where clause at the end.

SELECT 
 YEUXFILE.PRECID,
 YEUXFILE.CFTC, 
 YEUXFILE.PEXCH,
 YEUXFILE.PFC,
 YEUXFILE.ACCOUNT, 
 YEUXFILE.PFIRM,
 YEUXFILE.PSNAME,
 YEUXFILE.PQTY - COALESCE(YODIAC.PQTY,0) 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
Where
  YEUXFILE.PQTY >=0 



-----Original Message-----
From: Muralidhar Narayana [mailto:Muralidhar_Narayana@xxxxxxxxxxx] 
Sent: Tuesday, August 31, 2004 11:59 AM
To: rpg400-l@xxxxxxxxxxxx
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.

Thanks for your help, I sincerely appreciate your help.

SELECT YEUXFILE.PRECID,YEUXFILE.CFTC, 

YEUXFILE.PEXCH,YEUXFILE.PFC,YEUXFILE.ACCOUNT, 

YEUXFILE.PFIRM,YEUXFILE.PSNAME,YEUXFILE.PQTY-COALESCE(YODIAC.PQTY,0) 

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 


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.