What are the challenges you face when working across database platforms? Take the survey
Options

Formatting of CREATE VIEW fails

steveculshawsteveculshaw Posts: 16 Bronze 2
edited August 25, 2009 5:26PM in SQL Prompt Previous Versions
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 <> '_'
)
Cheers,
SteveC.

Comments

Sign In or Register to comment.