Dear All

I want to know if there is another method and faster than use select sql for seeking data in a table ? please advise me and give a sample.
Thanks

Hi,

SQL is the fastest way I know from within Baan tools. There is also the dynamic SQL (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_dynamic_sql). If you post your SQL you might get help and get it fatser.

- George

Hi George,

Yes, I have a problem with SQL, its very slowly, OK I give you sample. I want to search a item in table tiitm001.item to conversion table tiitm004.item for get a conversion, like this,

extern domain tcitem item.f
extern domain tcqiv1 xmeter

select tiimt001.*
from tiitm001
where tiitm001.item = :item.f
selectdo
select tiitm004.*
from tiitm004
where tiitm004.item = :item.f
and tiitm004.basu = :tiitm001.cuni
selectdo
if tiitm001.unit =" m" then
xmeter = tiitm001.stoc/tiitm004.conv
endif
selectempty
xmeter = 0.0
endselect
endselect


any idea ???


regards

Yadhi

Actually, although George's suggestion might in some cases be even more performant, I don't see a reason your original query should take long, except for the fact that you should add 'as set with 1 rows' before the selectdo...

Read with index :


extern domain tcitem item.f
extern domain tcqiv1 xmeter

select tiimt001.*
from tiitm001
where tiitm001._index1 = {:item.f}
selectdo
select tiitm004.*
from tiitm004
where tiitm004._index1 inrange {"", :item.f}
and {"ZZZZZZ", :item.f}
and tiitm004.basu = :tiitm001.cuni
selectdo
if tiitm001.unit =" m" then
xmeter = tiitm001.stoc/tiitm004.conv
endif
selectempty
xmeter = 0.0
endselect
endselect


Regards

I see I didn't read the SQL correctly in my first suggestion (deleted). After looking again on it, I don't see why you need another SQL inside the selectdo section. You can put it out side of the selectdo and use some variables.

Also, if can only get 1 record as output ad "as set with 1 rows" to the select.
select *
from
where
order by
as set with 1 rows
selectdo.

In your first select move the select on tiitm004 inside the If tiitm001.unit = "m" statement.


Are you aware that there is a dll that will return the unit conversion factor : tiitmdll0003?

Hello bdittmar.....

I interest with your trick (read with index), I want to your suggestion about my another script. I made a form for keyin lot number, from there I want to get an order number etc, like this

FORM
---------

Lot No = xxxxxxxxxxxxxxxx -----> input (field= tisfc950.clot)
Order Number = zzzzzz -----> display (field= tisfc950.orno)
Item = zzzzzzzzzzzzzzzz -----> display (field= tisfc950.item)
Item Name = zzzzzzzzzzzzzzzzzzz -----> display



field.tisfc950.clot:
after.input:
select tdltc001,clot, tdltc001.orno, tdltc001.item, tiitm001.item, tiitm001.dsca
from tdltc001, tiitm001
where tdltc001._index1 inrange {"","","",:tisfc950.clot}
and {"ZZZZZZ","ZZZZZZZZZZZZZZZZ","ZZZ",:tisfc950.clot}
and tdltc001.item = :tiitm001.item |||| what's difference between = and refers to
as set with 1 rows
selectdo
tisfc950.orno = tdltc950.orno
tisfc950.item = tdltc950.item
item.dsca = tiitm001.dsca

display("tisfc950.orno")
display("tisfc950.item")
display("item.dsca")
refresh()
selectempty
message("Lot Number not found !!")
input.again()
endselect


It is a right script ????????

thanks every body

First you can edit your previous thread and see how I added code tags into the post. It makes it easier to read.

And according to this " and tdltc001.item = :tiitm001.item " you already have the item? Is this correct? If I already have the item then my query would look like this:

select tdltc001.item, tdltc001.clot, tdltc001.oudt
from tdltc001
where tdltc001._index1 = {" ",:tiitm001.item}
and tdltc001.clot = :tisfc950.clot
as set with 1 rows

On our system the project is always empty - so I do not use inrange. If you do not have the item then that would change my query. To me it looks like your query just about does a full table scan with that inrange.