Helen,



I forgot to indicate the purpose of this. The application will be producing
a report for each Supplier showing all overdue items. The reports will be
faxed/emailed to the respective supplier asking for a status on the
indicated items.



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]