跨数据库

USE [DW]
GO

/****** Object: View [dbo].[V_MetaData_Shop] Script Date: 03/02/2011 21:37:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[V_MetaData_Shop]
AS
SELECT obj.ShopID, obj.ShopShortName, obj.ShopFullName, obj.ShopCompanyName, obj.ShopArea, obj.FactoryBrand,
obj.ShopOnBoardDate AS newShopBoardDate, obj.IfExistCompetition,
obj.ShopLevel,

obj.CarCityName, obj.CreateBy, obj.CreateDate, obj.ModifyBy,
                      obj.ModifyDate, obj.IsAvailable, obj.ShopType, obj.OrderID AS ShopOnBoardDate, aobj.City, obj.OrderID AS ShortID,
                      CASE WHEN aobj.City = '徐州' THEN 'AAAA' WHEN aobj.City = '淮安市' THEN 'AAAB' WHEN aobj.City = '连云港市' THEN 'AAAC' END AS CityShort,
                      obj.BrandLevel AS Barand,
case when obj.ShopLevel=0 then 'A'
when obj.ShopLevel=1 then 'B'
when obj.ShopLevel=2 then 'C'
when obj.ShopLevel=3 then 'D'
END AS Level, obj.BrandLevel AS BrandLevel ,obj.SaleAfterOrderId
,
ShopOnBoardDateType=case when DATEDIFF(year,
                      ShopOnBoardDate, GETDATE()) >0 then '原有店'
                      else '现有店'
                      end,isnull(a.RegionName,'未知') as RDShopEmpty1,
                      ISNULL(c.Name,'未知') as Name

FROM         dbo.tbl_MetaData_Shop obj LEFT OUTER JOIN
                      dbo.V_MetaData_Area aobj ON obj.ShopArea = aobj.AreaID
                      left outer join dbo.tbl_MetaData_Region a on a.RegionID=obj.RegionID
                      left outer join BISystem..tblUser c on c.UserId=obj.KeepShoper
UNION
SELECT     '未知' AS Expr1, '未知' AS Expr2, '未知' AS Expr3, '未知' AS Expr4, '' AS Expr5, '未知' AS Expr6, '2010-05-17' AS Expr7, 1 AS Expr8, 0 AS Expr9,
                      '' AS Expr10, 'Mark' AS Expr11, '2010-05-17' AS Expr12, NULL AS Expr13, NULL AS Expr14, 1 AS Expr15, NULL AS Expr16, 'zzzz', '未知', 'ZZZZ',
                      'ZZZZ','高端' ,'A','高端','ZZZ','现有店','未知','未知'