I realize this is fiddly, but I think it would be useful. Happy to hear others say otherwise

SELECT
 Field1,
 SUM(Field2),
 MAX(Field3),
 COALESCE(Field4, 0)
FROM Table

Names of resulting Query Fields:

Field1, Expression, Expression1, Expression2

Wouldn't it be nicer if the names automatically came out:

Field1, SUM_Field2, MAX_Field3, COALESCE_Field4

It might even be better if the last item above was just "Field4"

I know I can write:

SELECT
 Field1,
 SUM(Field2) as SUM_Field2,
 MAX(Field3) as MAX_Field3,
 COALESCE(Field4, 0) as COALESCE_Field4
FROM Table

But it takes time ... which is my enemy.

Sorry I missed the GROUP BY in the above examples ... I wish it was an intentional mistake!

Adam


OK - now prove you've thought it through

select
SUM(_Fee) + SUM(_VATAmount),
SUM(_Fee) / SUM(_VATAmount),
SUM(_Fee + _VATAmount)
From Invoices

Roy

ps

Adam,

<< I realize this is fiddly, but I think it would be useful. Happy to hear others say otherwise >>

It would be useful, but, unfortunately, it also might break a lot of applications that may be relying on the existing auto-naming conventions.

Tim Young
Elevate Software
www.elevatesoft.com

Roy

I wasn't asking for it to work in all possible instances Roy, just in those Tim could be bothered to code for.

In the example you give it would still be possible to create fieldnames in the final dataset ... "SumFieldAPlusSumFieldB" ... it would just be "fiddly" to write behind the scenes in the code as there would be nasty parsing once expressions became complex, in that case it could revert back to "Expression".

Adam

>I wasn't asking for it to work in all possible instances Roy, just in those Tim could be bothered to code for.

>In the example you give it would still be possible to create fieldnames in the final dataset ... "SumFieldAPlusSumFieldB" ... it would just be "fiddly" to write behind the scenes in the code as there would be nasty parsing once expressions became complex, in that case it could revert back to "Expression".

When you reach a point where "it sometimes does this and sometimes does that" then, in my view, its time to stop. I also think that when you need to run the query just to find out what field names have been generated its also time to stop.

I can't complain about asking for changes to make your life a little easier, I've done so myself, but ultimately I think this one has a potential to bite back. Actually, rereading your post, I'm not sure why you want this. Is it just for esthetics?

Roy

Roy

I agree that there are plenty of negatives with my suggestion, but I still think in the "first order" expressions it could work.

After all if I select <Field> from Table I expect it to return "FieldName" not "expression" ... so to continue this up 1 order of FUNCTION behaviour could be useful.

The main issue for me is that me and my users write quite a lot of custom SQL, a lot of different business analysis reporting SELECTs. In this situation a grid is generated with data in it, and it is actually hard to figure out which column originates from which data-columns when they are just listed Expression1, Expression2.

Of course all but the laziest can write "as <FieldName>" in their query ... but this enhancement would save me and a good number of my users that tiresome work at least a few times every day.

Adam


>The main issue for me is that me and my users write quite a lot of custom SQL, a lot of different business analysis reporting SELECTs. In this situation a grid is generated with data in it, and it is actually hard to figure out which column originates from which data-columns when they are just listed Expression1, Expression2.

In my homebrew report generator (simple one just reporting columns from various tables) I have a facility for the user to enter column headings - generally I wouldn't want them to use field headings.

>Of course all but the laziest can write "as <FieldName>" in their query ... but this enhancement would save me and a good number of my users that tiresome work at least a few times every day.

I find this a bit scary - it sounds as though you're allowing your users to type raw sql. How do you control it and prevent malicious sql?

Roy

Roy

You are right, I do give users control, but only the ability to write SELECTs within my applications.

That said they are free to do more than this. They "own" their databases, they are fairly big businesses and I am the consultant. I am not writing anything shrink-wrapped.

They use ODBC a lot and are writing SQL in Excel too. Regularly updating and changing their database structures by themselves ... happy days