Helen,



Thanks for the response. Believe I see your approach but I am still confused
somewhat as I have not done much with programming / SQL in several years and
struggling a bit with re-wetting my feet. I imagine I will have to post some
sample data and table structure to get a response that I will be able to
come to grips with easily. I have not dome much with views as of yet but
already have a number of joins going on.



Oh, and the post on the IBO forum was a similar but different question. That
was to remove redundancy where the same record is displayed more than once
this was accomplished through the use of DISTINCT. The current one deals
with a supplier having multiple overdue items from the same or different
purchase orders. This could be the same item on different orders or
different items on one order. The idea was to display only the first item
for each supplier so that I have the supplier name listed only one time in
the result set.



I am dealing with a database put together by someone who is no longer around
and I apologize if my SQL is a bit hack and slash. If you are wondering how
I decided on what type of joins to use, I simply used a script from the
current application. Oh, btw, there are many more fields in the tables, I
simply included the ones that I am using)



// TABLE STRUCTURE



po_hdr (

supplier_id, -- Unique Supplier Identifier

po_no, -- Unique PO Identifier

division_id -- Division Identifier

)



po_line (

po_line_uid, -- Unique Identifier for record in table

po_no, -- Unique PO Identifier

inv_mast_uid, -- Unique Item ID

pricing_unit, -- Name of Pricing Unit

pricing_unit_size, -- Size of Pricing Unit in Base Unit

unit_quantity, -- Number of Units Ordered

qty_ordered, -- Quantity ordered in Base Unit

qty_received, -- Quantity received in Base Unit

date_due -- Expected Date

)



address (

id, -- Supplier ID, Vendor ID, Customer ID,
etc.

name -- Name of Supplier, etc.

)



inventory_supplier (

supplier_id, -- Supplier ID

supplier_part_no, -- Supplier Part No

division_id -- Division Identifier

)



inv_mast (

item_id -- Unique Item ID

)





// SQL STATTEMENT THAT PULLS A COMPLETE LISTING OF OVERDUE ITEMS



SELECT DISTINCT

address.name,

po_hdr.supplier_id,

po_line.po_line_uid,

po_line.pricing_unit,

po_line.pricing_unit_size,

po_line.unit_quantity,

po_line.po_no,

inventory_supplier.supplier_part_no,

inv_mast.item_id,

po_line.qty_ordered,

po_line.qty_received,

po_line.date_due

FROM po_line



INNER JOIN po_hdr ON (po_hdr.po_no = po_line.po_no)



LEFT JOIN oe_line_po ON oe_line_po.po_no = po_line.po_no

AND oe_line_po.po_line_number = po_line.line_no



INNER JOIN inv_mast ON (inv_mast.inv_mast_uid = po_line.inv_mast_uid)



LEFT JOIN inventory_supplier ON inventory_supplier.supplier_id =
po_hdr.supplier_id

AND
inventory_supplier.division_id = po_hdr.division_id

AND
inventory_supplier.inv_mast_uid = po_line.inv_mast_uid



INNER JOIN address ON (address.id = po_hdr.supplier_id)



WHERE po_line.delete_flag = 'N'

AND po_line.cancel_flag = 'N'

AND po_line.qty_ordered > po_line.qty_received

AND po_line.date_due < CURRENT_TIMESTAMP



ORDER BY po_line.po_no ASC







// SAMPLE RECORD RETURNED BY ABOVE SQL



Name HYDRAULICS CO.

Supplier ID 104129

PO Line UID 3782

Pricing Unit CASE

Pricing Unit Size 1.0000

Unit Quantity 1.000000000

Unit Size 50.0000

PO Number 1000561

Supplier Part No M21593

Item ID HYD M21593

Qty Ordered 50.000000000

Qty Received 5.000000000

Date Due 3/4/2005 00:00:00.000



Thanks again for your time and patience.



Regards,

Terry



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Tuesday, August 23, 2005 1:48 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How to display only the first instance of a
value



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





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS


Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+security&w3=Computer+technical+support&w4=Computer+training&w5=F
ree+computer+technical+support&c=5&s=138&.sig=77sbeaNS_CB99ND1NpQ02w>
support

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+security&w1=Technical+support&
w2=Computer+security&w3=Computer+technical+support&w4=Computer+training&w5=F
ree+computer+technical+support&c=5&s=138&.sig=h-vpfglcVDyMom6ZGRj-AA>
security

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+security&w3=Computer+technical+support&w4=Computer+trai
ning&w5=Free+computer+technical+support&c=5&s=138&.sig=0PtYe-giICy1KkECNuG2E
A> technical support


Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+training&w1=Technical+support&
w2=Computer+security&w3=Computer+technical+support&w4=Computer+training&w5=F
ree+computer+technical+support&c=5&s=138&.sig=psUjUcHSklN7Uvu_cJch-g>
training

Free
<http://groups.yahoo.com/gads?t=ms&k=Free+computer+technical+support&w1=Tech
nical+support&w2=Computer+security&w3=Computer+technical+support&w4=Computer
+training&w5=Free+computer+technical+support&c=5&s=138&.sig=isTctikpuewr3b60
7bkoUA> computer technical support





_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]