November 20, 2006 at 6:43 am
Hi Everyone
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment.
Mitesh
November 20, 2006 at 8:51 am
Guys found the solution to my problem...but with someones help.
Removed - (select * from customer) --
and Added INVDATE.[LASTORDERDATE] in Group by
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,CUSTOMER as CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE]COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_Latin1_General_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE],INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply