The column prefix h does not match with a table name or alias name used in the query
-
Hi guys,
I have a SELECT Query (with sub-query) , that causes the the above error:
SELECT ax.AccNo,
(SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID) As Product
FROM dbo.History h LEFT OUTER JOIN dbo.AccXRef ax ON h.ID= ax.ID LEFT OUTER JOIN dbo.States ON h.[HistoryItemsub-Type] = dbo.States.Type LEFT OUTER JOIN dbo.CustXRef cx ON h.CustomerNo = cx.CustomerNo
WHERE HistoryItemDate <= getdate() ORDER BY HistoryItemDate ASC
This query runs fine in "Query Analyser" and in the program "A.exe" ...
When a user runs the A.exe he gets the error.
is there a settings in SQLserver that needs to be set???
The connection is made thru ADO, SQL2K...
Many thanx
TNT
-
I believe the problem is with your subSELECT:
SELECT Accounts.ProductCode FROM Accounts WHERE h.ID=Accounts.ID
h is never defined within the subSELECT.
-SQLBill
-
Hi SQLBill,
the query works in query analyer and in the program (A.exe) here..
But the A.exe program does not work for clients ...
but am not sure if this occurs for 'SELECT' statement ...
help!!!
TNT
-
Does the user have SELECT access to HISTORY?
-SQLBill
-
yes.
the user has comlete access the whole database.
TNT
-
Where does the 'work' take place? I know in Crystal Reports the default is to pull all the data back to the client machine. You can change it to do all the work on the SQL Server machine.
Maybe the queries are being done a bit at a time. Have you tried changing the subquery to use history instead of the h?
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply