In the classic customer...orders..stuff/widgets model that I was
referring to then yes, there would be a third table with widgets per
order.
In actual fact there is only the 'CADs' table (a list Card Accepting
Devices which are unique) and a table which is a list of files collected
from each CAD (equiv to Orders ). To save on disk space I check and if
there is more than 150 files collected for any CAD then I delete the 50
oldest.
As the app is still in D1 which does not support nested quries, (uggh -
but D1 sure seems small & fast after D4), I had to resort to doing a
loop through the master detail table & triggering a (prepared) query to
count & delete the oldest files.
I have learnt from BJ's example though, & have flagged the code so in
the port to D4 it will be replaced by the single Delete query.
Grant Black
Software Developer
SmartMove (NZ) Ltd
Phone: +64 9 361-0219 extn 719
Fax : +64 9 361-0211
Email: [EMAIL PROTECTED]
> -----Original Message-----
> From: Carl Reynolds [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 12, 1999 4:56 PM
> To: Multiple recipients of list database
> Subject: RE: [DUG-DB]: Common SQL Query
>
> Well, if a "widget" is a field in the order table, then I guess so,
> although in that case COUNT(*) should be replaced by SUM(widget). I
> thought Grant meant there was a third table "widget" which contained
> multiple widget records per order record - ie. a "widget" was a record
> in the widget table - and we were only pruning widgets from orders
> that more than 100, not orders from customers with more than 100
> widgets over all their orders. Grant?
>
> Cheers,
>
> Carl Reynolds Ph: +64-9-4154790
> CJN Technologies Ltd. Fax: +64-9-4154791
> [EMAIL PROTECTED] DDI: +64-9-4154795
> PO Box 302-278, North Harbour, Auckland, New Zealand
> 12 Piermark Drive, North Harbour Estate, Auckland, NZ
> Visit our website at http://www.cjntech.co.nz/
>
> -----Original Message-----
> From: Aaron Scott-Boddendijk [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 12, 1999 3:45 PM
> To: Multiple recipients of list database
> Subject: Re: [DUG-DB]: Common SQL Query
>
>
> >> The psuedo would do something like:
> >> for each Customer in the Customer table do
> >> select count(widgets)
> >> from orders
> >> if count_widgets > 100 then
> >> Delete_widgets from orders where (some_field > 100_value)
>
> >> I guess I can do the first part with a parameterised query...
>
> >Query1:
> >select * from customers
>
> >Query2:
> >select orders.order_no, count(*) widget_count
> >from widgets, orders
> >where orders.order_no = widgets.order_no
> >and orders.customer_no = :customer_no
> >group by order_no
> >having widget_count > 100
>
> >Query2.DataSource := DataSource1; // DataSource1 has Query1 as its
> >DataSet
> >Query1.Open;
> >while not Query1.EOF do begin
> >// Can't remember whether you need to open and close Query2
> >explicitly
> >while not Query2.EOF do begin
> >// Delete 100 - x widgets from this order - for example
> >DeleteSomeWidgets( Query2order_no.AsInteger,
> > 100 - Query2widget_count.AsInteger );
> >Query2.Next;
> >end;
> >Query1.Next;
> >end;
>
> DELETE FROM
> ORDERS A
> WHERE
> 100<(
> SELECT
> COUNT(*)
> FROM
> ORDERS O
> WHERE
> O.CUST=A.CUST
> GROUP BY O.CUST
> )
>
> works doesn't it...
>
> IE Find the counts of records in the orders table for each customer
> and delete those orders for customers with more than 100 records.
>
> --
> Aaron Scott-Boddendijk
> Jump Productions
> (07) 838-3371 Voice
> (07) 838-3372 Fax
>
>
> ----------------------------------------------------------------------
> -----
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
