Hi Everyone, I am new to Baan Programming. I am developing a report which 4 tables and some fields from them

1. tdpur200 2. tdpur201 3. tdpur202 4. tdpur400
rqno rqno rqno orno
rdat item orno odat
rqst qoor

As you can observer, rqno field is common in 3 tables while orno is common in 2 tables. (I want rqst=Approved(requisition status))

I want a report having all these fields. I have written following query, but unfortunately its not giving what I want.

select
tdpur200.rqno, | Requisition
tdpur200.rdat, | Requisition Date
tdpur201.item, | Item
tdpur200.rqst, | Requisition Status
tdpur201.oamt, | Order Amount
tdpur400.orno, | Purchase Order
tdpur400.odat | Order Date

from
tdpur200, | Purchase Requisitions
tdpur201, | Purchase Requisition Lines
tdpur202, | Linked Requisition Line Data
tdpur400 | Purchase Orders
where
tdpur200.rqno = tdpur201.rqno and
tdpur200.rqst = tdpur.rqst.approved and
tdpur400.orno=tdpur202.prno and
tdpur202.rqno=tdpur201.rqno

Sorry if I have made mistakes, I have just started with my developments.

Thanks.

Make the query simpler to test so start with this:

select
tdpur200.rqno, | Requisition
tdpur200.rdat, | Requisition Date
tdpur201.item, | Item
tdpur200.rqst, | Requisition Status
tdpur201.oamt, | Order Amount
| tdpur400.orno, | Purchase Order
| tdpur400.odat | Order Date

from
tdpur200, | Purchase Requisitions
tdpur201 | Purchase Requisition Lines
| tdpur202, | Linked Requisition Line Data
| tdpur400 | Purchase Orders
where
tdpur200.rqst = tdpur.rqst.approved | Get headers approved
and tdpur200.rqno = tdpur201.rqno | Link to lines
| and tdpur202.rqno=tdpur201.rqno
| and tdpur400.orno=tdpur202.prno


Notice I put the ands on the next line - just my preference and it makes it easier to comment out lines. So test this and make sure it gives you what you expect. Then uncomment the tdpur202 join - again make sure it gives what you want. If not add what is needed to make it correct - more selection restrictions. Then add in the last table.

When I have queries that cause me problems I go back and add one table at a time to see which table causes my issue - from performance problems or index problems, etc.

PS - since I am not familiar with your data or tables I can really assist with the query.

can you try this?where
tdpur200.rqst = tdpur.rqst.approved | Get headers approved
and tdpur202.rqno = tdpur201.rqno
and tdpur202.pono = tdpur201.pono
and tdpur202.rqno refers to tdpur200
and tdpur202.prno refers to tdpur400

Hi Mark,

I am trying to get report of Pending Purchase Requisitions, whose status is approved but not converted.

Hi Benito,

I have used this, but it is giving the same output what I was getting through my query. There are about 100 such lines, but in my report I am getting only 4 lines.

Providing you the modified code again:



functions:

function read.main.table()
{
select
tdpur201.rqno, | Requisition
tdpur200.rdat, | Requisition Date
tdpur201.item, | Item
tdpur200.rqst, | Requisition Status
tdpur201.qoor, | Order Amount
tdpur400.orno, | Purchase Order
tdpur400.odat, | Order Date
tdpur400.otbp | Buy from business partners

from
tdpur200, | Purchase Requisitions
tdpur201, | Purchase Requisition Lines
tdpur202, | Linked Requisition Line Data
tdpur400 | Purchase Orders

where
tdpur201.rqno = tdpur200.rqno
and tdpur200.rqst = tdpur.rqst.approved and tdpur200.rqst<>tdpur.rqst.converted
and tdpur202.rqno=tdpur201.rqno
and tdpur400.orno=tdpur202.prno
and tdpur202.rqno refers to tdpur200
and tdpur202.prno refers to tdpur400


selectdo
rprt_send()

endselect
}

and tdpur200.rqst >= tdpur.rqst.approved and tdpur200.rqst<= tdpur.rqst.converted

Hi,

I will make my question a bit simple. Following are the 3 sample tables. Please let me know, how to join them.

Table1=(orno,qono,d,e)
Table2=(orno,x,y,z)
Table3=(qono,a,b,c)

Thanks.

I also think benito was close since you wanted approved but not converted:

tdpur200.rqst >= tdpur.rqst.approved and tdpur200.rqst<tdpur.rqst.converted

Notice I took out the = on the second part of the query.

If you add the tables in one at time as I recommended it might point out which one is causing you query to not return what you expect. Then you can check the joins and indexes on it or if necessary separate it out and put it inside the select do.

So things are a bit more complicated that what you posted - but assuming no link between table 2 and 3 I would separate it out. You don't always have to have everything in one query - and sometimes it is better to separate them out. It also depends on what the data is, what the report will look like on how the queries are designed and where I would put the report send statement.

Anyway here is how I build queries:

select table1.*, table2.* | Normally I use only fields I need not *
from table1, table2
where table1.orno = 1234 | I always put the table with conditions first
and table2.orno = table1.orno | and normally I use _index if available
selectdo
select table3.*
from table3
where table3.qono = :table1.qono
selectdo
endselect
endselect


Depending on the situation I might put table1 and table 3 in the outer query because they could be a 1 to 1 match. The performance guide from Infor really tells you when to separate queries - but I do it at times just to make it easier for someone else to understand what it is doing.

So back to the original post. I would do header and lines tables first to validate I got the correct records. And I would keep working it until I got it right. Then add in the rec data tdpur202 - make sure you were getting the right rqno and prno. If everything works then add in the tdpur400 table - again validating you got the right records. I do it this way especially when I am not familiar with the tables.

Hi Mark & Benito,

Thanks for all the guidance. I have solved it. I was comparing header field with lines and lines with header. So, it was giving me too many records in the records.

Mark, I tried to use your technique of using one table at a time. And it really helped. Thanks.

Will keep posting :)