Invalid Column Name Error?? 1

[MeSoCrazy]

Hello All,

I have a lengthy SQL statement that runs fine until I use a column name in the Where clause which causes an error of Invalid Column Name error. If I comment out the line in the where clause that uses this and run it completes without error and when I look at the results the column name is shown in the header of the results.

The line that causes me problems whne in the WHERE clause is

--AND (StCust = 'Custom')

The portion of the code that creates this column is:

CASE WHEN LEFT(#tempSTCustBS.StandardCustom, 6) = 'Custom' THEN 'Custom' 
WHEN LEFT(#tempSTCustBS.StandardCustom, 8) = 'Standard' THEN 'Standard'
ELSE Null END StCust

Not sure what I am doing wrong, but here is the code:

SELECT     'A' AS Grouper, dbo.RT_Event.RTEventID, dbo.RT_Event.ClientNbr AS RTClientNbr, dbo.RT_Event.EventStatus, dbo.RT_Event.EffectiveDate AS EventEffectiveDate, 
                      DateDiff(day, GetDate(), dbo.RT_Event.EffectiveDate) AS TodayEffDateDiff, dbo.RT_Event.TypeOfEvent, dbo.RT_Event.EnrollmentTypeForOE, dbo.RT_Event.OngoingEnrollmentType, 
                      dbo.RT_EventStatus.SoldNotificationReceivedComplete, dbo.RT_EventStatus.RequirementsComplete, dbo.RT_EventStatus.NCDApprovedComplete, 
                      dbo.RT_EventPlanCycleTime.NCDApprovedDate AS PlanCycleTimeNCDApprovedDate, 
                      dbo.RT_EventPlanCycleTime.PaperworkToGroupSetupDate AS PlanCycleTimePaperworkToGroupSetupDate, 
                      dbo.RT_EventPlanCycleTime.GroupSetupCompleteDate AS PlanCycleTimeGroupSetupCompleteDate, 
                      dbo.RT_EventPlanCycleTime.WebBuildCompleteDate AS PlanCycleTimeWebBuildCompleteDate, 
                      dbo.RT_EventPlanCycleTime.SPAASetupCompleteDate AS PlanCycleTimeSPAASetupCompleteDate, 
                      dbo.RT_EventPlanCycleTime.EnrollmentReceivedDate AS PlanCycleTimeEnrollmentReceivedDate, 
                      dbo.RT_EventPlanCycleTime.EnrollmentLoadedDate AS PlanCycleTimeEnrollmentLoadedDate, 
                      dbo.RT_EventPlanCycleTime.IDCardFormatApprovedDate AS PlanCycleTimeIDCardFormatApprovedDate, 
                      dbo.RT_EventPlanCycleTime.IDCardMeteredDate AS PlanCycleTimeIDCardMeteredDate, 
                      dbo.RT_EventPlanCycleTime.PlanLoadCompleteDate AS PlanCycleTimePlanLoadCompleteDate, 
                      dbo.RT_EventPlanCycleTime.ProductBuildCompleteDate AS PlanCycleTimeProductBuildCompleteDate, 
                      dbo.RT_EventPlanCycleTime.GroupBilledDate AS PlanCycleTimeGroupBilledDate, 
                      dbo.RT_EventPlanCycleTime.CertsIssuedDate AS PlanCycleTimeCertsIssuedDate, 
                      dbo.RT_EventPlanCycleTime.RequirementsCompleteToNCDApproved AS PlanCycleTimeRequirementsCompleteToNCDApproved, 
                      dbo.RT_EventPlanCycleTime.RequirementsCompleteToPaperworkToGroupSetup AS PlanCycleTimeRequirementsCompleteToPaperworkToGroupSetup, 
                      dbo.RT_EventPlanCycleTime.RequirementsCompleteToGSUComplete AS PlanCycleTimeRequirementsCompleteToGSUComplete, 
                      dbo.RT_EventPlanCycleTime.GSUCompleteToWebBuildComplete AS PlanCycleTimeGSUCompleteToWebBuildComplete, 
                      dbo.RT_EventPlanCycleTime.RequirementsCompleteToSPAASetupComplete AS PlanCycleTimeRequirementsCompleteToSPAASetupComplete, 
                      dbo.RT_EventPlanCycleTime.GroupSetupCompleteToEnrollmentReceived AS PlanCycleTimeGroupSetupCompleteToEnrollmentReceived, 
                      dbo.RT_EventPlanCycleTime.EnrollmentReceivedToEnrollmentLoaded AS PlanCycleTimeEnrollmentReceivedToEnrollmentLoaded, 
                      dbo.RT_EventPlanCycleTime.EnrollmentLoadedToIDCardMetered AS PlanCycleTimeEnrollmentLoadedToIDCardMetered, 
                      dbo.RT_EventPlanCycleTime.NCDApprovedToPlanLoadComplete AS PlanCycleTimeNCDApprovedToPlanLoadComplete, 
                      dbo.RT_EventPlanCycleTime.NCDApprovedToProductBuildComplete AS PlanCycleTimeNCDApprovedToProductBuildComplete, 
                      dbo.RT_EventPlanCycleTime.GroupBilledToEffectiveDate AS PlanCycleTimeGroupBilledToEffectiveDate, 
                      dbo.RT_EventPlanCycleTime.CertsIssuedToEffectiveDate AS PlanCycleTimeCertsIssuedToEffectiveDate, 
                      dbo.RT_EventActualCycleTime.SoldNotificationReceivedDate AS ActualCycleTimeSoldNotificationReceivedDate, 
                      dbo.RT_EventActualCycleTime.RequirementsCompleteDate AS ActualCycleTimeRequirementsCompleteDate, 
                      dbo.RT_EventActualCycleTime.NCDApprovedDate AS ActualCycleTimeNCDApprovedDate, 
                      dbo.RT_EventActualCycleTime.PaperworkToGroupSetupDate AS ActualCycleTimePaperworkToGroupSetupDate, 
                      dbo.RT_EventActualCycleTime.GroupSetupCompleteDate AS ActualCycleTimeGroupSetupCompleteDate, 
                      dbo.RT_EventActualCycleTime.WebBuildCompleteDate AS ActualCycleTimeWebBuildCompleteDate, 
                      dbo.RT_EventActualCycleTime.SPAASetupCompleteDate AS ActualCycleTimeSPAASetupCompleteDate, 
                      dbo.RT_EventActualCycleTime.EnrollmentReceivedDate AS ActualCycleTimeEnrollmentReceivedDate, 
                      dbo.RT_EventActualCycleTime.EnrollmentLoadedDate AS ActualCycleTimeEnrollmentLoadedDate, 
                      dbo.RT_EventActualCycleTime.IDCardFormatApprovedDate AS ActualCycleTimeIDCardFormatApprovedDate, 
                      dbo.RT_EventActualCycleTime.IDCardMeteredDate AS ActualCycleTimeIDCardMeteredDate, 
                      dbo.RT_EventActualCycleTime.PlanLoadCompleteDate AS ActualCycleTimePlanLoadCompleteDate, 
                      dbo.RT_EventActualCycleTime.ProductBuildCompleteDate AS ActualCycleTimeProductBuildCompleteDate, 
                      dbo.RT_EventActualCycleTime.GroupBilledDate AS ActualCycleTimeGroupBilledDate, 
                      dbo.RT_EventActualCycleTime.CertsIssuedDate AS ActualCycleTimeCertsIssuedDate, 
                      dbo.RT_EventActualCycleTime.SoldNotificationReceivedToRequirementsComplete AS ActualCycleTimeSoldNotificationReceivedToRequirementsComplete,
                      dbo.RT_EventActualCycleTime.RequirementsCompleteToNCDApproved AS ActualCycleTimeRequirementsCompleteToNCDApproved, 
                      dbo.RT_EventActualCycleTime.RequirementsCompleteToPaperworkToGroupSetup AS ActualCycleTimeRequirementsCompleteToPaperworkToGroupSetup,
                      dbo.RT_EventActualCycleTime.RequirementsCompleteToGSUComplete AS ActualCycleTimeRequirementsCompleteToGSUComplete, 
                      dbo.RT_EventActualCycleTime.GSUCompleteToWebBuildComplete AS ActualCycleTimeGSUCompleteToWebBuildComplete, 
                      dbo.RT_EventActualCycleTime.RequirementsCompleteToSPAASetupComplete AS ActualCycleTimeRequirementsCompleteToSPAASetupComplete, 
                      dbo.RT_EventActualCycleTime.GroupSetupCompleteToEnrollmentReceived AS ActualCycleTimeGroupSetupCompleteToEnrollmentReceived, 
                      dbo.RT_EventActualCycleTime.EnrollmentReceivedToEnrollmentLoaded AS ActualCycleTimeEnrollmentReceivedToEnrollmentLoaded, 
                      dbo.RT_EventActualCycleTime.EnrollmentLoadedToIDCardMetered AS ActualCycleTimeEnrollmentLoadedToIDCardMetered, 
                      dbo.RT_EventActualCycleTime.NCDApprovedToPlanLoadComplete AS ActualCycleTimeNCDApprovedToPlanLoadComplete, 
                      dbo.RT_EventActualCycleTime.NCDApprovedToProductBuildComplete AS ActualCycleTimeNCDApprovedToProductBuildComplete, 
                      dbo.RT_EventActualCycleTime.GroupBilledToEffectiveDate AS ActualCycleTimeGroupBilledToEffectiveDate, 
                      dbo.RT_EventActualCycleTime.CertsIssuedToEffectiveDate AS ActualCycleTimeCertsIssuedToEffectiveDate, dbo.ClientProfile.Segment, 
                      dbo.ClientProfile.AccountExecutive, ASD.FirstName + ' ' + ASD.LastName AS ASD, CSE.FirstName + ' ' + CSE.LastName AS CSE, 
                      CASE WHEN AIM.FirstName IS NOT NULL THEN AIM.FirstName + ' ' + AIM.LastName WHEN AIC.FirstName IS NOT NULL 
                      THEN AIC.FirstName + ' ' + AIC.LastName ELSE '_AIM or AIC not defined' END AS AIMAIC, 
                      CASE WHEN dbo.ClientDemographics.ClientName IS NOT NULL 
                      THEN dbo.ClientDemographics.ClientName ELSE '_Client not defined' END AS ClientName, CASE WHEN dbo.Market.MarketText IS NOT NULL 
                      THEN dbo.Market.MarketText ELSE '_Market not Defined' END AS Market, CASE WHEN dbo.Market.MarketText IS NOT NULL 
                      THEN dbo.Market.MarketID ELSE 0 END AS MarketID, 
                      CASE WHEN dbo.RT_EventStatus.SoldNotificationReceivedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.SoldNotificationReceivedFull = 'Y'
                       THEN 'In Progress' ELSE '' END AS SoldNotification, 
                      CASE WHEN dbo.RT_EventStatus.RequirementsComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.RequirementsFull = 'Y' THEN 'In Progress'
                       WHEN dbo.RT_EventStatus.RequirementsNA = 'Y' THEN 'N/A' ELSE '' END AS StatusRequirements, 
                      CASE WHEN dbo.RT_EventStatus.NCDApprovedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.NCDApprovedNA = 'Y' THEN 'N/A' ELSE '' END
                       AS StatusNCDApproved, 
                      CASE WHEN dbo.RT_EventStatus.PaperworkToGSUComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.PaperworkToGSUNA = 'Y' THEN 'N/A' ELSE
                       '' END AS StatusPaperworkToGSUC, 
                      CASE WHEN dbo.RT_EventStatus.GroupSetupComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.GroupSetupPartial = 'Y' OR
                      dbo.RT_EventStatus.GroupSetupFull = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.GroupSetupPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.GroupSetupNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusGroupSetup, 
                      CASE WHEN dbo.RT_EventStatus.SPAASetupComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.SPAASetupFull = 'Y' OR
                      dbo.RT_EventStatus.SPAASetupPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.SPAASetupPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.SPAASetupNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusSPAASetup, 
                      CASE WHEN dbo.RT_EventStatus.EnrollmentReceivedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.EnrollmentReceivedFull = 'Y' OR
                      dbo.RT_EventStatus.EnrollmentReceivedPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.EnrollmentReceivedPending = 'Y' THEN 'Pending'
                       WHEN dbo.RT_EventStatus.EnrollmentReceivedNA = 'Y' THEN 'N/A' ELSE '' END AS StatusEnrollmentReceived, 
                      CASE WHEN dbo.RT_EventStatus.EnrollmentLoadedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.EnrollmentLoadedFull = 'Y' OR
                      dbo.RT_EventStatus.EnrollmentLoadedPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.EnrollmentLoadedPending = 'Y' THEN 'Pending' WHEN
                       dbo.RT_EventStatus.EnrollmentLoadedNA = 'Y' THEN 'N/A' ELSE '' END AS StatusEnrollmentLoaded, 
                      CASE WHEN dbo.RT_EventStatus.IDCardFormatApprovedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.IDCardFormatApprovedFull = 'Y' OR
                      dbo.RT_EventStatus.IDCardFormatApprovedPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.IDCardFormatApprovedPending = 'Y' THEN 'Pending'
                       WHEN dbo.RT_EventStatus.IDCardFormatApprovedNA = 'Y' THEN 'N/A' ELSE '' END AS StatusIDCardFormatApproved, 
                      CASE WHEN dbo.RT_EventStatus.IDCardMeteredComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.IDCardMeteredFull = 'Y' OR
                      dbo.RT_EventStatus.IDCardMeteredPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.IDCardMeteredPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.IDCardMeteredNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusIDCardMetered, 
                      CASE WHEN dbo.RT_EventStatus.PlanLoadComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.PlanLoadFull = 'Y' OR
                      dbo.RT_EventStatus.PlanLoadPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.PlanLoadPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.PlanLoadNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusPlanLoad, 
                      CASE WHEN dbo.RT_EventStatus.ProductBuildComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.ProductBuildFull = 'Y' OR
                      dbo.RT_EventStatus.ProductBuildPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.ProductBuildPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.ProductBuildNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusProductBuild, 
                      CASE WHEN dbo.RT_EventStatus.GroupBilledComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.GroupBilledFull = 'Y' THEN 'In Progress' ELSE
                       '' END AS StatusGroupBilled, 
                      CASE WHEN dbo.RT_EventStatus.CertsIssuedComplete = 'Y' THEN 'Complete' WHEN dbo.RT_EventStatus.CertsIssuedFull = 'Y' OR
                      dbo.RT_EventStatus.CertsIssuedPartial = 'Y' THEN 'In Progress' WHEN dbo.RT_EventStatus.CertsIssuedPending = 'Y' THEN 'Pending' WHEN dbo.RT_EventStatus.CertsIssuedNA
                       = 'Y' THEN 'N/A' ELSE '' END AS StatusCertsIssued, 
                      CASE WHEN LEFT(#tempPlanFundingBS.PlanFunding, 3) = 'ASO' THEN 'ASO' ELSE 'FI' END PlanFunding,
                      CASE WHEN LEFT(#tempSTCustBS.StandardCustom, 6) = 'Custom' THEN 'Custom' 
                           WHEN LEFT(#tempSTCustBS.StandardCustom, 8) = 'Standard' THEN 'Standard'
                      ELSE Null END StCust,
                      #EVTApproved.EVTApprovedDate,
                      #EVTSent.EVTSentDate,
                      #EventAIMAICComment.Comments
FROM                  dbo.RT_EventStatus INNER JOIN
                      #tmpMasterList ON dbo.RT_EventStatus.RTEventID = #tmpMasterList.EventProfileID INNER JOIN
                      dbo.RT_Event ON dbo.RT_EventStatus.RTEventID = dbo.RT_Event.RTEventID INNER JOIN
                      dbo.RT_EventActualCycleTime ON dbo.RT_Event.RTEventID = dbo.RT_EventActualCycleTime.RTEventID INNER JOIN
                      dbo.RT_EventPlanCycleTime ON dbo.RT_Event.RTEventID = dbo.RT_EventPlanCycleTime.RTEventID LEFT OUTER JOIN
                      #EventAIMAICComment ON dbo.RT_Event.RTEventID = #EventAIMAICComment.EventProfileID LEFT OUTER JOIN
                      #tempPlanFundingBS ON dbo.RT_EventStatus.RTEventID = #tempPlanFundingBS.RTEventID LEFT OUTER JOIN
                      #tempSTCustBS ON dbo.RT_EventStatus.RTEventID = #tempSTCustBS.RTEventID LEFT OUTER JOIN
                      #EVTApproved ON dbo.RT_EventStatus.RTEventID = #EVTApproved.EventProfileID LEFT OUTER JOIN
                      #EVTSent ON dbo.RT_EventStatus.RTEventID = #EVTSent.EventProfileID LEFT OUTER JOIN
                      dbo.Associate AS CSE RIGHT OUTER JOIN
                      dbo.ClientProfile ON CSE.AssociateID = dbo.ClientProfile.CSEID LEFT OUTER JOIN
                      dbo.Associate AS ASD ON dbo.ClientProfile.ASDID = ASD.AssociateID LEFT OUTER JOIN
                      dbo.ClientDemographics ON dbo.ClientProfile.ClientNbr = dbo.ClientDemographics.ClientNbr LEFT OUTER JOIN
                      dbo.Associate AS AIC ON dbo.ClientProfile.AICID = AIC.AssociateID LEFT OUTER JOIN
                      dbo.Associate AS AIM ON dbo.ClientProfile.AIMID = AIM.AssociateID LEFT OUTER JOIN
                      dbo.Market ON dbo.ClientProfile.SellingMarketID = dbo.Market.MarketID ON dbo.RT_Event.ClientNbr = dbo.ClientProfile.ClientNbr LEFT OUTER JOIN
                      dbo.RT_Client ON dbo.RT_Event.ClientNbr = dbo.RT_Client.RTClientNbr 
WHERE ((IsDate(dbo.RT_EventActualCycleTime.SoldNotificationReceivedDate) = 1) 
       AND (IsDate(dbo.RT_EventActualCycleTime.RequirementsCompleteDate) = 0)
       AND (PlanFunding = 'ASO')
       AND (DateDiff(day, GetDate(), dbo.RT_Event.EffectiveDate) >= -25))
       OR
       ((IsDate(dbo.RT_EventActualCycleTime.SoldNotificationReceivedDate) = 1) 
       AND (IsDate(dbo.RT_EventActualCycleTime.RequirementsCompleteDate) = 0)                     
       AND (PlanFunding = 'FI')
       --AND (StCust = 'Custom')
       AND (DateDiff(day, GetDate(), dbo.RT_Event.EffectiveDate) >= -20))
       
       
ORDER BY dbo.RT_Event.EffectiveDate, dbo.ClientDemographics.ClientName

Due to security policies, I can not upload any results showing that the column does return in the recordset when the column is not used in the WHERE clause.


Thanks, Troy

 

[markros]

You can not use column's name created in the list of the select items in a WHERE clause. You can only use it if you use derived table or if you create this field using CROSS APPLY technique.

More on the problem you can read (and vote up if find it useful) here
Column Aliases everywhere in the Query

PluralSight Learning Library

 

[gmmastros]

Try repeating the case statement in the where clause, like this:

--AND CASE WHEN LEFT(#tempSTCustBS.StandardCustom, 6) = 'Custom' THEN 'Custom' 
WHEN LEFT(#tempSTCustBS.StandardCustom, 8) = 'Standard' THEN 'Standard'
ELSE Null END = 'Custom'

Of course, that column can only be custom when the first condition is met, so you can simplify (and improve performance) with:

AND LEFT(#tempSTCustBS.StandardCustom, 6) = 'Custom'

And it would be even better to do this:

AND #tempSTCustBS.StandardCustom Like 'Custom%'

Whenever you use LEFT in a where clause, you should change it to use a like comparison they way that I showed because it allows the query to become [google]SQL Server Sargable[/google]. Since this is a temp table, there isn't likely to be an index that this particular condition can use, but you should make it a habit so that when an index can be used, it will. If you don't know what sargable means, I encourage you to spend 10 minutes reading up on it. It's not a complicated subject, and it's well worth your time.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MeSoCrazy]

OK, I understand and thanks for the SQL Server Sargable information.

Can you tell me why the portion of the WHERE clause above it works as it is from a similar crafted column.

Where clause snippet:

AND (PlanFunding = 'FI')

PlanFunding defined snippet:

CASE WHEN LEFT(#tempPlanFundingBS.PlanFunding, 3) = 'ASO' THEN 'ASO' ELSE 'FI' END PlanFunding

I am fairly new to SQL programming, so I am asking for my understanding of the difference between the two.. one that works and the one that didn't.

Thanks, Troy

 

[gmmastros]

Plan funding works because you have a column named planfunding in your temp table. The filter in the where clause is using the underlying data for the filtering and NOT the case statement. It might rake some digging on your part to realize this, but I assure you it is happenening.

Bottom line, it appears as though plan funding works because you aliased that case/when statement with the same name as an existing column.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom