I am selecting the tdsls040 / tdsls041 / tdsls045 tables. I link the tables up using the refers statement.



Now what exactly are the records selected in the selectdo ?? Lets say the user enters a sales order number 10, it has 10 lines and each line has 2 deliveries.



How many times does the selectdo statement get executed ? 1 time, 10 times or 20 times. ?



Is it dependent on the order by clause ? If I say order by tdsls040._index 1 then the selectdo is hit 1 time

If I say order by tdsls041._index 1 then the selectdo is hit 10 times

If I say order by tdsls045._index 1 then the selectdo is hit 20 times

Without know the tables but going by what you described the answer would be 20 times. The order by only determines the sort on the records, so it would not effect the number of records. A "group by" would effect the number of records. This is assuming the only thing you have is an order and there are no other restrictions.

So maybe I did not understand the question.

Mark

here is the P code:

select tdsls040 tdsls041 tdsls045
from tdsls040 tdsls041 tdsls045
where tdsls041.PlannedDeliveryDate between UserEnteredDate
or tdsls045.DeliveryDate between UserEnteredDate
and tdsls045 refers to tdsls041
and tdsls041 refers to tdsls040
order by tdsls045
selectdo
accumulate the Delivered and Order Qty's
section is hit 20 times ???
endselect

Basically I am trying to accumulate the ordered and delivered qtys for orders that either have their Delivered date (tdsls045) or Planned Delivered Date (tdsls041) within the range provided by the user on the Form (UserEnteredDate).

This question came about because I got an Oracle error when the query ran regarding the Outer Join could not be performed. It compiled o.k

Thanks..

It looks to me like you will need to different queries. One based on delivery date and one based on planned delivery date. I am not sure if you could do it in one query. At least not that I can see and not where you are using the "or". Maybe a real expert knows how to do this, but I don't.

Good Luck!

Mark

the p code:

select tdsls040.*
from tdsls040
where tdsls040._index1 inrange {:orno.f} and {:orno.t}
and tdsls040.ddat inrange {:ddat.f} and {:ddat.t}
and tdsls040.prdt inrange {:prdt.f} and {:prdt.t}
and tdsls040.orno refers to tdsls041
and tdsls041.orno refers to tdsls045
selectdo
numbers selectdo = x + y + z
endselect

z = # Regs. tdsls045.orno = tdsls041.orno and
tdsls045.pono = 10,20,30 .....

y = # Regs. tdsls041.orno = tdsls040.orno and
tdsls041.pono = 10,20,30 .....

z = tdsls040.orno = user range

Check it :)

thanks for the response - but I need the query based on the user input date from and date to - to find any deliveries using the date delivered from the tdsls045 or the planned delivery date which can be modifed at the s.o line level - that it why I use the planned delivery date from the tdsls041.

The user does not provide the S.O number.

thanks anyway.

Why dont you set up a test order and test several senarios?

use variable long ran.query that gets initiated between retry.point() and the select and gets incremented right after selectdo.

Hello,

First of all, use the clause between only with combined fields, for non-combined use always the clause inrange.

Your selection with the planned date of the tdpur041 and tdpur045 does not make sense. Are you sure about that? Try to check this information.

I built a example using the planned date of tdpur041, the field tdpur041.ddtb. In my example, ddtb.f and ddtb.t are form variable, the date entered by the user. Try to use the select of tdpur045 separated, it's much better to read and understand the code and doesnt make much difference in performance.
select tdsls040.*, tdsls041.*
from tdsls040, tdsls041
where tdsls041._index3 inrange {:ddtb.f} and {:ddtb.t}
and tdsls041.orno refers to tdsls040
selectdo
select tdsls045.*
from tdsls045
where tdsls045._index1 = {:tdsls041.orno, :tdsls041.pono}
selectdo
make.your.calc.here()
endselect
endselect
:cool: