I am trying to perform a select with a sum and group by on a table with an array field. The compiles fails with an syntax error the '(' is not expected. Here is my code:

select tfacr200.ninv:ninvc, min(tfacr200.docd):docdc,
sum(tfacr200.amth(1)):amntb,
sum(tfacr200.cdam(1)):amntc
from tfacr200
where tfacr200.itbp = bpid
and (tfacr200.amth(1)+tfacr200.cdam(1)) < 0
group by tfacr200.ninv
order by 1

If I change the array fields to non-array fields, the compile is clean. Has anyone run into this problem before and how did you get around it?

Thanks in advance for your help!

Hi , try following structure with having statement


select tfacr200.ninv:ninvc, min(tfacr200.docd):docdc,
sum(tfacr200.amth(1)):amntb,
sum(tfacr200.cdam(1)):amntc
from tfacr200
where tfacr200.itbp = bpid
group by tfacr200.ninv
HAVING (tfacr200.amth(1)+tfacr200.cdam(1)) < 0
order by 1


GOOD LUCK !!!

Hi mr_suleyman,

Thanks for your suggestion, but the problem is not with the where clause or having clause. If I remove the 2nd part of the where clause (i.e. only using the bpid as a conditional), the compile still fails with the syntax error of '(' not expected. The problem is with the sum function on array fields.

Does anyone else have any suggestions?

Thanks!

Set functions (max, min , avg etc) cannot tbe applied to the array fields in Baan SQL.

You can compute the sum in selectdo loop.

Or alternatively, why don't you just declare the fields amntb and amntc also as array fields, and then just assign the value...

Or alternatively, why don't you just declare the fields amntb and amntc also as array fields, and then just assign the value...

Baan will still give the error.

No, it won't. This must work. Of course, in case you misunderstood, you should not specify the elements, just the whole array.

Example:
extern domain tfgld.amnt amntb(5)

select tfacr200.amnt:amntb
from tfacr200 etc

Maybe I mis-misunderstood, I thought you were asking to do...

domain tfgld.amnt amntb(3)

select sum(tfacr200.amnt):amntb
from tfacr200

The above code will not work

Oops, I sincerely apologise about this misunderstanding! I didn't read through your original post carefully, I thought I saw tfacr200.amnt, and tested with that on my system. Since tfacr200.amnt is not an array, there was no error. In your case it is indeed according to Baan documentation not possible to use aggregating functions on array fields, so you should just do the aggregation in the selectdo section.

Sorry again for the confusion and good luck!