需要帮助修改SQL视图

I have a SQL view created already & that view gives certain columns from a database table Data. Now, I want to modify that view in such a manner that it does not need to add any additional columns to the result & should match exactly with the same result as this view generated before. The only modification I want here is: There is a column called PayDate that the view takes from Data table. I need that column to have value depending on other column called OrderType. The Data table looks like :

我已经创建了一个SQL视图,该视图提供了数据库表Data中的某些列。现在,我想以这样一种方式修改该视图,即它不需要在结果中添加任何其他列,并且应该与之前生成的视图完全匹配。我想要的唯一修改是:视图从Data表中获取一个名为PayDate的列。我需要该列具有值,具体取决于名为OrderType的其他列。数据表如下所示:

Book    OrderType   PayDate      ValuDate
-------------------------------------------
FFF     CA          21/05/2015   NULL
BBB     CO          NULL         2/02/2014
EEE     IO          NULL         3/2/2014
QQQ     Dividend    02/02/2014   NULL

Currently, the view gives OrderType & Paydate as a result. Twist here is, for OrderType = 'CA' OR 'Dvidend' we have PayDate . For OrderType = 'CO' OR 'IO' we have ValuDate. Each time for 'CO' or 'IO' the value got is NULL. So here I want a CASE that will check if OrderType is 'CO' OR 'IO' it'll return ValuDate in PayDate column. And at places where OrderType is 'CA' or 'Dividend' give value of PayDate from Data table in PayDate column of the view.

目前,该视图将OrderType和Paydate作为结果。扭曲在这里,对于OrderType ='CA'或'Dvidend',我们有PayDate。对于OrderType ='CO'或'IO',我们有ValuDate。每次'CO'或'IO'得到的值都是NULL。所以在这里我想要一个CASE来检查OrderType是'CO'还是'IO'它将在PayDate列中返回ValuDate。在OrderType为“CA”或“Dividend”的地方,从视图的PayDate列的Data表中提供PayDate的值。

PS : Do not want to add a ValuDate column here. All we need is just put the value of ValueDate in PayDate column of view if OrderType is 'CO' or 'IO'`

PS:不想在这里添加ValuDate列。如果OrderType是'CO'或'IO',我们只需要将ValueDate的值放在PayDate列的视图中

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ABC]'))
BEGIN
    DROP VIEW [dbo].[ABC]
    PRINT '<<< DROPPED VIEW [dbo].[ABC] >>>'
END
GO  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ABC]
AS

    SELECT  ISNULL(Book, '') + '_' + ISNULL(OrderType,'') + '_'
            + ISNULL(CONVERT(VARCHAR(8), PayDate, 112), '') + '_'
            MAX(t.[InstrumentKey]) [InstrumentKey] ,
            MAX(t.[BookKey]) [BookKey] ,
            t.[OrderType],
            MAX(t.[InstrumentID]) [InstrumentID] ,
            t.PayDate ,
            FROM    dbo.Data t (NOLOCK)
    LEFT OUTER JOIN dbo.ExtInstrument i ON t.InstrumentKey = i.InstrumentKey

    WHERE [STATUS] = 'LIVE'

    GROUP BY Book ,
            OrderType ,
            PayDate ,
            BackToBackBook 


GO
PRINT '<<< CREATED VIEW [dbo].[ABC] >>>'

GRANT SELECT ON dbo.ABC TO INVENTORY_READERS
GO

PRINT '<<< GRANT SELECT ON  VIEW [dbo].[ABC] TO  INVENTORY_READERS  >>>'
GO

2 个解决方案

#1

Trying to follow what you are asking. Is this what you mean?

试着按照你的要求去做。你是这个意思吗?

CREATE VIEW [dbo].[ABC]
AS

SELECT  ISNULL(Book, '') + '_' + ISNULL(OrderType,'') + '_'
        + ISNULL(CONVERT(VARCHAR(8), PayDate, 112), '') + '_'
        MAX(t.[InstrumentKey]) [InstrumentKey] ,
        MAX(t.[BookKey]) [BookKey] ,
        t.[OrderType],
        MAX(t.[InstrumentID]) [InstrumentID] ,
        CASE WHEN OrderType in ('CO','IO') THEN t.ValuDate ELSE t.PayDate END AS PayDate,
        FROM    dbo.Data t (NOLOCK)
LEFT OUTER JOIN dbo.ExtInstrument i ON t.InstrumentKey = i.InstrumentKey

WHERE [STATUS] = 'LIVE'

GROUP BY Book ,
        OrderType ,
        CASE WHEN OrderType in ('CO','IO') THEN t.ValuDate ELSE t.PayDate END ,
        BackToBackBook

EDIT: I suspect the first part needs to be

编辑:我怀疑第一部分需要

SELECT  ISNULL(Book, '') + '_' + ISNULL(OrderType,'') + '_'
        + ISNULL(CONVERT(VARCHAR(8), CASE WHEN OrderType in ('CO','IO') THEN t.ValuDate ELSE t.PayDate END, 112), '') + '_'

#2

This is something which I have done. But I want to use 'UNION' & get the view modified. Can we use UNION in anyways to modify this view??

这是我做过的事情。但我想使用'UNION'并修改视图。我们可以使用UNION来修改这个视图吗?

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ABC]'))
BEGIN
    DROP VIEW [dbo].[ABC]
    PRINT '<<< DROPPED VIEW [dbo].[ABC] >>>'
END
GO  

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ABC]
AS


SELECT  ISNULL(Book, '') + '_' + ISNULL(OrderType,'') + '_'
        + ISNULL(t.RIC, '') + '_'
        + CASE OrderType
                            WHEN OrderType = 'PNoteCorporateAction' OR 'PNoteDividendDraft' OR 'PNoteDividend' THEN ISNULL(CONVERT(VARCHAR(8), PayDate, 112), '') 
            ELSE ISNULL(CONVERT(VARCHAR(8), ValuDate, 112), '') 
          END AS PayDate + '_'  
        MAX(t.[InstrumentKey]) [InstrumentKey] ,
        MAX(t.[BookKey]) [BookKey] ,
        t.[OrderType],
        MAX(t.[InstrumentID]) [InstrumentID] ,
        t.PayDate ,
        FROM    dbo.Data t (NOLOCK)
LEFT OUTER JOIN dbo.ExtInstrument i ON t.InstrumentKey = i.InstrumentKey

WHERE [STATUS] = 'LIVE'

GROUP BY Book ,
        OrderType ,
        CASE WHEN OrderType = 'PNoteCorporateAction' OR 'PNoteDividendDraft' OR 'PNoteDividend' THEN PayDate ELSE ValueDate END AS PayDate,
        BackToBackBook 


GO
PRINT '<<< CREATED VIEW [dbo].[ABC] >>>'

GRANT SELECT ON dbo.ABC TO INVENTORY_READERS
GO

PRINT '<<< GRANT SELECT ON  VIEW [dbo].[ABC] TO  INVENTORY_READERS  >>>'
GO

#1