Why can't I have an SQL statement like?

select  coll1+col2+col3+col4+col5 as MySum, MySum/5 as MyAvg from table1

I'd like to reference MySum in another expression. But this generates a syntax error because it doesn't know what MyAvg is. I end up having to repeat the calculation all over again for MyAvg. Since the computation had already been calculated for MySum, why do I have to calculate it again for MyAvg?

TIA
Barry

Barry

You can´t do it.

<<
select  coll1+col2+col3+col4+col5 as MySum, MySum/5 as MyAvg from table1
>>
The correct way is

select  coll1+col2+col3+col4+col5 as MySum, ((coll1+col2+col3+col4+col5)/5)
as MyAvg from table1

The coluumn MySum does not exist during the scan and it will be create in
the final result.

Eduardo

"Eduardo [HPro]" wrote:

<<You can´t do it.
The correct way is

select  coll1+col2+col3+col4+col5 as MySum, ((coll1+col2+col3+col4+col5)/5)
as MyAvg from table1

The coluumn MySum does not exist during the scan and it will be create in
the final result.>>

Correct. But it means repeating the expression. I have some really complicated expressions and I don't want to make a typo. These expressions are often updated and I have to repeat the changes to the rest of the expressions. Repeating expressions makes for a really large SQL statement.

The point I was trying to make is, the SQL parser knows the expression for MySum is "coll1+col2+col3+col4+col5" so why can't it just substitute the same expression when it sees MySum later on in the SQL statement? It would be like a Macro in assembler. It won't have to do any more than that.

Barry

Barry

> The point I was trying to make is, the SQL parser knows the expression for MySum is "coll1+col2+col3+col4+col5" so why
can't it just substitute the same expression when it sees MySum later on in the SQL statement? It would be like a Macro
in assembler. It won't have to do any more than that.

What would the parser do with the following

SELECT col1+col2+col3 AS col1,col1/5 AS average FROM Table

One way to do it might be

SELECT MySum,MySum/5 AS Average FROM
(SELECT col1+col2+col3+col4+col5 AS MySum FROM Table) T1

John

>One way to do it might be

>SELECT MySum,MySum/5 AS Average FROM
>(SELECT col1+col2+col3+col4+col5 AS MySum FROM Table) T1

Yes, I think you've got it.

This would allow me to do calculations and even summarize columns from a table only once, instead of doing several subselects into the same table. This will make queries much faster.

Is this syntax "select ... from <select statement>" an SQL standard? Because I haven't seen it before using EDB?

Barry

Where I have complex expressions which I might want to use regularly in the way you are expressing I do 1 of 2 things:

1. Create a COMPUTED column in the table, so that it is already there to use in any SELECT

ALTER TABLE
CREATE COLUMN "AvgValue" FLOAT COMPUTED ALWAYS as (Col1+Col2+Col3)/3 ...

--

2. Create a VIEW which contains the complex computed columns plus the original tables primary key field then I can JOIN to this VIEW and get the values without the typo problems.

CREATE VIEW TableAverages AS
SELECT
ID,
(Col1+Col2+Col3)/3 as "AvgValue"

FROM MyTable

--

I like VIEWS like this as they create a pallet of "ready summarized" data, which means if you have several people writing scripts they don't all write them referencing different values (for examples averages which are derived in different ways) also if Col1, 2 & 3's names change you only have to ALTER  the VIEW ... not rewrite a dozen bits of SQL which access those columns.

... overall you get just a bit more standardization & simplification than you would otherwise.


Adam

Adam

>1. Create a COMPUTED column in the table, so that it is already there to use in any SELECT

Neat idea. Does it hit performance much if you have a lot of them?

>2. Create a VIEW which contains the complex computed columns plus the original tables primary key field then I can JOIN to this VIEW and get the values without the typo problems.

Also neat.

Roy Lambert

Roy Lambert wrote:

>>1. Create a COMPUTED column in the table, so that it is already there to use in any SELECT

>Neat idea. Does it hit performance much if you have a lot of them?

Correct me if I'm wrong, since EDB doesn't physically store Computed column data in the table, then there shouldn't be a performance hit when creating computed columns for intermediate calculations. (Nice idea!)
The table data size should stay the same.

I would probably prefix these column names with "ct_" as in "ct_Avg_Ages" so I know it is a computed temp column. I don't know if you guys have standard naming conventions for tables, columns, computed columns, generated columns, triggers or views. But if there is a standard, would you mind posting  a link to it? Or paste it here? - Thanks

>2. Create a VIEW which contains the complex computed columns plus the original tables primary key field then I can JOIN to this VIEW and get the values without the typo problems.

+1

It's too bad Tim didn't allow for user defined function calls in a computed column like you can in a generated column, but I suspect he did it this way for speed, which I understand.

Barry

Barry

>>Neat idea. Does it hit performance much if you have a lot of them?
>
>Correct me if I'm wrong, since EDB doesn't physically store Computed column data in the table, then there shouldn't be a performance hit when creating computed columns for intermediate calculations. (Nice idea!)
>The table data size should stay the same.

The reason I was asking is that the column value is going to have to be computed. I don't know if that's only done when the column is accessed or when the row is accessed. If the latter then scrolling through a table in a grid could have a performance hit. I don't know.

>I would probably prefix these column names with "ct_" as in "ct_Avg_Ages" so I know it is a computed temp column. I don't know if you guys have standard naming conventions for tables, columns, computed columns, generated columns, triggers or views. But if there is a standard, would you mind posting a link to it? Or paste it here? - Thanks

My convention is:

All column names start with underscore followed by a capital letter. That way I don't have to bother about hitting a reserved word and, since I use tables a lot in my apps and use persistent fields table_Field rather than tableField makes it easier to visually differentiate

Within Delphi any OnCalc fields start _x

In ElevateDB computed columns start _c, generated ones _g

>It's too bad Tim didn't allow for user defined function calls in a computed column like you can in a generated column, but I suspect he did it this way for speed, which I understand.

The reason has been explained a couple of times and its more to do with how the SQL is "compiled" and bound in (or so my memory says)

Roy Lambert

>>Correct me if I'm wrong, since EDB doesn't physically store Computed column data in the table

When I add computed column to a table it appears exactly as an ordinary column & is generated/updated (with a very very small performance hit) when the fields which it references change, otherwise (I believe) there is no work done on the server, aside from just returning the already-stored computed value.

A convention I have is that all my tables have a computed field called "Name" (I don't bother with underscores etc., as I always put field names in double-quotes, so reserved words don't matter, I can have fields called Sum or Table or Select if I want (though I don't do this!) since the double quotes wash out the keyword reference.

The Name column might consist of "FirstName" + ' ' + "LastName" + ' (' + "JobTitle" + ') ' for a table with people data in it, or it might consist of CAST(SalesNumber as VARCHAR(30)) + ' ' + CAST(DateDone as VARCHAR(10)) for a sales database.

This convention means I can always write SQL:

SELECT Name, ID FROM [TableName] WHERE [conditions]

and return a useful list which might not be beautiful, but will list out the records in an identifiable way.

This is really useful for speeding up code on the application side of things, as I can hard-code this SQL for any table in the application.