Hi,
I am 4 fields in index1.
But I want to extract values based on field1 & field4.
How to specify this while using index in the query.

Ex:
select *
from ticpr300
where ticpr300._index1 = {:ITEM,,,:COSTCOMP}

fields 2 & 3 I don't want pass any value.
How to specify that in the query?

Regards,
SAT.

Hi there SAT,

That's easy change your select statement from your to this:


today = date.num()
select ticpr300.*
from ticpr300
where ticpr300._index1 inrange {:item,"",0,:costcomp}
and {:item,"ZZZ",:today,:costcomp}


Be warned that more than one row may be returned so this must be handled in your selectdo

Regards,
Rupert

select *
from ticpr300
where ticpr300.field1 = :ITEM
and ticpr300.filed4 = :COSTCOMP

Why do you want to use ._index1? If you really want to use it, you can use:

select *
from ticpr300
where ticpr300._index1 = {:ITEM}
and ticpr300.filed4 = :COSTCOMP

Off course field1 and field4 should be changed according to the real field name.

Hope this helps,
Dick

It is better to specify ticpr300._index1 = {:ITEM} and not ticpr300.item = :ITEM. Sometimes the database drivers do not select the correct index for the query and this forces it. Just a good programming habit.

Mark

Dick and Mark are right.

Refer to -

Index hint (http://www.baanboard.com/programmers_manual_baanerp_help_functions_query_hints_index_hint) and also Hint types (http://www.baanboard.com/programmers_manual_baanerp_help_functions_query_hints_hint_types)

If a wrong index is chosen by the database you might find some bad performance. Using hints will allow the BaaN driver to specify the index explicitly instead of the Database picking up a wrong index.

Mark is partly right:

The table._index "hint" is only used when using a level 1 driver. So when writing custo's I have no problem with it, but when writing generic SQL you have to take care.

In case you want to force the use of an index for Level 2 you can use the index hints as mentioned by NPRao for all versions > BIV.

Kind regards,
Dick