Calculations within PFI - LawsonGuru.com

Name	Points
Greg Moeller	4184
David Williams	3349
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244
Chris Martin	825

Bob

Basic Member

Posts: 7

10/20/2011 6:53 PM

I am attempting to use PFI to simulate generation of reports. The query that PFI accepts is:
SELECT UCCUNO, UCORNO, UCIVQT, UCCUAM
FROM *LIBL.OSBSTD
WHERE UCORTP IN ('CUS','EMC') AND UCIVDT BETWEEN 20100201 AND 20100228.

However, the actual SQL statement I want to apply to PFI is:
SELECT UCCUNO, UCORNO, SUM(UCIVQT), SUM(UCCUAM)
FROM *LIBL.OSBSTD
WHERE UCORTP IN ('CUS','EMC') AND UCIVDT BETWEEN 20100201 AND 20100228
GROUP BY UCCUNO, UCORNO

How can this be accomplished within PFI?

Thanks

George Graham

Veteran Member

Posts: 201

10/20/2011 9:05 PM

Are you getting an error?

Bob

Basic Member

Posts: 7

10/21/2011 12:11 PM

HI George:
I did not receive any error during the genration of PFI, but the return value of both summed data field are "undefined". Is there any way to fix this issue? This issue is the only thing preventing this PFI being published.

Thanks
Bob

John Henley

Posts: 3366

New Poster

Engaged Reader

Avid Reader

10/21/2011 1:13 PM

Use an alias in the SQL for the sum columns, e.g. SUM(UCCUAM) AS UCCUAM_SUMMARY.
Then reference nodename_UCCUAM_SUMMARY in your flow.

Bob

Basic Member

Posts: 7

10/21/2011 1:55 PM

It work! Thank you so much...