Column aliases use literal not alias in Group By

[NickB]

This query creates two columns with aliases and a literal as the value. It
works fine in Access but falls over when using a table linked to SQL Server
because the generated SQL uses the literal as the column name not the alias
(see below). I have tried using column number (ie group by 1,3) but this
doesn't make any difference. Also tried editing the SQL to use the aliases
but it won't run at all then.

Same symptoms under ANSI 89 and 92.

********************************
SELECT "040 - ServiceContracts" AS Status,
Count(dbo_viewServiceContractFull.ClientCode) AS [Number], "" AS Report
FROM dbo_viewServiceContractFull
GROUP BY "040 - ServiceContracts", "";
********************************

 

[Douglas J. Steele]

Try it without the GROUP BY clause. Since both are constants, it shouldn't
be necessary.

SELECT "040 - ServiceContracts" AS Status,
Count(dbo_viewServiceContractFull.ClientCode) AS [Number], "" AS Report
FROM dbo_viewServiceContractFull

 

[NickB]

Douglas,

That fixed it. Edited the SQL but noticed that this replaces "Group By"
with "Expression" on the query designer. Thanks.

--
NickB

Try it without the GROUP BY clause. Since both are constants, it shouldn't
be necessary.

SELECT "040 - ServiceContracts" AS Status,
Count(dbo_viewServiceContractFull.ClientCode) AS [Number], "" AS Report
FROM dbo_viewServiceContractFull


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This query creates two columns with aliases and a literal as the value.
It
works fine in Access but falls over when using a table linked to SQL
Server
because the generated SQL uses the literal as the column name not the
alias
(see below). I have tried using column number (ie group by 1,3) but this
doesn't make any difference. Also tried editing the SQL to use the
aliases
but it won't run at all then.

Same symptoms under ANSI 89 and 92.

********************************
SELECT "040 - ServiceContracts" AS Status,
Count(dbo_viewServiceContractFull.ClientCode) AS [Number], "" AS Report
FROM dbo_viewServiceContractFull
GROUP BY "040 - ServiceContracts", "";
********************************