|
Home > Archive > dBASE SQL Servers > February 2006 > Are Nested selects possible?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Are Nested selects possible?
|
|
| John Marshall 2006-02-02, 8:23 pm |
| Are nested Select statements possible in BDE flavor SQL?
Here's the code I'm attempting to run. I'm trying to sum an amount field in a related table (T) and bring it into the sales summary query (S)
SELECT sum(s.cctips) as mcctip, sum(s.cctipsls) as mccsls,
sum(s.recip) as mrecip, sum(s.contrib) as mContrib,
sum(s.sales) as msales, sum(s.dectips) as mdectips,
t.rtamt as ramt
FROM 'SLSNTIP.DBF' S
JOIN (select recip, sum(amt) as rtamt from 'TIP.DBF'
where mdate >=: mSdate and
mdate <=: mEdate and
recip =:mEmpno
group by recip) T
ON (t.recip=s.employee)
WHERE s.mdate >=: mSdate and
s.mdate <=: mEdate and
s.employee =: mEmpno
I just stuffed some sample figures in to the params
params["mSdate"] = {12/15/2005}
params["mEdate"] = {12/20/2005}
params["mEmpno"] = "9200"
Thanks in advance!
JM
| |
| Mark Ziburis 2006-02-03, 8:23 pm |
| Try the following. I believe that it will accomplish your goal.
Mark Ziburis
SELECT sum(s.cctips) as mcctip,
sum(s.cctipsls) as mccsls,
sum(s.recip) as mrecip,
sum(s.contrib) as mContrib,
sum(s.sales) as msales,
sum(s.dectips) as mdectips,
sum(t.amt) as ramt,
t.recip
FROM SLSNTIP.DBF S
,TIP.DBF T
where t.mdate between :mSdate and :mEdate
and t.recip = :mEmpno
and s.mdate = t.mdate
and s.mdate = t.mdate
and s.employee = t.recip
group by t.recip
| |
| John Marshall 2006-02-04, 7:23 am |
| Thanks Mark, that did work. May I ask that you consider the following complication ...
The TIP table has a logical field called "Allocated." It would be a subset of the sum(t.amount). I'd like to add this to the result set as well. I have found it impossible to return two sums from the same table. Perhaps you can guide me.
PS: Does dBase/BDE support sub queries?
Thanks,
JM
Mark Ziburis Wrote:
> Try the following. I believe that it will accomplish your goal.
>
> Mark Ziburis
>
> SELECT sum(s.cctips) as mcctip,
> sum(s.cctipsls) as mccsls,
> sum(s.recip) as mrecip,
> sum(s.contrib) as mContrib,
> sum(s.sales) as msales,
> sum(s.dectips) as mdectips,
> sum(t.amt) as ramt,
> t.recip
>
> FROM SLSNTIP.DBF S
> ,TIP.DBF T
>
> where t.mdate between :mSdate and :mEdate
> and t.recip = :mEmpno
> and s.mdate = t.mdate
> and s.mdate = t.mdate
> and s.employee = t.recip
>
> group by t.recip
>
>
| |
| Mark Ziburis 2006-02-06, 11:23 am |
| I take this as being what you want to accomplish. This will separate
the sums betwen those with allocation being true and false.
To answer your question about subqueries, I don't see why it won't
support them.
Mark
SELECT sum(s.cctips) as mcctip,
sum(s.cctipsls) as mccsls,
sum(s.recip) as mrecip,
sum(s.contrib) as mContrib,
sum(s.sales) as msales,
sum(s.dectips) as mdectips,
sum(t.amt) as ramt,
t.recip,
t.allocated
FROM SLSNTIP.DBF S
,TIP.DBF T
where t.mdate between :mSdate and :mEdate
and t.recip = :mEmpno
and s.mdate = t.mdate
and s.mdate = t.mdate
and s.employee = t.recip
and allocated = true
group by t.recip,
t.allocated
Union
SELECT sum(s.cctips) as mcctip,
sum(s.cctipsls) as mccsls,
sum(s.recip) as mrecip,
sum(s.contrib) as mContrib,
sum(s.sales) as msales,
sum(s.dectips) as mdectips,
sum(t.amt) as ramt,
t.recip,
t.allocated
FROM SLSNTIP.DBF S
,TIP.DBF T
where t.mdate between :mSdate and :mEdate
and t.recip = :mEmpno
and s.mdate = t.mdate
and s.mdate = t.mdate
and s.employee = t.recip
and allocated = false
group by t.recip,
t.allocated
Order by 9,8
/* Ordered by allocation value then by recip. This is useful when
column names are different between the two parts of a union (and for
less typing). */
|
|
|
|
|