Hi

How to use index key for a particular table in SQL especially when index keys are more than one for single table. In below sample case I need _index2 for tibom010 table to include one more condition as :
tibom010.sitm >= :rm.item.f and tibom010.sitm <= :rm.item.t

SQL
select tisfc001.*, ticst001.*, tibom010.*
from tisfc001, ticst001, tibom010
where tisfc001.pdno >= :pdno.f
and tisfc001.pdno <= :pdno.t
and tibom010.mitm >= :item.f
and tibom010.mitm <= :item.t
and tibom010.mitm = tisfc001.mitm
and tibom010.sitm = ticst001.sitm
and tisfc001.pdno = ticst001.pdno

Thanx in advance

Regards,


GANESH

Hi

As per what i do is always use index which is primary, even if i could use the second index along with it, because such type of SQL could have a performance issue, or use the index for which you have got the value to search for, if it is " " (blank) to "ZZZZ" then better use the index for which you have got some value.

In your case as your main table in selection is tisfc001 use only tisfc001._index1.

By the way could you elaborate on what you are actually trying to get out of that query ?. May be able to suggest you to get some better query ...

Thanks & Regards

You can also use "table._index2 inrange {:item.f} and {:item.t}". So for your query I would do something like this:

select tisfc001.*, tibom010.*
from tisfc001, tibom010
where tisfc001._index1 inrange {:pdno.f} and {:pdno.t}
and tisfc001.mitm inrange {:item.f} and {:item.t}
and tibom010._index1 = {tisfc001.mitm}
and tibom010.sitm inrange {:sub.item.f} and {:sub.item.t}

Not sure I understand what you are doing with ticst001 - because you sub-item query can come from there. So like v_chandra understanding what you want out of the query may help someone make better suggestions.

Hi

I am trying to write a SQL to generate a report of BOM v/s Production Order having order status as Active, Completed or Closed.

There are two reasons as 1) Sometimes there is difference between the qty required as per BOM and actual qty used in the Produtction Order.
2) Sometimes we were using items / material which are not defined in the BOM through "Maintain Estimated Material (tisfc0110m000) session".

We want list out those items. If you suggest better SQL or tips for script I will be thankful to all.

Regards,


Ganesh

BaanERP Application Software Engineering Guides

Choosing the index
--------------------------------------------------------------------------------

Problem
It is important to know how the driver will define an index before it parses a query.

Solution
The database driver will create index hints to the RDBMS. These index hints are defined as follows:

First, the where condition is evaluated to define which table fields are checked.
After this evaluation, the number of index parts in each index (from 1 to the number of indices) is compared with the number of matching fields used in the where condition.
The index number with the highest hit rate (most matching) will be taken as index hint to the database.
If there are equal numbers of fields in the where condition that match the index fields, the index with the lowest number is taken.

There are two solutions to force the hint generator to give the correct index hint to the database.

Implement order by table._index?
Implement index hint in 4gl program. (Available from the Verdi release).
See help page 'Index hints'.

Example

Table books has the next fields: book number, description, author, publisher, ISBN number.
It has the next indices: 1. Book number, 2. Author and book number, 3. ISBN number, 4. Publisher and book number.

Bad situation

select books.*
from books
where books.number inrange 1 and 10
and books.publisher = "Publisher X"
The driver will go for the 4th index. This is really bad for performance: If a publisher has hundreds of books, hundreds of records are checked on the number range 1 to 10.

Note:
the output sequence is undefined.

Improved

select books.*
from books
where books.number inrange 1 and 10
and books.publisher = "Publisher X"
order by books._index1
The driver will go for the 1st index. Output sequence is defined. Not more than 10 books will be read and checked on the specific publisher.
Improved (Verdi solution)

select books.*
from books
where books.number inrange 1 and 10
and books.publisher = "Publisher X"
hint use index 1 on books
The driver will go for the 1st index. Output sequence is undefined.

BaanERP Application Software Engineering Guides

Queries on tables with multiple index parts
--------------------------------------------------------------------------------

Problem
If a table index has multiple index parts, the performance will slow down when queries are implemented by having one or more of the next where conditions:

where table._index? >= { :field1 }
where table._index? > { :field1 }
where table._index? <= { :field2 }
where table._index? < { :field2 }
where table._index? between { :field.f } and { :field.t }
Solution
If possible, reduce the number of index parts and/or read by specifying the highest number of index parts in an index.

Example
Bad situation

select catalogue.*
from catalogue
where catalogue._index2 inrange { :a, :b, :c, :d }
and { :a, :b, :c, :e }
selectdo
Improved

select catalogue.*
from catalogue
where catalogue._index2 = { :a, :b, :c }
and catalogue.d inrange :d and :e
selectdo

Well Ganesh I do not know about others, but I like to "keep things simple". There are many ways to do what you are asking. In my session I would probably do something like this:


select tisfc001.mitm, ticst001.pdno, ticst001.sitm,
ticst001.qucs
from tisfc001, ticst001
where tisfc001._index1 inrange {:pdno.f} and {:pdno.t}
and tisfc001.osta inrange tcosta.hours.adj and tcosta.closed
and ticst001._index1 = {tisfc001.pdno}
and ticst001.sitm inrange :sub.item.f and :sub.item.t
selectdo
| You can play with other indexes - like index2 on the tibom010
select tibom010.qana
from tibom010
where tibom010._index1 = {:tisfc001.mitm}
and tibom010.sitm = :ticst001.sitm
selectdo
| Compare qty - double.cmp might be better
if tibom010.qana <> ticst001.qucs then
rprt_send()
endif
selectempty
| Item not on BOM
rprt_send()
endselect
endselect

This SQL is a simple query that says a part in ticst001 is either on the BOM or not. This would not work at our site since we use multi-level phantoms. And if you want to do this as simple EASY-SQL query(prompting for the order and sub-order range) then you take the inner select and put it in the detail section of a report script. At that point the inner select would use lattr.print instead of rprt_send. Like I said - a simple easy to understand query set-up. I am sure there is a way to combine it all into one query, but sometimes it is best to break the queries up into individual selects.

Hi Mark

My purpose solved, I wrote code as below. First it will compare tisfc001 with tibom010 and vise versa.

{
select tisfc001.*, ticst001.*
from tisfc001, ticst001
where tisfc001._index2 inrange {:cprj.f,:item.f,:pdno.f} and {:cprj.t,:item.t,:pdno.t}
and tisfc001.osta BETWEEN 5 and 7
and ticst001._index3 inrange {:rm.item.f} and {:rm.item.t}
and tisfc001.pdno = ticst001.pdno
order by tisfc001.pdno, tisfc001.mitm, ticst001.sitm
selectdo
ord.mitm = tisfc001.mitm
ord.sitm = ticst001.sitm
compare.bom() |to get items which are not in BOM but are estimated in Prod.Order
mfg.itm.desc()
if m.cnt = 0 then
if yesno = tcyesno.no then
if ((bom.qty * tisfc001.qdlv) - ticst001.qucs) <> 0 then |bom.qty = BOM net qty
m.count = 0
else
m.count = 9
endif
endif
endif
rprt_send()
endselect
bom.item.not.in.order()
}

function compare.bom()
{
select tibom010.*
from tibom010
where tibom010.mitm = :ord.mitm
and tibom010.sitm = :ord.sitm
selectdo
m.cnt = 0
m.count = 0
bom.qty = tibom010.qana
selectempty
m.cnt = 1
m.count = 1
endselect
}

function mfg.itm.desc()
{
select tiitm001.*
from tiitm001
where tiitm001.item = :ord.mitm
selectdo
m.dsca = tiitm001.dsca
endselect
}

function bom.item.not.in.order()
{
select tisfc001.*, tibom010.*
from tisfc001, tibom010
where tisfc001._index1 inrange {:pdno.f} and {:pdno.t}
and tibom010._index2 inrange {:rm.item.f,:item.f} and {:rm.item.t,:item.t}
and tisfc001.osta BETWEEN 5 and 7
and tisfc001.mitm = tibom010.mitm
order by tisfc001.pdno, tibom010.mitm, tibom010.sitm
selectdo
ord.mitm = tibom010.mitm
ord.sitm = tibom010.sitm
ord.no = tisfc001.pdno
compare.order() |to get items which are in BOM but are deleted from Prod.Order
mfg.itm.desc()
if m.cnt = 0 then
if m.cnt1 = 0 then
m.count = 2
rprt_send()
m.cnt1 = 1
endif
if yesno = tcyesno.no then
if (tibom010.qana * tisfc001.qdlv) <> 0 then
m.count = 3
else
m.count = 9
endif
endif
endif
rprt_send()
endselect
}

function compare.order()
{
select ticst001.*
from ticst001
where ticst001.sitm = :ord.sitm
and ticst001.pdno = :ord.no
selectdo
m.cnt = 1
selectempty
m.cnt = 0
m.count = 3
endselect
}

Thanx for your co-operation.


Ganesh