Created Fields Not working on SQL Linked Tables

[Guest]

The following query works with Access linked tables:

SELECT ACCOUNT, Date() AS [Current Date], 0 AS Historical
FROM [Local Exchange G/L]
GROUP BY ACCOUNT, Date(), 0;

When we switched to SQL linked tables, we got the following error:

ODBC – call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]GROUP BY expressions must
refer to column names that appear in the select list. (#164)

When we removed the Current Date and Historical columns, the query worked.
It also worked when we changed the Current Date column from a Group to a Max
and the Historical column from a group to a Sum.

Since the entire application has many queries which may have this situation,
is there a way to change something on the SQL Server side so the queries do
not have to be changed ?

 

[Michel Walsh]

MS SQL Server can group by on not selected values.

----------------
USE pubs
GO
SELECT MAX(au_lname)
FROM authors
GROUP BY au_fname
----------------


but as soon as you add the ,0 the error message you mentioned occurs:



--------------------
USE pubs
GO
SELECT MAX(au_lname)
FROM authors
GROUP BY au_fname, 0
- - - - - - - - - - - - - - - - -
Server: Msg 164, Level 15, State 1, Line 3
GROUP BY expressions must refer to column names that appear in the select
list.
----------------------



I suggest to remove the ,0, which does nothing very useful, in the group by.
I doubt there is an 'option' that may made MS SQL Server to 'ignore' that
error.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you.

MS SQL Server can group by on not selected values.

----------------
USE pubs
GO
SELECT MAX(au_lname)
FROM authors
GROUP BY au_fname
----------------


but as soon as you add the ,0 the error message you mentioned occurs:



--------------------
USE pubs
GO
SELECT MAX(au_lname)
FROM authors
GROUP BY au_fname, 0
- - - - - - - - - - - - - - - - -
Server: Msg 164, Level 15, State 1, Line 3
GROUP BY expressions must refer to column names that appear in the select
list.
----------------------



I suggest to remove the ,0, which does nothing very useful, in the group by.
I doubt there is an 'option' that may made MS SQL Server to 'ignore' that
error.


Hoping it may help,
Vanderghast, Access MVP

The following query works with Access linked tables:

SELECT ACCOUNT, Date() AS [Current Date], 0 AS Historical
FROM [Local Exchange G/L]
GROUP BY ACCOUNT, Date(), 0;

When we switched to SQL linked tables, we got the following error:

ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]GROUP BY expressions must
refer to column names that appear in the select list. (#164)

When we removed the Current Date and Historical columns, the query
worked.
It also worked when we changed the Current Date column from a Group to a
Max
and the Historical column from a group to a Sum.

Since the entire application has many queries which may have this
situation,
is there a way to change something on the SQL Server side so the queries
do
not have to be changed ?