Wednesday, March 06, 2013 3:44 PM
I just want to report a bug I found. I've recently updated a database from SQL 2008 to SQL 2012. This morning a daily procedure failed. Upon further investigation I found a view had been updated as follows:SELECT PolicyKey, Policy, PolicyStartDate, Sequence, Amendment, Status, RatingStatus, OldPolicy, ClientNumber, TransactionType, EffectiveDate, BillingType, NextBillingType, Reversed, BusinessLine, BranchCode, GroupCode, BrokerCode, PolicyEndDate, PreviousInsurerCode, PreviousPolicy, RequestLetter, RenewalCode, RenewalBusinessLine, NonRenewalRemark, AmendmentCode, AmendmentRemark, InsuredSince, InceptionDate, DatePrinted, PostDate, DatePosted, ShortRate, Subscription, LeadPolicyNumber, PrintPolicy, BusinessType, BrokersCancelDate, UploadedFromBroker, BrokerIDForInsured, IneligibleforPHIX AS HeldBy, HeldBy AS DateHeld, DateHeld AS HeldByUniID, HeldByUniID AS AddedBy, AddedBy AS DateAdded, DateAdded AS ChangedBy, ChangedBy AS DateChanged, DateChanged
Please note the section where a field had been added to the table (IneligibleforPHIX) and SQL tried to compensate by adding the AS keyword to retain position. This ultimately offset the entire result set by 1. IE: 1 as 2, 2 as 3, 3 as 4, 4 as 5... etc. and completely screwed up the data selection from it.