I have found some things in baan standard scripts which confuse me. I have found that the WHERE clause of a select statement has been used with INRANGE. This has been used where only part of the key is known e.g.

where timrp030._index1 inrange {koor.f, orno.f}
and {koor.t, orno.t}

Would this not result in some data possibly being missed, because the maximum possible values for the remaining fields of the index (pono, ponb, sern) have not been defined. Does the fact that the rest of the fields are not detailed, mean that the min and max values are somehow defaulted to the where clause? (I know that the INRANGE method selects fields individually, rather than concatenated, as the BETWEEN method does)

I would normally code my select statement along these lines;
Where timrp030._index1 between
{koor.f, orno.f, pono.min, ponb.min, sern.min}
and {koor.t, koor.t, pono.max, ponb.max, sern.max}

I am sure I remember correcting reports in the past, where required data has not been selected for this reason. If I am mistaken, please correct me.


thanks

p.s. (I have removed the : symbol as lots of smiley faces appeared in my text)

Using "where timrp030._index1 inrange {koor.f, orno.f} and
{koor.t, orno.t}" should be no problem since all records which
suit the condition imposed ( even those with different ponb or
sern ) would be catched by the query. Note that children of
index fields for which no value is specified are not included
in the condition.

As you pointed, INRANGE or BETWEEN should be used if you want
to apply the boundaries indicated to each separate field or to the
combined field as a whole.

Tomas Sanchez Tejero
tsanchez@consultant.com

I use the inrange method all the time with only one or two pieces of the index. I have not had a problem with this. Sometimes all I have from the user is one piece of the key. I would always like to have the full key, but that is just not possible. So I guess maybe I do not really understand the question.

Mark

This post (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=840) by Pat tells you how to keep unwanted smiles out of your posts.

inrange is equal to #>= and #<=
between is equal to >= and <=


Followng is from BAAN SQL help:


-----------
If a child field of a combined field is not specified, the value of this field is free and is not included in the condition. The following example will make this clear:

Example:

Suppose the combined field ppmod001.comb1 consists of the fields ppmod001.modu, ppmod001.tblno and ppmod001.compno

WHERE ppmod001.comb1 #>= {"adv", "000", "100"} AND ppmod001.comb1 #<= {"zzz", "999"}

equals (written out):

WHERE ppmod001.modu >= "adv" AND ppmod001.modu <= "zzz" AND ppmod001.tblno >= "000" AND ppmod001.tblno <= "999" AND ppmod001.compno >= "100"

As the field ppmod001.compno has no upper limit, all values greater than or equal to 100 are fetched.

....

As with other combined fields, children of index fields for which no value is specified are not included in the condition. However, fields can only be left unspecified at the end of the index, not in the middle.

----------

First where is equal to:

WHERE ppmod001.comb1 inrange {"adv", "000", "100"} AND {"zzz", "999"}

I'm sure you're right with your answer. I just found the baan help confusing on this matter. It isn't at all clear.

Smiffy -
Are you clear now?

Your thought was correct, it fills missing parts of the index with the min/max of the domain. Only on the end..you can't skip parts of the index and then give values.

The Baan example shows that if you give values on the From range for Parts 1,2 and 3 of the index, and values on the To range for Parts 1 and 2 only, then third part will be between the From value provided, and the maximum value allowed for the domain.

This has always been the case from back on Triton, so, you should not have had this problem in the past.

Lisa

Yes, I'm sure you are correct. I think problems I have encountered and corrected previously (over a year ago), may have been because INRANGE was used instead where BETWEEN was more appropriate. It was probably that some data was missed, because the key needed to be selected as a concatenated value (similar to the example given in the baan help).

I have just got used to using my method of selecting the data when a range needs to be selected for an index of a table. It all depends on how much selection criteria you wish to give the users of the report. Sometimes, too much criteria can be confusing and mistakes be made when entering the range data.

Just as an extra tip; Sometimes, I like to produce reports where the selection ranges entered for a report are displayed as a header or footer on the report. This identifies where reports are for a particular selection range and false assumptions about the content can be avoided.


Thanks again