Formatting of CREATE VIEW fails

steveculshaw

The formatting of a CREATE VIEW statement seems to be failing with SP4

e.g. my original SQL with SP3.9 was like so ...

CREATE VIEW HBenPersonNewCreate
AS  
SELECT  HBen.* ,
        HBenChkSum.StateFlag ,
        HBenChkSum.PersonFlag ,
        HBenChkSum.PersonCk ,
        HBenChkSum.DeliveryPointCk ,
        HBenChkSum.DateProcessed ,
        HBenChkSum.InvalidDataFlag
FROM    HBen
        INNER JOIN HBenChkSum ON HBen.ClaimRef = HBenChkSum.ClaimRef
WHERE   HBenChkSum.PersonFlag = 'N'
        AND ( HBenChkSum.StateFlag = 'C'
              OR HBenChkSum.StateFlag = 'U'
              OR HBenChkSum.StateFlag = 'X'
            )
        AND DateProcessed IS NULL
        AND DeliveryPointCk IS NOT NULL
        AND ( HBen.Surname <> '_'
              AND HBen.Forename <> '_'
              AND HBen.Title <> '_'
            )
GO

but after SP4 it's ...

CREATE VIEW HBenPersonNewCreate
AS  
SELECT  HBen.* ,
HBenChkSum.StateFlag ,
HBenChkSum.PersonFlag ,
HBenChkSum.PersonCk ,
HBenChkSum.DeliveryPointCk ,
HBenChkSum.DateProcessed ,
HBenChkSum.InvalidDataFlag
FROM    HBen
INNER JOIN HBenChkSum ON HBen.ClaimRef = HBenChkSum.ClaimRef
WHERE   HBenChkSum.PersonFlag = 'N'
AND ( HBenChkSum.StateFlag = 'C'
OR HBenChkSum.StateFlag = 'U'
OR HBenChkSum.StateFlag = 'X'
)
AND DateProcessed IS NULL
AND DeliveryPointCk IS NOT NULL
AND ( HBen.Surname <> '_'
AND HBen.Forename <> '_'
AND HBen.Title <> '_'
)

stuart_tetley

I have discovered this same problem. It appears to have been reported separately, confirmed, and logged to be addressed in the next release:

http://www.red-gate.com/MessageBoard/vi ... php?t=9454

As a workaround, temporarily commenting or cutting out the CREATE VIEW statement allows SP4 to format the view correctly.

steveculshaw

Thanks for the pointer ... hope it's a quick fix !

I've found I can Ctrl+K, Ctrl+Y and do the entire file, then select the SELECT block and Ctrl+K, Ctrl+Y to format it

stuart_tetley wrote:

I have discovered this same problem. It appears to have been reported separately, confirmed, and logged to be addressed in the next release:

http://www.red-gate.com/MessageBoard/vi ... php?t=9454

As a workaround, temporarily commenting or cutting out the CREATE VIEW statement allows SP4 to format the view correctly.