SQL Server Invalid Column name error while referencing alias in CASE Statement

[Mittle]

Hello

I am trying to create a view and getting Invalid column name error when executing this Select statement


CREATE VIEW [dbo].[test]
AS

SELECT
ProjectReturn_Detail.DepartmentID,
ProjectReturn_Detail.QTRID,
ProjectReturn_Detail.ProjectID,
ProjectReturn_Detail.ProjReturnID,

Min(CASE
WHEN [ManMilestoneID]=2 THEN [OriginalBaselineDate]
ELSE Null
END
)
AS EndDateBase,

Min(CASE
WHEN [ManMilestoneID]=2 THEN [LatestBaselineDate]
ELSE Null

END
)
AS EndDateLastBase,


Min(CASE
WHEN [ManMilestoneID]=1 THEN [OriginalBaselineDate]
ELSE NULL
END
)
AS StartDateBase,
Min(CASE
WHEN [ManMilestoneID]=1 THEN [LatestBaselineDate]
ELSE NULL
END
)
AS StartDateLastBase,
--------------------------------------------------------------------------------------------------------------------------------------------------------
CASE

WHEN [EndDateLastBase] Is Null THEN datediff(day,[EndDateBase],[StartDateBase])/365.25
ELSE datediff(day, [EndDateLastBase],[StartDateBase])/365.25

END
AS ProjectDuration
---------------------------------------------------------------------------------------------------------------------------------------------------------
FROM
ProjectReturn_Detail
INNER JOIN (MandatoryMilestone
INNER JOIN Milestone ON MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID)
ON ProjectReturn_Detail.ProjReturnID = Milestone.ProjReturnID
WHERE (((Milestone.MandatoryMilestoneID)=1 Or (Milestone.MandatoryMilestoneID)=2 Or (Milestone.MandatoryMilestoneID)=3 Or (Milestone.MandatoryMilestoneID)=13))
GROUP BY ProjectReturn_Detail.DepartmentID, ProjectReturn_Detail.QTRID, ProjectReturn_Detail.ProjectID, ProjectReturn_Detail.ProjReturnID

 

[Isaac]

Not sure about that error, but

1) not sure how this is going to run without grouping by your last case statement. EVERYTHING has to be grouped by or aggregated
2) it's an odd way to structure a join, for sure. normal is:

from
table1 inner join table2 on table1.something=table2.something
table3 inner join table4 on table3.something=table4.something

I haven't used what you are doing at all, which is not to say it may not be one of the many ways things can be done in t-sql, just a bit diff.

 

[Mittle]

Not sure about that error, but

1) not sure how this is going to run without grouping by your last case statement. EVERYTHING has to be grouped by or aggregated
2) it's an odd way to structure a join, for sure. normal is:

from
table1 inner join table2 on table1.something=table2.something
table3 inner join table4 on table3.something=table4.something

I haven't used what you are doing at all, which is not to say it may not be one of the many ways things can be done in t-sql, just a bit diff.

I think the reason for the error is that the Case statement is using aliases defined just above it and that doesnt exist yet .

 

[Isaac]

May be true, I suspected that my answer wasn't the cause of your immediate error, but #1 certainly will have to be corrected either way

 

[Mittle]

May be true, I suspected that my answer wasn't the cause of your immediate error, but #1 certainly will have to be corrected either way

yes ive inherited an access database and I need to rewrite all the queries

 

[theDBguy]

yes ive inherited an access database and I need to rewrite all the queries

Perhaps you can post the query to convert, and maybe someone can help with that.

 

[Mittle]

this is the original access query and ive replaced the IIFs with Case statements SQL Server . the problem is am getting invalid column names because of the AS aliases .not sure how in access the queries run ok without issues .

I just need a work around to resolve the issue.Thanks to anyone that can help


SELECT
ProjectReturn_Detail.DepartmentID,
ProjectReturn_Detail.QTRID,
ProjectReturn_Detail.ProjectID,
ProjectReturn_Detail.ProjReturnID,
Min(IIf([ManMilestoneID]=2,[OriginalBaselineDate],Null)) AS EndDateBase,
Min(IIf([ManMilestoneID]=2,[LatestBaselineDate],Null)) AS EndDateLastBase,
Min(IIf([ManMilestoneID]=2,[Actual-ForecastDate],Null)) AS EndDateFor,
Min(IIf([ManMilestoneID]=3,[OriginalBaselineDate],Null)) AS KeyDelDateBase,
Min(IIf([ManMilestoneID]=3,[LatestBaselineDate],Null)) AS KeyDelDateLastBase,
Min(IIf([ManMilestoneID]=3,[Actual-ForecastDate],Null)) AS KeyDelDateFor,
Min(IIf([ManMilestoneID]=1,[OriginalBaselineDate],Null)) AS StartDateBase,
Min(IIf([ManMilestoneID]=1,[LatestBaselineDate],Null)) AS StartDateLastBase,
Min(IIf([ManMilestoneID]=1,[Actual-ForecastDate],Null)) AS StartDateFor,
Min(IIf([ManMilestoneID]=13,[OriginalBaselineDate],Null)) AS ProjEndDateBase,
Min(IIf([ManMilestoneID]=13,[LatestBaselineDate],Null)) AS ProjEndDateLastBase,
Min(IIf([ManMilestoneID]=13,[Actual-ForecastDate],Null)) AS ProjEndDateFor,
IIf([EndDateLastBase] Is Null,([EndDateBase]-[StartDateBase])/365.25,([EndDateLastBase]-[StartDateBase])/365.25) AS ProjectDuration, ([EndDateFor]-[StartDateFor])/365.25 AS ProjectDurationAF
FROM
ProjectReturn_Detail
INNER JOIN
(MandatoryMilestone
INNER JOIN Milestone
ON MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID)
ON ProjectReturn_Detail.ProjReturnID = Milestone.ProjReturnID
WHERE (((Milestone.MandatoryMilestoneID)=1 Or (Milestone.MandatoryMilestoneID)=2 Or (Milestone.MandatoryMilestoneID)=3 Or (Milestone.MandatoryMilestoneID)=13))
GROUP BY ProjectReturn_Detail.DepartmentID, ProjectReturn_Detail.QTRID, ProjectReturn_Detail.ProjectID, ProjectReturn_Detail.ProjReturnID;

 

[Minty]

It's difficult to test this but I think this would work

SELECT Q1.*,
CASE WHEN Q1.[EndDateLastBase] Is Null THEN (Q1.[EndDateBase]-Q1.[StartDateBase])/365.25 ELSE (Q1.[EndDateLastBase]- Q1.[StartDateBase])/365.25) END AS ProjectDuration,
(Q1.[EndDateFor]-Q1.[StartDateFor])/365.25 AS ProjectDurationAF
 FROM
(
SELECT
ProjectReturn_Detail.DepartmentID,
ProjectReturn_Detail.QTRID,
ProjectReturn_Detail.ProjectID,
ProjectReturn_Detail.ProjReturnID,

Min (CASE WHEN [ManMilestoneID]=2  THEN [OriginalBaselineDate] ELSE Null END) AS EndDateBase,
Min (CASE WHEN [ManMilestoneID]=2  THEN [LatestBaselineDate]   ELSE Null END) AS EndDateLastBase,
Min (CASE WHEN [ManMilestoneID]=2  THEN [Actual-ForecastDate]  ELSE Null END) AS EndDateFor,
Min (CASE WHEN [ManMilestoneID]=3  THEN [OriginalBaselineDate] ELSE Null END) AS KeyDelDateBase,
Min (CASE WHEN [ManMilestoneID]=3  THEN [LatestBaselineDate]   ELSE Null END) AS KeyDelDateLastBase,
Min (CASE WHEN [ManMilestoneID]=3  THEN [Actual-ForecastDate]  ELSE Null END) AS KeyDelDateFor,
Min (CASE WHEN [ManMilestoneID]=1  THEN [OriginalBaselineDate] ELSE Null END) AS StartDateBase,
Min (CASE WHEN [ManMilestoneID]=1  THEN [LatestBaselineDate]   ELSE Null END) AS StartDateLastBase,
Min (CASE WHEN [ManMilestoneID]=1  THEN [Actual-ForecastDate]  ELSE Null END) AS StartDateFor,
Min (CASE WHEN [ManMilestoneID]=13 THEN [OriginalBaselineDate] ELSE Null END) AS ProjEndDateBase,
Min (CASE WHEN [ManMilestoneID]=13 THEN [LatestBaselineDate]   ELSE Null END) AS ProjEndDateLastBase,
Min (CASE WHEN [ManMilestoneID]=13 THEN [Actual-ForecastDate]  ELSE Null END) AS ProjEndDateFor,


FROM
ProjectReturn_Detail
INNER JOIN
(MandatoryMilestone
INNER JOIN Milestone
ON MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID)
ON ProjectReturn_Detail.ProjReturnID = Milestone.ProjReturnID
WHERE Milestone.MandatoryMilestoneID IN (1,2,3,13)
GROUP BY ProjectReturn_Detail.DepartmentID, ProjectReturn_Detail.QTRID, ProjectReturn_Detail.ProjectID, ProjectReturn_Detail.ProjReturnID ) AS Q1

 

[Mittle]

It's difficult to test this but I think this would work

SELECT Q1.*,
CASE WHEN Q1.[EndDateLastBase] Is Null THEN (Q1.[EndDateBase]-Q1.[StartDateBase])/365.25 ELSE (Q1.[EndDateLastBase]- Q1.[StartDateBase])/365.25) END AS ProjectDuration,
(Q1.[EndDateFor]-Q1.[StartDateFor])/365.25 AS ProjectDurationAF
FROM
(
SELECT
ProjectReturn_Detail.DepartmentID,
ProjectReturn_Detail.QTRID,
ProjectReturn_Detail.ProjectID,
ProjectReturn_Detail.ProjReturnID,

Min (CASE WHEN [ManMilestoneID]=2  THEN [OriginalBaselineDate] ELSE Null END) AS EndDateBase,
Min (CASE WHEN [ManMilestoneID]=2  THEN [LatestBaselineDate]   ELSE Null END) AS EndDateLastBase,
Min (CASE WHEN [ManMilestoneID]=2  THEN [Actual-ForecastDate]  ELSE Null END) AS EndDateFor,
Min (CASE WHEN [ManMilestoneID]=3  THEN [OriginalBaselineDate] ELSE Null END) AS KeyDelDateBase,
Min (CASE WHEN [ManMilestoneID]=3  THEN [LatestBaselineDate]   ELSE Null END) AS KeyDelDateLastBase,
Min (CASE WHEN [ManMilestoneID]=3  THEN [Actual-ForecastDate]  ELSE Null END) AS KeyDelDateFor,
Min (CASE WHEN [ManMilestoneID]=1  THEN [OriginalBaselineDate] ELSE Null END) AS StartDateBase,
Min (CASE WHEN [ManMilestoneID]=1  THEN [LatestBaselineDate]   ELSE Null END) AS StartDateLastBase,
Min (CASE WHEN [ManMilestoneID]=1  THEN [Actual-ForecastDate]  ELSE Null END) AS StartDateFor,
Min (CASE WHEN [ManMilestoneID]=13 THEN [OriginalBaselineDate] ELSE Null END) AS ProjEndDateBase,
Min (CASE WHEN [ManMilestoneID]=13 THEN [LatestBaselineDate]   ELSE Null END) AS ProjEndDateLastBase,
Min (CASE WHEN [ManMilestoneID]=13 THEN [Actual-ForecastDate]  ELSE Null END) AS ProjEndDateFor,


FROM
ProjectReturn_Detail
INNER JOIN
(MandatoryMilestone
INNER JOIN Milestone
ON MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID)
ON ProjectReturn_Detail.ProjReturnID = Milestone.ProjReturnID
WHERE Milestone.MandatoryMilestoneID IN (1,2,3,13)
GROUP BY ProjectReturn_Detail.DepartmentID, ProjectReturn_Detail.QTRID, ProjectReturn_Detail.ProjectID, ProjectReturn_Detail.ProjReturnID ) AS Q1

 

[Mittle]

Thanks so much . This issue has been resolved and I did exactly what you did using a subquery

 

[Mittle]

actually I did not use the Q1. alias in the CASE Statement but I wrapped the Subquery and gave it an alias Q1 like you have done and it worked

 

[Minty]

I normally do that to keep track of which sub query I'm using!
Once you get three or four on the go it can get a bit hairy keeping tabs of which one you are pulling the data from.

It's a good habit to call them something more meaningful ( but still short!) Q1 works great if it is just the one though.

 

[Mittle]

Correct .arrh thank you I will add the alias then . you are right Q1 works great if is just the one

 

[Galaxiom]

Not sure about that error, but

1) not sure how this is going to run without grouping by your last case statement. EVERYTHING has to be grouped by or aggregated
2) it's an odd way to structure a join, for sure. normal is:

from
table1 inner join table2 on table1.something=table2.something
table3 inner join table4 on table3.something=table4.something

I haven't used what you are doing at all, which is not to say it may not be one of the many ways things can be done in t-sql, just a bit diff.

Yes. Those odd Joins are Access syntax which demands they be nested. TSQL is much simpler.

Note it is not necessary to include ELSE NULL in a CASE statement because Null is automatically the outcome if no case is met.

 

[Isaac]

Note it is not necessary to include ELSE NULL in a CASE statement because Null is automatically the outcome if no case is met.

I'm not totally sure if I even knew that. Thanks

 

[Galaxiom]

BTW These are personal syntax preferences that I recommend.
I have queries with over 1300 lines (yes many are blank) and multiple levels of subqueries.
Clear formatting is essential to be able to follow them.

Put every column on its own line.
Locate the commas at the beginning of the lines where they are easy see. (It is easier in see then in MSSQLSMS than here.)
Separate the key phrases with a blank line.
Indent the subqueries.

I may have messed up the sql but you get the idea. The forum also messes with the indenting

SELECT 
      Q1.*

      ,CASE 
            WHEN Q1.[EndDateLastBase] Is Null 
            THEN (Q1.[EndDateBase]-Q1.[StartDateBase])/365.25 
            ELSE (Q1.[EndDateLastBase]- Q1.[StartDateBase])/365.25) 
      END AS ProjectDuration

     ,(Q1.[EndDateFor]-Q1.[StartDateFor])/365.25 AS ProjectDurationAF

 FROM
      (
        SELECT
            ProjectReturn_Detail.DepartmentID
            ,ProjectReturn_Detail.QTRID
            ,ProjectReturn_Detail.ProjectID
            ,ProjectReturn_Detail.ProjReturnID

            ,Min(
                   CASE 
                      WHEN [ManMilestoneID]=2  THEN [OriginalBaselineDate]
                   END
                  ) AS EndDateBase

                   etc

             FROM
                  ProjectReturn_Detail

              INNER JOIN
                   Milestone
                   ON Milestone.ProjReturnID = ProjectReturn_Detail.ProjReturnID

              INNER JOIN 
                   MandatoryMilestone
                   ON  MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID

              WHERE Milestone.MandatoryMilestoneID IN (1,2,3,13)

              GROUP BY 
                    ProjectReturn_Detail.DepartmentID
                    ,ProjectReturn_Detail.QTRID
                    ,ProjectReturn_Detail.ProjectID
                    ,ProjectReturn_Detail.ProjReturnID 

           ) AS Q1

I also often replace this

      FROM
            ProjectReturn_Detail

       INNER JOIN
            Milestone
            ON Milestone.ProjReturnID = ProjectReturn_Detail.ProjReturnID

        INNER JOIN 
             MandatoryMilestone
             ON  MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID

        WHERE Milestone.MandatoryMilestoneID IN (1,2,3,13)

with this

      FROM
            ProjectReturn_Detail

       INNER JOIN
            Milestone
            ON Milestone.ProjReturnID = ProjectReturn_Detail.ProjReturnID
            AND Milestone.MandatoryMilestoneID IN (1,2,3,13)

       INNER JOIN 
             MandatoryMilestone
             ON  MandatoryMilestone.ManMilestoneID = Milestone.MandatoryMilestoneID