Formatter removes table from columns in WHERE clause even when it's ambiguous otherwise

dbrinkerhoff

First, here is the sample code:

   CREATE TABLE dbo.#Isolates
      (
         MICSourceID                    VARCHAR(3)
        ,VisitID                        VARCHAR(256)
        ,PatientID                      VARCHAR(256)
        ,AccountNumber                  VARCHAR(256)
        ,UnitNumber                     VARCHAR(256)
        ,EmrNumber                      VARCHAR(256)
        ,Name                           VARCHAR(256)
        ,NameLast                       VARCHAR(256)
        ,NameFirst                      VARCHAR(256)
        ,BirthDateTime                  DATETIME
        ,Sex                            VARCHAR(256)
        ,InAdmitDateTime                DATETIME
        ,FacilityID                     VARCHAR(256)
        ,SpecimenID                     VARCHAR(255)
        ,SpecimenNumber                 VARCHAR(255)
        ,CollectionDateTime             DATETIME
        ,CollectionDate                 DATE
        ,[Status]                       VARCHAR(255)
        ,CollectionLocationID           VARCHAR(255)
        ,[Source]                       VARCHAR(255)
        ,ProcedureID                    VARCHAR(256)
        ,ResultSeqID                    INT
        ,OrganismID                     VARCHAR(256)
        ,DMicOrganismID                 VARCHAR(256)
        ,OrganismName                   VARCHAR(256)
        ,OrganismCode                   VARCHAR(255)
        ,DuplicateOrganismCode          VARCHAR(255)
        ,MTOrganismCode                 VARCHAR(255)
        ,AROPathogenCategory            VARCHAR(256)
        ,OrganismPreferredTerm          VARCHAR(256)
        ,EligibleAntimicrobialType      VARCHAR(256)
        ,LocationEntryDescription       VARCHAR(256)
        ,LocationCode                   VARCHAR(256)
        ,LocationDescription            VARCHAR(256)
        ,LocationLabel                  VARCHAR(256)
        ,InpatientLocation              CHAR(1)
        ,DMicSourceID                   VARCHAR(256)
        ,SourceName                     VARCHAR(256)
        ,SourceNHSNCode                 VARCHAR(256)
        ,SourceConceptID                VARCHAR(256)
        ,SourcePreferredTerm            VARCHAR(256)
        ,SourceARSpecimenSource         CHAR(1)
        ,SourceLowerResp                CHAR(1)
        ,SourceBlood                    CHAR(1)
        ,SourceCSF                      CHAR(1)
        ,SourceUrine                    CHAR(1)
        ,SourceType                     CHAR(1)
        ,StaphylococcusAureus           CHAR(1)
        ,PBP2aMTResult                  VARCHAR(256)
        ,PBP2aNHSNResult                VARCHAR(3)
        ,PCRMecMTResult                 VARCHAR(256)
        ,PCRMecNHSNResult               VARCHAR(3)
        ,EscapedIdentifierFileName      NVARCHAR(MAX)
        ,EscapedIdentifierSetID         NVARCHAR(MAX)
        ,IsolateCollectedDuringMonth    CHAR(1)
        ,FinalStatus                    CHAR(1)
        ,OrganismInNHSNCodeset          CHAR(1)
            DEFAULT 'N'
        ,LocationAUR                    CHAR(1)
        ,LocationDefaultCode            CHAR(1)
        ,SourceInNHSNCodeset            CHAR(1)
            DEFAULT 'N'
        ,IsolateIdentifierTooLong       CHAR(1)
            DEFAULT 'N'
        ,NoTestsDone                    CHAR(1)
            DEFAULT 'N'
        ,PatientIdentifierInvalid       CHAR(1)
            DEFAULT 'N'
        ,LocationAgeGenderRulesCheck    CHAR(1)
            DEFAULT 'Y'
        ,AdmitDateTimeInvalid           CHAR(1)
            DEFAULT 'N'
        ,BirthDateTimeInvalid           CHAR(1)
            DEFAULT 'N'
        ,SameDayDuplicateGroup          BIGINT
        ,SameDayDuplicate               CHAR(1)
            DEFAULT 'N'
        ,LastInvasiveResult14DaysOrLess CHAR(1)
            DEFAULT 'N'
        ,FirstNonInvasiveResultInMonth  CHAR(1)
            DEFAULT 'Y'
        ,IsolateCountedForReport        CHAR(1)
      );

	   WITH CTEIsolateMonth
     AS
        ( SELECT
             I.MICSourceID
            ,I.PatientID
            ,I.SpecimenID
            ,I.CollectionDateTime
            ,I.ProcedureID
            ,I.ResultSeqID
            ,COALESCE(I.DuplicateOrganismCode, I.OrganismCode) AS OrganismCode
            ,I.OrganismID
            ,I.FirstNonInvasiveResultInMonth
            ,I.IsolateCountedForReport
            ,ROW_NUMBER() OVER ( PARTITION BY
                                    I.PatientID
                                   ,COALESCE(I.DuplicateOrganismCode, I.OrganismCode)
                                 ORDER BY
                                    I.CollectionDateTime
                               ) AS RowNum
          FROM
             dbo.#Isolates AS I
          WHERE
             /* Only those in the calendar month count for the report */
             I.IsolateCountedForReport = 'Y'

             /* Non-Invasive Source Type */
             AND I.SourceType = 'N' )
   UPDATE
      dbo.#Isolates
   SET
      FirstNonInvasiveResultInMonth = 'N'
     ,IsolateCountedForReport = 'N'
   WHERE
      #Isolates.PatientID = CTEIsolateMonth.PatientID
      AND COALESCE(#Isolates.DuplicateOrganismCode, #Isolates.OrganismCode) = CTEIsolateMonth.OrganismCode
      AND #Isolates.CollectionDateTime = CTEIsolateMonth.CollectionDateTime
      AND CTEIsolateMonth.RowNum > 1;

The behavior I'm getting when formatting with SQL Prompt is:
1) It strips away all of the "#Isolates" in the WHERE clause.
2) Then, if I format a second time, it inserts "CTEIsolateMonth" where the "#Isolates" was, for everything except DuplicateOrganismCode (which doesn't exist in CTEIsolateMonth).
This changes the meaning of the code, obviously.  Is there a setting I can use to tell it to hang on to the existing tablename, whether the format tool thinks it's necessary or not?

ATurner

Hi dbrinkerhoff 

I am currently unable to replicate this issue so I might need a few things from you, so I'm going to raise a ticket.