April 28, 2009 at 9:19 am
I want to add another table to an existing select statement in order to pull an additional field.
The field I want to add is ClientBuckets.Cinvlastinv (Last date of Invoice sent). The select statement works until I add my additional Left Join. Here is the entire select statement with my additions commented out.
SELECT
ID AS client_eng_id,
O.OffName AS office,
C.CltNum + '.' + C.CltEng AS client_num,
Replace(C.CltName, ',', ';') AS client_name,
C.EngDesc AS eng_desc,
C.Engrelated AS eng_related,
C.Engentity AS entity_desc,
C.Engwork AS work_type,
C.Maractdate AS active_date,
C.Marinactdate AS inactive_date,
D.DeptName AS department,
--MAX(B.Cinvlastpay) AS lastpay_date,
Replace(C.CPPLname + '; ' + C.CPPFname, ',', ';') AS pp_name,
Replace(C.CSPLname + '; ' + C.CSPFname, ',', ';') AS sp_name,
Replace(C.CBMLname + '; ' + C.CBMFname, ',', ';') AS bm_name,
RegionDesc as RegionDesc,
SICDescription as SICDescription,
RTRIM(LTRIM(L.LOBDescription)) AS LineOfBusiness
FROM
Clients C
LEFT JOIN
Office O ON C.CltOff=O.OffID
LEFT JOIN
Region R ON O.OffRegion=R.RegionID
LEFT JOIN
Department D ON C.EngDept=D.DeptID
LEFT JOIN
SICCode S ON C.EngSICCode = S.SicCode
LEFt JOIN
LineOfBusiness L ON L.LOBID = C.EngLOB
--LEFT JOIN
--ClientBuckets B ON B.Cltnum = C.Cltnum
The error I am getting is:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'ID'.
I thought this would be easy enough to add to an existing select statement, but I don't understand the error I am getting. Any help would be appreciated!
Thanks,
Laurie
April 28, 2009 at 9:23 am
LMeyer (4/28/2009)
I want to add another table to an existing select statement in order to pull an additional field.The field I want to add is ClientBuckets.Cinvlastinv (Last date of Invoice sent). The select statement works until I add my additional Left Join. Here is the entire select statement with my additions commented out.
SELECT
ID AS client_eng_id, Which table is this column from?
O.OffName AS office,
C.CltNum + '.' + C.CltEng AS client_num,
Replace(C.CltName, ',', ';') AS client_name,
C.EngDesc AS eng_desc,
C.Engrelated AS eng_related,
C.Engentity AS entity_desc,
C.Engwork AS work_type,
C.Maractdate AS active_date,
C.Marinactdate AS inactive_date,
D.DeptName AS department,
--MAX(B.Cinvlastpay) AS lastpay_date,
Replace(C.CPPLname + '; ' + C.CPPFname, ',', ';') AS pp_name,
Replace(C.CSPLname + '; ' + C.CSPFname, ',', ';') AS sp_name,
Replace(C.CBMLname + '; ' + C.CBMFname, ',', ';') AS bm_name,
RegionDesc as RegionDesc,
SICDescription as SICDescription,
RTRIM(LTRIM(L.LOBDescription)) AS LineOfBusiness
FROM
Clients C
LEFT JOIN
Office O ON C.CltOff=O.OffID
LEFT JOIN
Region R ON O.OffRegion=R.RegionID
LEFT JOIN
Department D ON C.EngDept=D.DeptID
LEFT JOIN
SICCode S ON C.EngSICCode = S.SicCode
LEFt JOIN
LineOfBusiness L ON L.LOBID = C.EngLOB
--LEFT JOIN
--ClientBuckets B ON B.Cltnum = C.Cltnum
The error I am getting is:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'ID'.
I thought this would be easy enough to add to an existing select statement, but I don't understand the error I am getting. Any help would be appreciated!
Thanks,
Laurie
See my question above. I think you will find two of the tables you are using in this query when you add the second LEFT OUTER JOIN both have a column named ID. You need to qualify it. It is alsways a good idea to qualify column names in queries for just this reason.
April 28, 2009 at 9:53 am
I've added the table information on the ID field, and I've now removed the comment lines, and I am still gettig the following error:
Msg 8120, Level 16, State 1, Line 3
Column 'Clients.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here is the current script:
SELECT
C.ID AS client_eng_id,
O.OffName AS office,
C.CltNum + '.' + C.CltEng AS client_num,
Replace(C.CltName, ',', ';') AS client_name,
C.EngDesc AS eng_desc,
C.Engrelated AS eng_related,
C.Engentity AS entity_desc,
C.Engwork AS work_type,
C.Maractdate AS active_date,
C.Marinactdate AS inactive_date,
D.DeptName AS department,
MAX(B.Cinvlastpay) AS lastpay_date,
Replace(C.CPPLname + '; ' + C.CPPFname, ',', ';') AS pp_name,
Replace(C.CSPLname + '; ' + C.CSPFname, ',', ';') AS sp_name,
Replace(C.CBMLname + '; ' + C.CBMFname, ',', ';') AS bm_name,
RegionDesc as RegionDesc,
SICDescription as SICDescription,
RTRIM(LTRIM(L.LOBDescription)) AS LineOfBusiness
FROM
Clients C
LEFT JOIN
Office O ON C.CltOff=O.OffID
LEFT JOIN
Region R ON O.OffRegion=R.RegionID
LEFT JOIN
Department D ON C.EngDept=D.DeptID
LEFT JOIN
SICCode S ON C.EngSICCode = S.SicCode
LEFt JOIN
LineOfBusiness L ON L.LOBID = C.EngLOB
LEFT JOIN
ClientBuckets B ON B.Cltnum = C.Cltnum
Thanks,
Laurie
April 28, 2009 at 10:10 am
Because you are using the MAX aggregate in the select statement you need a group by clause, try the following:
SELECT
C.ID AS client_eng_id,
O.OffName AS office,
C.CltNum + '.' + C.CltEng AS client_num,
Replace(C.CltName, ',', ';') AS client_name,
C.EngDesc AS eng_desc,
C.Engrelated AS eng_related,
C.Engentity AS entity_desc,
C.Engwork AS work_type,
C.Maractdate AS active_date,
C.Marinactdate AS inactive_date,
D.DeptName AS department,
MAX(B.Cinvlastpay) AS lastpay_date,
Replace(C.CPPLname + '; ' + C.CPPFname, ',', ';') AS pp_name,
Replace(C.CSPLname + '; ' + C.CSPFname, ',', ';') AS sp_name,
Replace(C.CBMLname + '; ' + C.CBMFname, ',', ';') AS bm_name,
RegionDesc as RegionDesc,
SICDescription as SICDescription,
RTRIM(LTRIM(L.LOBDescription)) AS LineOfBusiness
FROM
Clients C
LEFT JOIN Office O
ON C.CltOff=O.OffID
LEFT JOIN Region R
ON O.OffRegion=R.RegionID
LEFT JOIN Department D
ON C.EngDept=D.DeptID
LEFT JOIN SICCode S
ON C.EngSICCode = S.SicCode
LEFT JOIN LineOfBusiness L
ON L.LOBID = C.EngLOB
LEFT JOIN ClientBuckets B
ON B.Cltnum = C.Cltnum
GROUP BY
C.ID,
O.OffName,
C.CltNum + '.' + C.CltEng,
Replace(C.CltName, ',', ';'),
C.EngDesc,
C.Engrelated,
C.Engentity,
C.Engwork,
C.Maractdate,
C.Marinactdate,
D.DeptName,
Replace(C.CPPLname + '; ' + C.CPPFname, ',', ';'),
Replace(C.CSPLname + '; ' + C.CSPFname, ',', ';'),
Replace(C.CBMLname + '; ' + C.CBMFname, ',', ';'),
RegionDesc,
SICDescription,
RTRIM(LTRIM(L.LOBDescription))
;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply