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

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

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

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

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

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

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