Good day to All,

Can anyone guide me on how can I build a SQL statement to extract the vendor code that we last purchase from (latest order date) for every item from table tdpur401 (Purchase Order Line)

The SQL should return the following fields.

Item, vendor, last purchase date and the PO number.

If any item having POs from multiple vendor, just return one record with the latest purchase date.

Many thanks in advance.

Well since I don't know the tables you could try something like:

select tdpur041.item,tdpur041.suno,max(tdpur041.odat)
from tdpur041
group by tdpur041.item, tdpur041. suno


This is only a sample of one way to extract items by supplier for the largest order date. This is from 4c4 and our purchase order line table. You should be able to apply the same concept to your table.

Hi Mark,

Thanks for your reply.

I have written the same query as yours before. This query is good only if I want to report the latest PO date for the item/vendor combination.

My requirement is to report latest PO date for every item with the vendor return along with the query.

Example:

PO----------PO_Date---------Item--------Vendor
PO1---------7-May-11--------A2----------V3
PO2---------6-Jun-11--------A2-----------V1
PO3---------7-Jul-11---------A1-----------V1
PO4---------3-Aug-11--------A1-----------V2
PO5---------3-Sep-11--------A2-----------V3
PO6---------6-Sep-11--------A1-----------V1

The correct query should return the following :

Item-----Lastest_PO_Date-------Vendor
A1-------6-Sep-11-------------- V1
A2-------3-Sep-11---------------V3

Hi,

I guess below code will solve your problem,

select distinct(tdpur401.item), tdpur401.otbp, tdpur401.odat
from tdpur401
order by tdpur401.odat desc
selectdo
endselect

Regards,
Gaurav

Does distinct work like that is an LN? In 4c4 what I would do is nested queuries. The outer query would get the distinct item and the inner query(using an alias) would then select the suno and date ordering by the date descending using as set with 1 rows. You can do the same in easy sql. In the report in the detail.1 before layout you could do the inner select.

Hi,

just a suggestion, but maybe much faster with a direct query to your Oracle DB using a analytic function:


select t$item as ITEM, t$orno as PO, t$otbp as VENDOR, t$odat as LPURDAT from
(
select t$item, t$orno, t$otbp, t$odat, row_number() over (PARTITION BY t$item order by t$odat desc) X
from baandb.ttdpur401123
)
where X = 1


baandb= your DB
123=your Baan companynr.

Regards,
Juergen

Hi,

You can use

Select item,vendor,date
from tablename
group by item,vendor,date
order by date desc
selectdo

endselect





Might help

Hi,

sorry, but I think this will not give the output requested by teddybear

Originally posted by dhruv_x0

Select item,vendor,date
from tablename
group by item,vendor,date
order by date desc
selectdo

endselect