Hi,
I am trying to select unique sales order numbers from the table tdsls045 and in some cases the system is not doing so. I am not sure about the mistake in my code which is given below;
select tdsls045.orno :orno1
from tdsls045, tiitm001, tccom010
where tdsls045._index1 inrange {:orno.f} and {:orno.t}
and (tdsls045.orno between :orno.f and :orno.t
and tdsls045.orno not between 800000 and 899999)
and tdsls045.item between :item.f and :item.t
and tdsls045.cuno between :cuno.f and :cuno.t
and tdsls045.ddat between :ddat.f and :ddat.t
and tdsls045.item refers to tiitm001
and tdsls045.cuno refers to tccom010
and tiitm001.kitm = tckitm.manufacture
and tccom010.ccty = "BAH"
group by tdsls045.orno
selectdo
further process......
endselect

Please let me know how can i get unique sales order number in the above code to further process after the selecdo option.

You should always use an order by statement when using the group by, maybe that's the problem. It will only group when the orders are in sequential order.

Why are you using orno.f and orno.t range 2 times?
It seems superfluous.

where tdsls045._index1 inrange {:orno.f} and {:orno.t}
and (tdsls045.orno between :orno.f and :orno.t
and tdsls045.orno not between 800000 and 899999)
I think this is also possible:
where tdsls045._index1 inrange {:orno.f} and {:orno.t}
and tdsls045.orno not between 800000 and 899999

Hi,
I have made changes as suggested and problem still exists. Code is now as follows;
select tdsls045.orno :orno1
from tdsls045, tiitm001, tccom010
where tdsls045._index1 inrange {:orno.f} and {:orno.t}
and tdsls045.orno not between 800000 and 899999
and tdsls045.item between :item.f and :item.t
and tdsls045.cuno between :cuno.f and :cuno.t
and tdsls045.ddat between :ddat.f and :ddat.t
and tdsls045.item refers to tiitm001
and tdsls045.cuno refers to tccom010
and tiitm001.kitm = tckitm.manufacture
and tccom010.ccty = "BAH"
group by tdsls045.orno
order by tdsls045.orno
selectdo
further process......
endselect

Hi,

I also have very often problems with group by :mad: . So I'm mostly using a "complicate way" without group by.

Example

select tdsls040.*,
tccom010.*
from tdsls040,
tccom010
where tdsls040._index1 inrange {:orno.f} and {:orno.t}
and (tdsls040.orno < 800000 or tdsls040.orno > 899999)
and tdsls040.cuno inrange :cuno.f and :cuno.t
and tdsls040.cuno refers to tccom010
and tccom010.ccty = "BAH"
selectdo
select tdsls045.orno,
tiitm001.kitm
from tdsls045,
tiitm001
where tdsls045._index1 = {:tdsls040.orno}
and tdsls045.item inrange :item.f and :item.t
and tdsls045.ddat inrange :ddat.f and :ddat.t
and tdsls045.item refers to tiitm001
and tiitm001.kitm = tckitm.manufacture
as set with 1 rows | In this case the same result like group by
selectdo
further process......
endselect
endselect


Maybe a little slower, but easier to handle.

Regards
csecgn