Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 7 of 7 total |
AS names |
Sun, Oct 16 2011 4:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Any chance of correlation names being allowed in the WHERE clause?
I'm working on my query generator to allow "virtual" columns so I've been experimenting to try and understand the rules. SO far I've come up with: 1. AS names can't be used in a where clause 2. AS names MAY be used in a GROUP BY or ORDER BY for any column 3. AS names MUST be used in a GROUP BY or ORDER BY when the expression is a subselect The only way I could think of testing HAVING worked when using the AS name but caused an AV in EDBManger when I tried to use the expression SELECT (SELECT 99 FROM Config RANGE 1 to 1) AS vNum, _HideAlarm AS Bert, EXTRACT(MONTH FROM _HideAlarm) AS vMonth FROM Calls WHERE EXTRACT(MONTH FROM _HideAlarm) = 10 --GROUP BY EXTRACT(MONTH FROM _HideAlarm) GROUP BY vMonth --ORDER BY EXTRACT(MONTH FROM _HideAlarm) HAVING (SELECT 99 FROM Config RANGE 1 to 1) > 10 ORDER BY vMonth Roy Lambert |
Wed, Oct 19 2011 1:08 PM | Permanent Link |
John Hay | Roy
> Any chance of correlation names being allowed in the WHERE clause? > I'm not convinced this is a good idea. You are in danger of getting ambiguous/unexpected results where an alias name clashes with a column name. You can get the results you want more explicitly using a derived table (I think!) eg SELECT vNum,vMonth FROM ( (SELECT 99 FROM Config RANGE 1 to 1) AS vNum, _HideAlarm AS Bert, EXTRACT(MONTH FROM _HideAlarm) AS vMonth FROM Calls WHERE EXTRACT(MONTH FROM _HideAlarm) = 10 --GROUP BY EXTRACT(MONTH FROM _HideAlarm) GROUP BY vMonth --ORDER BY EXTRACT(MONTH FROM _HideAlarm) ) T1 WHERE vNum > 10 ORDER BY vMonth John |
Wed, Oct 19 2011 1:34 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>> Any chance of correlation names being allowed in the WHERE clause? >> > >I'm not convinced this is a good idea. You are in danger of getting ambiguous/unexpected results where an alias name >clashes with a column name. You can get the results you want more explicitly using a derived table (I think!) eg > >SELECT vNum,vMonth FROM >( >(SELECT 99 FROM Config RANGE 1 to 1) AS vNum, >_HideAlarm AS Bert, >EXTRACT(MONTH FROM _HideAlarm) AS vMonth >FROM Calls >WHERE >EXTRACT(MONTH FROM _HideAlarm) = 10 >--GROUP BY EXTRACT(MONTH FROM _HideAlarm) >GROUP BY vMonth >--ORDER BY EXTRACT(MONTH FROM _HideAlarm) >) T1 >WHERE vNum > 10 >ORDER BY vMonth How does this stop the clash? More importantly the very thought of trying to alter my simple query generator to produce that makes me cringe. Roy Lambert |
Wed, Oct 19 2011 1:48 PM | Permanent Link |
John Hay | Roy
> How does this stop the clash? There was no clash in your example, but if you had a field AvNum in Calls and used it in a where clause there would be a clash. > More importantly the very thought of trying to alter my simple query generator to produce that makes me cringe. What else are you going to do in the dark nights ahead John |
Thu, Oct 20 2011 3:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>What else are you going to do in the dark nights ahead Start work on the ElevateDB replacement for my DBISAM powered mail / news client! Roy Lambert |
Fri, Oct 21 2011 4:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< The only way I could think of testing HAVING worked when using the AS name but caused an AV in EDBManger when I tried to use the expression >> This is now fixed for the next build (2.06 B2). EDB wasn't expecting the use of sub-queries in the HAVING clause and was not properly initializing some objects to handle this. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Oct 22 2011 4:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>This is now fixed for the next build (2.06 B2). EDB wasn't expecting the >use of sub-queries in the HAVING clause and was not properly initializing >some objects to handle this. Considering the difficulty I had in getting my head round creating something I'm not surprised ElevateDB wasn't expecting it. Roy Lambert |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |