Hi.
On Thu, Jan 18, 2001 at 03:25:24PM -0500, [EMAIL PROTECTED] wrote:
> Sorry, I was away for a bit, and got side tracked!
>
> I have a tiered Access application running over a WAN, and
> performance has turned out to be an issue. But to convert the
> Access queries into something that MySQL can understand takes a
> while. I started converting them before, but ran into a sub querie,
> and couldn't think of an alternate way to do it, so I gave up. The
> queries are pretty hefty (by my standards anyway - since I don't
> really know SQL very well). I'll paste one here so you can see what
> I'm working with. It'll take me a day or two to get it to that
> point again. If you're still willing to help, I'll be happy to do
> it and post it to the group.
>
> Thanks!
> Mark
>
> PS. Here's the Access SQL querie that I have to convert...
Sorry, but the query you quoted doesn't use sub-queries, does it? So what is the
problem with. After reformatting and throwing away a lot of redundant
parenthesis I get something like
SELECT CensusData.SiteKey, CensusData.Date, CensusData.Testing,
LicensedBeds.SNF_Beds, LicensedBeds.SCNF_Beds,
LicensedBeds.SubAcute_Beds,
[CensusData]![Mix_SNF_Private] + [CensusData]![Mix_SNF_Medicare] +
[CensusData]![Mix_SNF_Medicaid] + [CensusData]![Mix_SNF_Ins_Other]
AS Total_SNF,
[CensusData]![Mix_SCNF_Private] + [CensusData]![Mix_SCNF_Medicare] +
[CensusData]![Mix_SCNF_Medicaid] + [CensusData]![Mix_SCNF_Ins_Other]
AS Total_SCNF,
[CensusData]![Mix_SubAcute_Private] +
[CensusData]![Mix_SubAcute_Medicare] +
[CensusData]![Mix_SubAcute_Medicaid] +
[CensusData]![Mix_SubAcute_Ins_Other] AS Total_SubAcute,
IF([SNF_Beds]=0,0,Int([Total_SNF]/[SNF_Beds]*10000)/100) AS SNF_Per,
IF([SCNF_Beds]=0,0,Int([Total_SCNF]/[SCNF_Beds]*10000)/100) AS SNCF_Per,
IF([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[SubAcute_Beds]*10000)/100)
AS SubAcute_Per,
[SNF_Beds] + [SCNF_Beds] + [SubAcute_Beds] AS Total_Beds,
IF([SNF_Beds]=0,0,Int([Total_SNF]/[Total_Beds]*10000)/100)
AS SNF_Per_Total,
IF([SCNF_Beds]=0,0,Int([Total_SCNF]/[Total_Beds]*10000)/100)
AS SNCF_Per_Total,
IF([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[Total_Beds]*10000)/100)
AS SubAcute_Per_Total,
[Total_SNF] + [Total_SCNF] + [Total_SubAcute] AS Total_Census,
IF([Total_Beds]=0,0,Int([Total_Census]/[Total_Beds]*10000)/100)
AS Total_Per
/* swapped the order of the INNER JOINS to get rid of the parenthesis */
FROM SiteLookup
INNER JOIN FilterMaxDataEntries
ON SiteLookup.SiteKey = FilterMaxDataEntries.SiteKey
INNER JOIN CensusData
ON FilterMaxDataEntries.SiteKey = CensusData.SiteKey AND
FilterMaxDataEntries.Date = CensusData.Date AND
FilterMaxDataEntries.MaxOfTime_Stamp = CensusData.Time_Stamp
INNER JOIN LicensedBeds
ON LicensedBeds.LicensedBedsKey = CensusData.LicensedBedsKey
WHERE CensusData.SiteKey = [Forms]![Gen_OneFacility]![Site] AND
CensusData.Testing = False AND
[CensusData]![Date] >= [Forms]![Gen_OneFacility]![StartDate] AND
[CensusData]![Date] <= [Forms]![Gen_OneFacility]![EndDate]
ORDER BY CensusData.Date;
Well, you still have to change the [some]![thing] syntax to something
appropriate and IIRC use FLOOR() instead of INT().
If you encounter other problems, please explain.
Bye,
Benjamin.
PS: Please format the query yourself next time, at least a little bit. It is
quite time consuming to scan a bunch of long lines with > 400 chars.
> >>> Ryan Wahle <[EMAIL PROTECTED]> 01/16/01 04:51PM >>>
>
> What's your subquery and we can help you convert it to a join.
>
> On 16 Jan 2001 15:59:48 -0500, Mark Marshall wrote:
> > Hi all,
> >
> > Is there any kind of schedule that says when the next MySQL version will be
>available? I'd like to utilize MySQL here, but I can't really start to seriously
>work on it until sub-queries are possible.
[...]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
