I got a syntax error: "group" not expected as complie program session.
My purpose is to join purchase receipt transaction with the latest purchase orders' item prices and suppliers.
I have several kinds of "group" query but still got the same error.
Please help. Does "group" not work in subquery? (But, I found an example in some BaaN materials)

Baan code as below:

select tdinv700.*, tiitm001.*, tccom020.*, tdpur040.*, tdpur041.*
from tdinv700, tiitm001, tdpur041, tdpur040, tccom020
where tdinv700.trdt between :trdt.f and :trdt.t
and tdinv700.cwar between :cwar.f and :cwar.t
and tdinv700.kost = :kost
and tdinv700.item between :item.f and :item.t
and tdinv700.item refers to tiitm001
and tdpur041 refers to tdpur040
and tdpur041.item = tdinv700.item
and tdpur041.suno between :suno.f and :suno.t
and tdpur040.suno refers to tccom020
and tdpur040.odat in
(select m0.odat
from tdpur040 m0, tdpur041 m1
where m1 refers to m0
and m1.item = tdpur041.item
group by m1.item having max(m0.odat))
selectdo
rprt_send()
endselect

All elements of the <group condition> must be specified in the <select list> of the SELECT clause.


Link (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_group_by)

May I give some performance hints?

- Only mention the used tablefields (where possible) instead of using table.* in the select clause. This will improve the transport speed from database to bshell. Off course only valid when running in Level 2 mode.

- Use REFERS TO ... UNREF SKIP or '=' where the the releationship between the tables is mandatory. Oracle performs better with an relation ship on EQUALS ("=" ) than on OUTER JOINS with is the default result of a REFERS TO.

Maybe it sounds very technical but I have too few space to describe the whole stuff here :rolleyes:

Kind regards,
Dick

morpheus:

I have tried several "Group" method like:

Exists
(select m1.item, max(m0.odat)
from tdpur040 m0, tdpur041 m1
where m1 refers to m0
and m1.item = tdpur041.item
group by m1.item)

or

tdpur040.odat in
(select max(m0.odat)
from tdpur040 m0, tdpur041 m1
where m1 refers to m0
and m1.item = tdpur041.item
group by m1.item )

You want to have the max odat so in your case you need to write:


(select max(m0.odat)
from tdpur040 m0, tdpur041 m1
where m1 refers to m0
and m1.item = tdpur041.item )

Kind regards,
Dick

BTW: Are you realy realy sure that you need this construction? Every time your tdpur041 record changes a full tablescan in your subquery will be generated on tdsls041. This can take ages on a huge table! As I don't know the logic I 'don't know if it can be changed.

One thing that can be changed is to change the last line before the subquery from:

and tdpur040.odat in

into:

and tdpur040.odat =

This will probably not improve the performance but makes it better readable.