Hello,
Following is the code -

select tdinv700.*
from tdinv700
where tdinv700._index1 between {:item.f," ",:cwar.f,:edat.f} and {:item.t,"ZZZ",:cwar.t,:edat.t}
order by tdinv700._index1

The problem is -
If I use BETWEEN, the output is ir-respective of the edat.f and edat.t values. But, if I use INRANGE, the output is also dependent upon the dates entered. I know the difference between INRANGE & BETWEEN.
I have checked the table definition of tdinv700. Item and Container (first two fields of the index) are the combined fields. Has this problem got something to do with that!?:confused:

Hi morpheus,

Just an idea.
Did you work with containers on your system?

If not, remove the space between the " " in your statement.

Juergen

No, the containers are not used, that is why I used " " to "ZZZ" range. Also, I have used such ranges in other sessions/queries, and it does work!!

I think you should not provide the second arg at all try:

where tdinv700._index1 between {:item.f,,:cwar.f,:edat.f} and {:item.t,,:cwar.t,:edat.t}

try it with
...
where tdinv700._index1 inrange {:item.f,"",:cwar.f,:edat.f} and
...

Juergen

I did try that, but the system throws an error -
Illegal type assigned to edat.f.
Probably, it equates edat with the warehouse (part of index), and expects it to be of string type!!
I think second argument has to be there.

Just tested it on our system (IVc4 SP9).

Both versions (" " and "") must work. So I think there is another problem.

Sorry that I can`t help you!

Regards,
Juergen

Hello there !!

First of all, use inrange instead of between. I believe you have two possible solutions for your problem.

First:



select tdinv700.*
from tdinv700
where tdinv700._index1 inrange {:item.f, " ", :cwar.f, :edat.f} and {:item.t, "ZZZ", :cwar.t, :edat.t}
order by tdinv700._index1



Second (slower, but certainly will work):



select tdinv700.*
from tdinv700
where tdinv700._index1 inrange {:item.f} and {:item.t}
and tdinv700.cwar inrange :cwar.f and :cwar.t
and tdinv700.trdt inrange :edat.f and :edat.t
order by tdinv700._index1



Note that in the first solution there are "three blank spaces" and not zero or one. Use always zero or the maximum number respecting the domain. The second is slower than the fisrt but it will work fine.

Juergen,
I am also on BaaN IV c4 with SP9, but it does NOT work here!!

DB,
I know these two solutions as proposed by you, and why use INRANGE and not BETWEEN (second case)!?

And the query is - why "edat" is ineffective in BETWEEN and effective in INRANGE!!?:confused:

Look here
http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_sql_and_combined_fields
for the difference betweed INRANGE and BETWEEN

Uli

My dear friends,
I know the difference between the two.
When I use BETWEEN, I ask the system to fetch the records between "item.f," ",cwar.f,edat.f" and "item.t,"ZZZ",cwar.t,edat.t" (the COMBINATION of these values). But, the system also fetches the records where the item,cont and cwar do fall in the limits, but edat does not!! That is, I also get the records for which the date is outside the specified limit!!

Whereas, if I use INRANGE, I ask the system to fetch the records between "item.f - item.t", "'ZZZ' - 'ZZZ'", "cwar.f - cwar.t" and "edat.f - edat.t" (SEPARATE fields). Here, the system fetches the right records.

Now, how come BETWEEN is behaving in such a manner!!?

@morhpeus

i think, the problem is the following with the between operator :

lets do an simple example, we have two fields, item and date in the index. Your selection is from item 4711, date 991001 to item 4713, date 991015. So the index looks like :

4711991001
4713991015

So the optimizer compares your select statement with an >= and <= expression. If you have the follwing row also in your table (item = 4712 and date = 991016), the expression is >= 4711991001 and <= 4713991015 and the database fetch the row, but this is not the result you like to get.

Martin

Aah, I got it now!! I somehow forgot the way date is stored in BaaN IV tables!! How could I!!:o
Thanks for all the replies and sorry for acting so stupid.;)