At 02:23 PM 22/08/2005 -0400, you wrote:

>Hello,
>
>
>
>What I am attempting to do is to ascertain which suppliers in a table have
>overdue items on PO's. The way I am looking to do this is to pull the
>overdue items and filter the result so that only the first overdue item is
>shown for each supplier at which point I'll pull the supplier values into a
>string list thus giving a list of suppliers with overdue items. I am unsure
>of how to attain this result and would appreciate any assistance.

The concept of "first" in a relational database depends on how the data
would be ordered to achieve a cardinal list. Assuming all of the order
details in a purchase order hang off a purchase order header whose order
date covers all of the dependent details, one supposes that you have some
kind of PRIORITY, unique-per-order at the detail level, that would
represent the concept of "first", according to the design requirements.

select
poh.PORDER_NO,
poh.SUPPLIER_CODE,
poh.ORDER_DATE,
pod1.ITEM_CODE,
pod1.QUANTITY
from PORDER_HEADER poh
join PORDER_DETAIL pod1
on pod1.PORDER_NO = poh.PORDER_NO
where pod1.PRIORITY =
(
select min(pod2.PRIORITY) from PORDER_DETAIL pod2
where
pod2.PORDER_NO = pod1.PORDER_NO
AND pod2.DATE_RECEIVED is null
)

To get the strings into the output in the same pass, you'd need to do more
joining: to the SUPPLIER table to get the supplier name and to the ITEM
table to get the item name. A smoother way to do this would be to put the
"raw" query into a view and then join to the view to SUPPLIER and ITEM in
your run-time SQL to extract the strings.

A stringlist is a Delphi thing, off-topic for Firebird-support. Since I
see you cross-posted this question to the IBO list, I'll show you there how
you can get this data

./heLen