I have created the report session to output only the Demand forecast for the sales items based on the user selection.

Please find the attached file for session filters. Actually it is working but its taking long time , Is there any other way to write this query in performance
point of view.

select a.item, a.plni
from cprpd100 a
where a.item in (select tcibd001.item from tcibd001
where tcibd001.item >=:i.item.f
and tcibd001.item <=:i.item.t
and tcibd001.csig = "40"
and tcibd001.citg = :i.citg)
and Exists (select tdisa010.item from tdisa010
where tdisa010.stbp >= :i.stbp.f
and tdisa010.stbp <= :i.stbp.t
and tdisa010.item = a.item)

Any help in this regard is highly appreciated.

Thanks in advance...

Use index, instead of tcibd001.item use tcibd001._index1;
instead of tcibd001.citg use tcibd001._index4
instead of tdisa010.stbp use tdisa010._index2
instead of tdisa010.item use tdisa010._index3

select a.item, a.plni
from cprpd100 a
where a.item in (select tcibd001.item from tcibd001
where tcibd001._index1 inrange {:item.f} and {:item.t}
and tcibd001.csig = "40"
and tcibd001._index4 = :i.citg)
and Exists (select tdisa010.item from tdisa010
where tdisa010._index3 = a.item
and tdisa010._index2 inrange {:i.stbp.f}and {:i.stbp.t})

Try this. Query using indexes are faster.

Hi saumya and bhushanchanda,

Thanks a lot for your great help. Now the query is very fast.

As I am new to tools development, your explanations helped me to understand the usage of Index.

I am new and start to learn this language. I have task need to convert the script into sql. There are some things that i need your help with.

1. What is._index1, 2, 3, 4
Why use tcibd001._index1 instead of tcibd001.item and
instead of tcibd001.citg use tcibd001._index4 ?
instead of tdisa010.stbp use tdisa010._index2?
instead of tdisa010.item use tdisa010._index3?

2. What is the meaning of >=:i.item.f ?

3. what is inrange {:item.f} and {:item.t} ?

4. tibom010._index1 = {:tdsls401.item} ?

Thank you

KTKN

1. Not sure about SQL, but with Oracle using the _index4 tells the optimizer or the oracle query plan to use that specific index. Not sure if the wording is correct.

2. >= :i.item.f means find all items greater than the value of i.item.f.

3. The inrange means to find all values between the item.f and item.t.

4. The tibom010._index1 questions means use index 1 and find the items matching the tdsls401.item. In this case it only uses the item part of the index.

There is a manual attached to the forum that I suggest you peruse for write baan sql code. It might answer some of your questions.

Hi,

By your question I will assume that you have not yet got your tools training. But for your questions in addition to Mark's answers I would like to add a few lines.

1. If you know, every table has some primary keys. So, indexes are nothing but the primary keys of a table. A primary key can be a group of fields or a single field of a particular table. The 1st primary key is index1 , 2nd primary key is index2 and likewise.
So, when you say tablename._index1 it means 1st primary key of a table.

2. >= :i.item.f states that, you are querying for the items greater than or equal to the items in a table where i.item.f is a form field and not a table field.

3. Inrange is used to get the values between two fields. Here, item.f and item.t

4. tibom010._index1 is compared with tdsls401.item which will give you all the records of tibom010 table where tibom010.item field of tibom010 table are equal to tdsls401.item field in table tdsls401

You can get Programmers Guide which comes with Standard LN installation media's else as Mark suggested use this :- Programmer's Manual (http://www.baanboard.com/programmers_manual_baanerp) of Baanboard.

Keep practicing.

NOTE:- Its not always possible to use index fields as we sometimes need to put the other table fields in the WHERE clause. But, whenever the field in WHERE clause is an index field, you should use in the above mentioned way as index based searching/querying is always a faster way to get the data.