SQL case statement in a View

Question

ALTER View Myview AS

(SELECT
        TOP 1 ORGANIZATION_id
    FROM ORGANIZATION 
    WHERE code = mo.FunctionCode) org_id,
(SELECT
        TOP 1 ID
    FROM DEPARTMENT
    WHERE [NAME] = mo.Code) dep_id,
(SELECT
        TOP 1 Name
    FROM DEPARTMENT
    WHERE [ID] = dep_id) Dep_Name,
(SELECT
        TOP 1 Org_Name
    FROM CA_ORGANIZATION
    WHERE [organization_id] = org_id) Org_Name,
(CASE    
    WHEN Dep_Name = 'sales' AND Org_Name = 'Advanture'
        THEN 'salesTeam'
    WHEN Dep_Name ! = 'Sales' AND Org_Name = 'External'
        THEN 'ExternalTeam'
    ELSE
        'DefaultTeam'
    END) type

    FROM detail_view dv
    LEFT OUTER JOIN Global_oganization mo  ON  mo.area =dv.code

Error while altering the view is

Invalid column name 'Dep_Name'.
Msg 207, Level 16, State 1, Procedure myview,
Invalid column name 'Org_Name'.
Msg 207, Level 16, State 1, Procedure myview,
Invalid column name 'Dep_Name'.
Msg 207, Level 16, State 1, Procedure myview,
Invalid column name 'Org_Name'.

I have written CASE statement to validate the department name and organization name to decide the type.

Help me out to fix this error.

Answer 1

Try this one -

ALTER VIEW dbo.Myview
AS
    SELECT  /*missing in your query*/
            t.org_id
        ,   t.dep_id
        ,   t.Dep_Name
        ,   t.Org_Name
        ,   [type] = 
            CASE
                WHEN Dep_Name = 'sales' AND Org_Name = 'Advanture' THEN 'salesTeam'
                WHEN Dep_Name != 'Sales' AND Org_Name = 'External' THEN 'ExternalTeam' 
                ELSE 'DefaultTeam'
            END
    FROM (
        SELECT org_id = (
                    SELECT TOP 1 ORGANIZATION_id
                    FROM dbo.ORGANIZATION
                    WHERE Code = mo.FunctionCode
                )
            , dep_id = (
                    SELECT TOP 1 ID
                    FROM dbo.Department
                    WHERE [name] = mo.Code
                )
            , Dep_Name = (
                    SELECT TOP 1 name
                    FROM dbo.Department
                    WHERE [ID] = dep_id
                )
            , Org_Name = (
                    SELECT TOP 1 Org_Name
                    FROM dbo.CA_ORGANIZATION
                    WHERE [organization_id] = org_id
                )
        FROM dbo.detail_view dv
        LEFT JOIN dbo.Global_oganization mo ON mo.area = dv.Code
    ) t