Error 4104 multi-part identifier "dbo.carrier.cactive" could not be bound.

[LSGKelly]

I am in the process of switching my back end of my database to an SQL Server. There are a few queries that are not working. I have narrowed it down to the LEFT JOIN statement, but I have no idea how to fix it. If I take out the LEFT JOIN, the query runs fine, but when I put it back, I get a bunch of the above errors.

Here is the SQL code:

SELECT Group.gBRET, 
Group.gName, 
Group.gID, 
Assessment.aReqReceived, 
Assessment.aPEO, 
Assessment.aEffectiveDate, 
Assessment.aBRETTier, 
qCurrentPl.cpID, 
qCurrentPl.cpName, 
qCurrentPl.cpPharmacyTier, 
qCurrentPl.cpHDHP, 
qCurrentPl.cpOutOfNetwork, 
qCurrentPl.cpGatekeeper, 
qCurrentPl.cpInPatientCopay, 
qCurrentPl.cpValueNetwork, 
qCurrentPl.cpHMO, 
qCurrentPl.cpValuePlan, 
qCurrentPl.cpInPatientCopayAmt, 
qCurrentPl.cpInPatientCopayOut, 
qCurrentPl.cpIPCoPayLimits, 
qCurrentPl.cpDedCoinsuranceRx, 
qCurrentPl.cpSingleDedIn, 
qCurrentPl.cpFamilyDedIn, 
qCurrentPl.cpSingleDedOut, 
qCurrentPl.cpFamilyDedOut, 
qCurrentPl.cpCoinsuranceIn, 
qCurrentPl.cpCoinsuranceOut, 
qCurrentPl.cpSingleOOPMaxIn, 
qCurrentPl.cpFamilyOOPMaxIn, 
qCurrentPl.cpSingleOOPMaxOut, 
qCurrentPl.cpFamilyOOPMaxOut, 
qCurrentPl.cpPhysOfficeVisitIn, 
qCurrentPl.cpSpecOfficeVisitIn, 
qCurrentPl.cpPhysOfficeVisitOut, 
qCurrentPl.cpInPatientHospIn, 
qCurrentPl.cpInPatientInDetailed, 
qCurrentPl.cpInPatientHospOut, 
qCurrentPl.cpDiagnosticsIn, 
qCurrentPl.cpDiagnosticsOut, 
qCurrentPl.cpOutPatientFacIn, 
qCurrentPl.cpOutPatientFacOut, 
qCurrentPl.cpUrgentCare, 
qCurrentPl.cpEmergencyRoom, 
qCurrentPl.cpTier1Rx, 
qCurrentPl.cpTier2Rx, 
qCurrentPl.cpTier3Rx, 
qCurrentPl.cpTier4Rx, 
qCurrentPl.cpSubsEE, 
qCurrentPl.cpSubsES, 
qCurrentPl.cpSubsEC, 
qCurrentPl.cpSubsEF, 
qCurrentPl.cpSubsFC, 
qCurrentPl.cptID, 
qCurrentPl.cparID, 
qCurrentPl.cpSingle, 
qCurrentPl.cpDouble, 
qCurrentPl.cpChildren, 
qCurrentPl.cpFamily, 
qCurrentPl.cpFamilyCont, 
qCurrentPl.cpMonthlyTotal, 
qCurrentPl.cpRenewalDate, 
qCurrentPl.cpIncrease, 
qCurrentPl.AuditTrail, 
qCurrentPl.cpCarrierRenewal, 
qCurrentPl.cpNotes, 
qNewPl.*, 
[cpsinglededin]-[psinglededin] AS SingleDedInC, 
[cpphysofficevisitin]-[pphysofficevisitin] AS PCPCoPayC, 
[cpspecofficevisitin]-[pspecofficevisitin] AS SpecCoPayC, 
[pcoinsurancein]-[cpcoinsurancein] AS CoinsuranceC, 
([cpsingleoopinwoded]-[psingleoopinwoded]) AS OOPMaxC, 
[cpInPatientcopayamt]-[pInPatientcopayamt] AS InPatientHospC, 
[cpTier1Rx]-[pTier1Rx] AS Tier1RxC, 
[cpTier2Rx]-[pTier2Rx] AS Tier2RxC, 
[cpTier3Rx]-[pTier3Rx] AS Tier3RxC, 
IIf([cpPharmacyTier]=[ppharmacyTier],"No","Yes") AS Tier4RxC, 
IIf([cpHDHP]=[phdhp],"No","Yes") AS HDHPC, IIf([cpHMO]=[pHMO],"No","Yes") AS HMONetC, 
IIf([cpOutOfNetwork]=[pOutOfNetwork],"No","Yes") AS OutOfNetC, 
IIf([cpGatekeeper]=[pGatekeeper],"No","Yes") AS GatekeeperC, 
IIf([cpDedCoinsuranceRx]=[pDedCoinsuranceRx],"No","Yes") AS DedCoinsRxC, 
DateAdd("m",0,DateSerial(Year([aeffectivedate]),Month([aeffectivedate]),1))-1 AS ADPPerEndDate, 
DateAdd("yyyy",+1,[adpperenddate]) AS CompEndDate, DateDiff("m",[aeffectivedate],[cPRenewalDate]) AS MosatCurr, 
12-[mosatcurr] AS MosAtRen, DateDiff("m",[aeffectivedate],[PRenewalDate]) AS ADPMosatCurr, 
12-[adpmosatcurr] AS ADPMosAtRen, 1+[cpincrease] AS CurCarIncr, 
DateDiff("m",[aeffectivedate],[cpRenewalDAte]) AS MosatCurrBl, 
12-[Mosatcurrbl] AS MosAtRenBl, 
[mosatcurrbl]+[mosatrenbl] AS TotalMos, 
(([rsingle]*[npSubsEE])+([rdouble]*[npSubsES])+([rchildren]*[npSubsEC])+([rfamily]*[npSubsEF])+([rfamilycont]*[npSubsFC]))*12 AS ADPRates, 
IIf([cpmonthlytotal]>0,[cpmonthlytotal]*12,(([cpsingle]*[cpSubsEE])+([cpdouble]*[cpSubsES])+([cpchildren]*[cpsubsEC])+([cpfamily]*[cpSubsEF])+([cpfamilycont]*[cpsubsFC]))*12) AS CurrentRates, 
IIf([cpmonthlytotal]>0,[cpmonthlytotal]*12,(([cpsingle]*[npSubsEE])+([cpdouble]*[npSubsES])+([cpchildren]*[npsubsEC])+([cpfamily]*[npSubsEF])+([cpfamilycont]*[npsubsFC]))*12) AS CurrentRates1, 
IIf([cpmonthlytotal]>0,[cpSubsEE]+[cpSubsES]+[cpSubsEC]+[cpSubsEF]+[cpSubsFC],0) AS CurSubsTot, 
[cpsubsee]+[cpsubses]+[cpsubsec]+[cpsubsef]+[cpsubsfc] AS cSubsTotal, [npSubsEE]+[npSubsES]+[npSubsEC]+[npSubsEF]+[npSubsFC] AS npSubsTotal, 
[cpMemEE]+[cpMemES]+[cpMemEC]+[cpMemEF] AS npMemTotal, 
Assessment.aID, 1+[oAssumedRenewal] AS ADPRenewalRate, qCurrentPl.ccName, qCurrentPl.cpMemEE, qCurrentPl.cpMemES, 
qCurrentPl.cpMemEC, 
qCurrentPl.cpMemEF
FROM [Group] INNER JOIN ((Assessment INNER JOIN qCurrentPl ON Assessment.aID = qCurrentPl.cpaID) 

LEFT JOIN qNewPl ON qCurrentPl.cpID = qNewPl.ocpID) ON (Group.gID = Assessment.gaID) AND (Group.gBRET = Assessment.agBRET);

Can someone please tell me what I am missing here? Thank you so much for your help.

Kelly

[ck9663]

You don't have an alias called "carrier"

Happy Coding!!!

~~ CK

[LSGKelly]

I'm sorry...I have no idea what that means!

Kelly

[ck9663]

Do you have a table called Carrier anywhere on your TSQL?

Good Luck!!!

~~ CK