I am using the following query in my Program Script. While running the session, if I enter values for citt.f and citt.t as 1 and 2 respectively or 2 and 2, the session returns results to the report. But if I write 1 and 1, the session runs as an infinite loop. What is wrong in this query?

select tibom010.*, tcibd001.kitm:sitm.kitm, tcibd001.dsca:sitm.dsca,
tcibd001.eitm:sitm.eitm, tcibd001.umer:sitm.umer,
tcibd001.cuni:sitm.cuni, tcibd001.txta:sitm.txta
,tcibd004.*,tccom100.*, tiedm010.*, tdipu001.*
from tibom010, tcibd001, tcibd004, tccom100, tiedm010, tdipu001
where tibom010._index1 > {:tibom010.mitm, :tibom010.pono, :tibom010.seqn}
and tibom010.sitm refers to tcibd001
and tibom010.sitm = tcibd004.item
and tibom010.sitm refers to tiedm010.eitm
and tccom100.bpid = tcibd004.bpid
and tcibd004.bpid = tdipu001.otbp
and tcibd004._index1 between { :citt.f } and { :citt.t }

I am using the following query in my Program Script. While running the session, if I enter values for citt.f and citt.t as 1 and 2 respectively or 2 and 2, the session returns results to the report. But if I write 1 and 1, the session runs as an infinite loop. What is wrong in this query?

select tibom010.*, tcibd001.kitm:sitm.kitm, tcibd001.dsca:sitm.dsca,
tcibd001.eitm:sitm.eitm, tcibd001.umer:sitm.umer,
tcibd001.cuni:sitm.cuni, tcibd001.txta:sitm.txta
,tcibd004.*,tccom100.*, tiedm010.*, tdipu001.*
from tibom010, tcibd001, tcibd004, tccom100, tiedm010, tdipu001
where tibom010._index1 > {:tibom010.mitm, :tibom010.pono, :tibom010.seqn}
and tibom010.sitm refers to tcibd001
and tibom010.sitm = tcibd004.item
and tibom010.sitm refers to tiedm010.eitm
and tccom100.bpid = tcibd004.bpid
and tcibd004.bpid = tdipu001.otbp
and tcibd004._index1 between { :citt.f } and { :citt.t }


Hello,

your profile says B4, the tables are LN, so update your profile, please.

tcibd004._index1 is citt, bpid, item

so try:

inrange {:citt.f, :businespartner, :item}
and {:citt.t, :businespartner, :item}

e.g.

inrange {:citt.f , :tccom100.bpid, :tibom010.sitm}
and {:citt.t, :tccom100.bpid, :tibom010.sitm}

Regards

Hi bdittmar
Thanks for your reply.

If I check as if you have said, the report has has only one tibom010.sitm and there is no data for tcibd004.aitc, tdipu001.otbp, tiedm010.eigp and tcibd004.citt
I am also checking in the query as
tcibd004.bpid = tdipu001.otbp
where can I add this?

Hi bdittmar
Thanks for your reply.

If I check as if you have said, the report has has only one tibom010.sitm and there is no data for tcibd004.aitc, tdipu001.otbp, tiedm010.eigp and tcibd004.citt
I am also checking in the query as
tcibd004.bpid = tdipu001.otbp
where can I add this?

Hello,

it has been an example !
Based on what you want to achive, you have to built your query.

.... {:citt.f, :tdipu001.otbp .......


If you need more and detailed help, describe your .

Regards

Hi bdittmar
I need to display tibom010.sitm, sitm.revi, sitm.dsca, tccom100.bpid,tccom100.nama,tcibd004.aitc, tdipu001.otbp, tiedm010.eigp and tcibd004.citt when the user enters a range for mitm and citt.
Actually I got my job recently and I am completely new to Baan. The person who has worked before has created a session and I need to remove some fields and add some new fields in the program script
Can you help me with this?
Thanks.
BR
mnmhema

Based off your original post and what bernd is saying I think your query should look like:

select tibom010.*, tcibd001.kitm:sitm.kitm, tcibd001.dsca:sitm.dsca,
tcibd001.eitm:sitm.eitm, tcibd001.umer:sitm.umer,
tcibd001.cuni:sitm.cuni, tcibd001.txta:sitm.txta
,tcibd004.*,tccom100.*, tiedm010.*, tdipu001.*
from tibom010, tcibd001, tcibd004, tccom100, tiedm010, tdipu001
where tibom010._index1 > {:tibom010.mitm, :tibom010.pono, :tibom010.seqn}
and tibom010.sitm refers to tcibd001
and tibom010.sitm = tcibd004.item
and tibom010.sitm refers to tiedm010.eitm
and tccom100.bpid = tcibd004.bpid
and tcibd004.bpid = tdipu001.otbp
and tcibd004._index1 inrange {:citt.f, tdipu001.otbp, tibom010.sitm} and
{:citt.t, tdipu001.otbp, tibom010.sitm}


When working with queries like this it is sometimes best to add one table at a time to make sure they are correct. Then when it does something like a cartesian product you can tell it is the last table you selected. If you notice I did not put a : on some of the fields for the example. That little : can cause some interesting problems at times.

Hi Mark
When I enter 1 and 1 for citt.f and citt.t respectively with your query, I get an endless loop. I need to end Baan in the Task Manager. There is no record for citt= 1 in the table.

Does the query run, not work, but just run to completion if you pull out this part of the querey - tcibd004._index1 inrange {:citt.f, tdipu001.otbp, tibom010.sitm} and
{:citt.t, tdipu001.otbp, tibom010.sitm}?

If it does then you know the problem is this line, if it still has an issue - then work your way backwards removing one table at a time. Or you can start the query from scratch adding one piece at a time. It will help you see what is happening at can look at the data that is returned and how long it takes.

There are two possible reasons for it not returning - 1 is a cartesian product and 2 is just a poor query that returns so many rows you just have to wait. For example - this statement in the query where tibom010._index1 > {:tibom010.mitm, :tibom010.pono, :tibom010.seqn} will return one heck of a lot of data. It is too tough to visualize what you are trying to do and I do not know your data. When I see that line I think of exploding the BOM and it would have "as set with 1 rows" in it.

I see a few issues with the original SQL statement. If you are rather new to Baan programming, it is best to break the SQL statement into separate queries/if statements until you get the desired result. Then, you can start to consolidate the statements back into a single one. That would have identified a few issues in your query:

You are asking for 'tdipu001' data yet you never select anything from the table. You should always try to have the same reference in 'select', 'from', and at least one line in 'where'. If you have a table in 'where' that isn't in 'from', you will encounter a full table read (all records) on that table for every record that is read from other tables. You are fortunate if you leave something out and get a compile error. Much harder to identify when the compile works but the execution doesn't.

I also try to keep the fields in 'select' as simple as possible. If you aren't going to use the field, remove it from the 'select' clause. If you remove everything for that table and don't need it to test a condition, remove it from the 'from' clause.

I would start by writing a select statement for tibom010 (only), then start putting select statements for the other tables within its selectdo. This will force you to think about how to define the relationships between the tables and will omit those that are not needed. Once it runs, you can consolidate the selects to the parent select statement using '=' or 'refers to'. I have been doing Baan programming for 15 years and will break up/reconstruct SQL statements when they get complicated. I will often leave statements as separate SQL structures instead of trying to do all in one to make it easier for the folks that have to maintain the code to understand what is happening. It also removes doubt about how the portingset builds complex queries for the database.

I've attached the program script file. It will be a great help, if anyone will correct it according to my need.

My recommendation is to restore the original code. Then run it continually in debug mode until you understand the flow and how it works with the original code.

Next after becoming familiar with the original code decide where you need this new code. Start building it there. You have this code all over the place and I am not sure if it is needed or not. So decide where this code needs to code? Is on the next line, next make item, top level, etc. If it is just to do more reporting I would create a subquery in the loop for just the tables needed. Something that would not impact the flow of exploding the bom.

If not reporting then decide if it is actually something to restrict the records that get pulled - again determine at what level - top level, make, line. Then build it in slowly(one table at a time) - continuing to run it in debug mode. Then you can see what is actually causing it to take so long.

A BOM explosion program is not something easy to start with - so run it until you understand the stack tracking, how it gets next line, next build item, how it knows when to decrease a level, etc. This is not something you want to just throw at a beginner. I will not attempt to modify the code because I do not know your systems, your data or even what you are trying to accomplish. It is not something that is easy to accomplish through the board.