In Vfp6 I have a query which looks like
Parent Table Dpsa - Primary Key - UnikID
Child Table Saleadv - Foreign Key - UnikID
Table Buyer - Connected to Parent Table by field Bcode
For each record in the parent table I want one record back where the child
records will come as sum.
Select a.unikid, a.gpdate, a.gpno, b.sdate, c.bname, a.invoiceno, a.grade,
a.prefix, a.suffix,;
sum(b.soldkg) as soldkg, Round(sum(b.soldkg*b.kgprice)/sum(b.soldkg),2) as
kgprice, sum(b.saleamt) as sasaleamt,;
000000.000 as ackg, 000.00 as acprice,0000000.00 as acsaleamt,000.00 as
expperkg,;
a.gpid, a.bcode,;
Thisform.ReportHeader as ReportHeader,;
Thisform.reportperiod as reportperiod;
from Dpsa a,saleadv b,Buyer c;
where a.unikid = b.unikid;
and a.bcode = c.bcode;
and a.gpdate between Thisform.sdate and Thisform.edate;
and a.unikid in (Select unikid from auctsales);
group by 1;
order by 5,2,3,6;
into cursor C1
Worked fine. Come Vfp8
Select a.unikid, a.gpdate, a.gpno, max(b.sdate) as sdate, c.bname,
a.invoiceno, a.grade, a.prefix, a.suffix,;
sum(b.soldkg) as soldkg, Round(sum(b.soldkg*b.kgprice)/sum(b.soldkg),2) as
kgprice, sum(b.saleamt) as sasaleamt,;
000000.000 as ackg, 000.00 as acprice,0000000.00 as acsaleamt,000.00 as
expperkg,;
a.gpid, a.bcode,;
Thisform.ReportHeader as ReportHeader,;
Thisform.reportperiod as reportperiod;
from Dpsa a,saleadv b,Buyer c;
where a.unikid = b.unikid;
and a.bcode = c.bcode;
and a.gpdate between Thisform.sdate and Thisform.edate;
and a.unikid in (Select unikid from auctsales);
group by 1,2,3,5,6,7,8,9,17,18;
order by 5,2,3,6;
into cursor C1 Readwrite
The group by became complicated. For the fourth field I had to use a max as
a group by on that field can lead to multiple records as table B can have
many records corressponding to Table a with different 'sdate'.
If you all can help me as to how I should go about this it will be real
nice.
Right now the only way I see is to have a first query as:
Select a.unikid, sum(b.soldkg) as soldkg,;
Round(sum(b.soldkg*b.kgprice)/sum(b.soldkg),2) as kgprice,;
sum(b.saleamt) as sasaleamt,;
from Dpsa a,saleadv b;
where a.unikid = b.unikid;
and a.gpdate between Thisform.sdate and Thisform.edate;
and a.unikid in (Select unikid from auctsales);
group by 1;
into cursor C1 Readwrite
Then create cursor C2 with all the fiels that I want from Table1 and C1
Create cursor C2 (-----------)
Select C1
Scan
lnUnikid = C1.unikid
Select Dpsa
If Seek(lnUnikid,"Dpsa","unikid")
Scatter Memvar
Select C2
Gather Memvar in C2
Endif
Endscan
Coding becomes much more but maybe simpler to read and correct later on.
Ajoy Khaund
Neamati Road
Near Bhogdoi Bridge
Jorhat 785 001
Assam, India
Tel: 91-376-2351288
Cell: 91-94350-92287
Mail: [EMAIL PROTECTED]
Mail: [EMAIL PROTECTED]
"Walking on water and developing software from a specification are easy if
both are frozen."
- Edward V. Berard, "Life-Cycle Approaches"
----- Original Message -----
From: "Ed Leafe" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, September 03, 2007 7:24 AM
Subject: Re: SQL "improvements"
On Sep 2, 2007, at 7:58 PM, Ricardo Aráoz wrote:
> select EmployeeId, EmpName, sum(salary) ;
> from Employee ;
> group by EmployeeId
>
> This would have sense but it is easily done with :
To a human, perhaps, but to a database engine, there is no
constraint that the ID and name be a 1:1 pair.
> select EmployeeId, max(EmpName) as 'EmpName', sum(salary) ;
> from Employee ;
> group by EmployeeId
And why is is that any simpler (or better, or cleaner, or...) than:
select EmployeeId, EmpName, sum(salary) ;
from Employee ;
group by EmployeeId, EmpName
If I inherited code that used max() of a name, red flags would be
raised in my mind as to the original coder's sanity. ;-)
-- Ed Leafe
-- http://leafe.com
-- http://dabodev.com
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
