error while i Aggregate filed sum() group by

[#1]

Hi All,

im trying to aggregate filed and grouped on load, but my scribt not work and give me the following error message :

ODBC read filed

SQL SELECT *

FROM S65C127A.JBFDATA.F43121

please check my script and inform me if there is something wrong..?? and note when i remove group by it's work fine!!

my data source is JDE.

/***********************************************/

Load

PRAN8 as [Address Number],

sum (PRAPTD) as [Amount Paid - ICL],

sum (PRAREC) as [Amount Rcv.],

sum (PRFAPT) as [Foreign Amount Payed],

PRMATC as [Match Type]

group by PRAN8;

SQL SELECT *

FROM S65C127A.JBFDATA.F43121;

Thanks For all,

[#2]

Here is my shot (from help):

load [ distinct ] *fieldlist

[( from file [ format-spec ] | inline [ format-spec ] data |

resident table-label | autogenerate size)]

[ where criterion ] | while criterion ]

[ group by fieldlist ]

[ order by field [ sortorder ] { , field [ sortorder ] } ]

As I can see the anwser is that "Group by" is not in the right place; you should try the following (:P):

1. Put the group by statement at the end of the load statement (I think it won't work, but give a chance).

2. Group by at SQL statement (you should do this).

3. Try another load statement after you load your table (i.e. LOAD .... RESIDENT .... GROUP BY .....;)

Hope it helps!

[#3 · Accepted solution]

I see an error on the group by syntax. The field PRMATC is neither on a aggregation expression or in the group by statement. If you are sure that this field would have only one value within the group by, you can use the "only(PRMATC)" or if does get more than one value, just include it with the group by statement.

Regards.

[#4]

Hi Sir,

i alrady try to fix it after SQL SELECT and after load but not working

i don't know maybe JD Edward have specific aggreation ..??

[brenner_martina]

Hi,

first load all data from datasource by the SQL-statement in a temporary table, then manage the group by statement:

TempTable:
Load
PRAN8 as [Address Number],
PRAPTD,
PRAREC,
PRFAPT,
PRMATC;
SQL SELECT *
FROM S65C127A.JBFDATA.F43121;

Table:
Load
PRAN8 as [Address Number],
sum (PRAPTD) as [Amount Paid - ICL],
sum (PRAREC) as [Amount Rcv.],
sum (PRFAPT) as [Foreign Amount Payed],
Only(PRMATC) as [Match Type]
Resident Table
group by PRAN8;

Drop Table TempTable;

Fields, that will not grouped must be loaded with function Only().

[#6]

Hi,

I tried your suggestion but give me error table not found.

i try to make the normal aggregation with SQL database, QVD Excel and all of them work fine....!!!

but when i try to make on JDE it's give me this error .

i think may something wrong with the data ,

i will try to check it,

and thanks all of you for your support,

[#7]

You're selecting two fields (PRAN8, and PRMATC), and 3 aggregates, all sums. Your group by clause only contains one of the fields. PRAN8 (which is aliased, as well).

Just for fun, take out your aliases (I'm not 100% sure on the alias syntax, so get it to work without your aliases first, and then change it afterwards); and then add PRMATC to your group by clause. ie: GROUP BY PRAN8, PRMATC

Then, I suspect your script should load just fine.

[#8]

Hi ivan_cruz,

your solution is work fine with me when i use Only()

Thanks a lot for all of you dears.