The following script cannot be compiled:

select tfgld106.*
from tfgld106
where tfgld106.otyp between :otyp.f and :otyp.t
and tfgld106.odoc between :odoc.f and :odoc.t
and tfgld106.leac in (select tcmcs036.sacn
from tcmcs036
group by tcmcs036.sacn)
selectdo
rprt_send()
endselect

Error message is:
Error SQL: Syntax error: 'group' not expected

I do not understand why this script is refused.

Thanks for your help.

Hallo,

I'm not sure but it seems as though you cannot use a group by caluse in a subselect :rolleyes: . Try the follwing select instead:
select tfgld106.*
from tfgld106
where tfgld106.otyp between typ.f and typ.t
and tfgld106.odoc between doc.f and doc.t
and exists ( select *
from tcmcs036
where tcmcs036.sacn = tfgld106.leac)
selectdo
rprt_send()
endselect

HI,

select tfgld106.*
from tfgld106
where tfgld106.otyp between typ.f and typ.t
and tfgld106.odoc between doc.f and doc.t
and tfgld106.leac in (select tcmcs036.sacn
from tcmcs036
group by tcmcs036.sacn)
selectdo
rprt_send()
endselect

I am confused why you are trying to apply a group by clause where you only selected one field from the table in a sub-select. Result will not change if that group by clause is works or not. A group by clause may effect the ordering of record in the main Select statement but not in the sub-select statement.

Mahmood

"Group by" is replacing "select distinct".

select distinct is not accepted by BaanIVc4.
So I use a group by. In SQL Plus it works.

In fact in table tcmcs036 table you can use different ledger accounts for booking the sales VAT transactions.
I want to get all of them and select these accounts and a transaction type from tfgld106.

I did what vahdani proposed and it is OK.

Thanks to you vahdani. Great idea.

Hi Friends

I hope your query will not work in BaaN . We have faced same problem earlier.

if you want to use group by command then you have to select only those field which is used in group by . You have to select rest of the field in selectdo area . In this way we are doing.



select tfgld106.sacn
from tfgld106
where tfgld106.otyp between :otyp.f and :otyp.t
and tfgld106.odoc between :odoc.f and :odoc.t
and tfgld106.leac in (select tcmcs036.sacn
from tcmcs036
group by tcmcs036.sacn)
selectdo
rprt_send()
endselect

Couldn't this be done using a "refers to" clause instead of using a sub query.