Hi

While compiling mentioned code getting 4 error messages for columns of tisfc001 table. One of the example as below.
Error SQL: Column 'tisfc001.pdno' is not used as group identifier

In the Query, table tisfc001 contains unique records for item where as table ticst001 contains multiple records for one item. I want summation of field ticst001.qucs for one item.

Required your HELP !!!

Thanx

function read.main.table.D()
{
select tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv, tisfc001.osta,
ticst001.pdno, ticst001.sitm, sum(ticst001.qucs):est.qty
from tisfc001, ticst001
where tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
{:cprj.t,:item.t,:pdno.t}
and tisfc001.osta BETWEEN 5 and 7
and ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
and ticst001.pdno = :tisfc001.pdno
group by ticst001.pdno, ticst001.sitm
order by tisfc001.pdno, tisfc001.mitm
}

Hi

The field tisfc001.mit is missing a letter in the order by clause ;)

Regards,

- George

Its typographical mistake, in the original query the command line is

order by tisfc001.pdno, tisfc001.mitm

The field tisfc001.pdno is missing in the group by clause. For my experience you cannot order by a field that is not in the group clause.

Regards
csecgn

Hi csecgn

Is it reqd to use tisfc001.pdno field in Group By statement in addition to ticst001.pdno. Is it allowed ?

Thanx

I Don't know and I've never tried it. But is it necessary?
If your relations are OK, I think you only need tisfc001.pdno (or the other one. I would try both and look if there is a difference in the result and the performance).

Regards
csecgn

This should work:


select tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv,
tisfc001.osta, ticst001.sitm, sum(ticst001.qucs):est.qty
from tisfc001, ticst001
where tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
{:cprj.t,:item.t,:pdno.t}
and tisfc001.osta BETWEEN 5 and 7
and ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
and ticst001.pdno = tisfc001.pdno
group by tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv,
tisfc001.osta, ticst001.sitm


Why include the ticst001.pdno? You already have the tisfc001.pdno. As far as I know when you to queries with sums you have to group by all other fields. You can also always separate the queries and do the sum of ticst001 in a query inside the selectdo on tisfc001. Also notice I removed the : infront of the tisfc001.pdno in the where clause.

Hi Mark

Thanx I will check the results by applying your suggesstions. As mentioned by you, plz tell me the sub-query syntax by considering my posted SQL. It will be helpful to me.

Why include the ticst001.pdno? You already have the tisfc001.pdno. As far as I know when you to queries with sums you have to group by all other fields. You can also always separate the queries and do the sum of ticst001 in a query inside the selectdo on tisfc001. Also notice I removed the : infront of the tisfc001.pdno in the where clause.

HI Mark

The code suggested by you is working fine. Plz let me know the sub-query logic for the same code so that I can check the performance also.

Thanx

Sometimes I find separating queries to be quicker and sometimes I separate queries just to make it easier for me to understand what is happening. In this case I am not sure separating the queries would increase performance. Reading the performance guide (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7665) can help in determining when to separate queries.


select tisfc001.pdno, tisfc001.mitm, tisfc001.qdlv,
tisfc001.osta
from tisfc001
where tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and
{:cprj.t,:item.t,:pdno.t}
and tisfc001.osta BETWEEN 5 and 7
selectdo
select ticst001.sitm, sum(ticst001.qucs):est.qty
from ticst001
where ticst001._index1 = {:tisfc001.pdno}
and ticst001.sitm inrange {:rm.item.f} and {:rm.item.t}
group by ticst001.sitm
selectdo
endselect
endselect