CREATE VIEW dbo.PLAS_LIST
AS
SELECT        SPECODE2, CITY, MANG, COLOR_, LAT, LNG, MANG_COLOR
FROM            (SELECT        CASE WHEN CC.SPECODE2 = 'ÜMIT' AND CC.CITY = 'Istanbul' THEN 'MUHAMMED' ELSE CC.SPECODE2 END AS SPECODE2, CC.CITY, NG.MANG, CASE WHEN CC.SPECODE2 = 'ÜMIT' AND 
                                                    CC.CITY = 'Istanbul' THEN 'LightSeaGreen' ELSE NG.COLOR_ END AS COLOR_, CASE WHEN CC.SPECODE2 = 'FATIH' AND CC.CITY = 'Istanbul' THEN '41,2444' ELSE CM.LAT END AS LAT, CM.LNG, 
                                                    NG.MANG_COLOR
                          FROM            dbo.LG_011_CLCARD AS CC LEFT OUTER JOIN
                                                    dbo.CITY_MAP AS CM ON CM.CITY = CC.CITY LEFT OUTER JOIN
                                                    dbo.CITY_MANG AS NG ON NG.PLAS = CC.SPECODE2
                          WHERE        (CC.ACTIVE = 0) AND (CC.SPECODE2 <> '') AND (CC.LOGICALREF NOT IN (3017, 3055))) AS QQ
GROUP BY SPECODE2, CITY, MANG, COLOR_, LAT, LNG, MANG_COLOR