Another free T-SQL Formatter, open-source and SQL2K8-compatible, SSMS Add-In

  • Hi,

    I recently completed the first non-beta release of the "Poor Man's T-SQL Formatter" library, SSMS Add-In, online formatter, etc, and I thought I'd post a quick announcement here in case it's useful to someone:

    http://www.architectshack.com/PoorMansTSqlFormatter.ashx

    It's a "full script" formatter, intended to handle multi-batch scripts with complex DDL & DML; so far over the last 2 weeks, I haven't received any bug reports / cases where any SQL gets mangled, but I'm still waiting for interesting bug reports to come in! 🙂

    It supports a few formatting options at the moment, but not a particularly large set - if there's any particular formatting option or style that someone would like to see, I'd love to hear about it!

    The formatter is available for immediate use online at http://poorsql.com, and can be downloaded for use as an SSMS Add-In (2005 & 2008 supported - Denali support can probably be added pretty easily if anyone's looking for it). It's fully open-source (AGPL), so anyone is free to modify and redistribute it as long as they retain the license.

    If you get the chance, I'd be really grateful if you could take a look, and tell me:

    • If it's useful, what would you like to see added to make it more useful?
    • If it's not useful, why not? Are you already using a commercial tool like SQL Prompt, or do you not need a formatter, or do you not like something about the way it formats or the options it supports? (or some other reason?)

    Again, any and all feedback would be hugely appreciated!

    Thanks,

    Tao

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • That's great stuff!

    Thanks for sharing.

    -- Gianluca Sartori

  • Thanks!

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • I like the tool a lot. I was looking for a tool to batch format sql scripts for the longest. I would like to drop some feedback after playing with it after couple of minutes.

    For #1 I wish the comma delimited list would fit the current line. For #2 I wish there was no newline after "CAST((" and no newline before ") AS VARCHAR(6))". For #3 a newline got lost. For #4 no newline for column list and after, no newline for options after with and no newline before the last comma

    #1

    CASE

    WHEN lt.[T_SLS_DEED_CAT_TYP] = 'X'

    THEN CASE

    WHEN lt.[T_SLS_DEED_CD] IN (

    'MG',

    'TR',

    'EQ',

    'AI',

    'AP',

    'CL',

    'DP',

    'RV',

    'IM',

    'SE',

    'TL',

    'MO'

    )

    THEN 'T'

    ELSE 'G'

    END

    ELSE lt.[T_SLS_DEED_CAT_TYP]

    END AS DeedCatTyp,

    #2

    CAST((

    SELECT OutVal

    FROM Util.dbo.RemoveLeadingZerosInline(lt.T_BUY_ADDR_APT_NBR)

    ) AS VARCHAR(6)) AS BuyAddrAptNbr,

    #3

    ) mtgcnt

    OPTION (RECOMPILE)

    --#endregion

    formatted to

    ) mtgcnt

    OPTION (RECOMPILE) --#endregion

    #4

    CREATE UNIQUE NONCLUSTERED INDEX [TransId] ON [DiabloStg-Diablo].[DataFolder-Trans_view] (

    [DataSupplierId] ASC,

    [TransId] ASC

    )

    WITH (

    DATA_COMPRESSION = ROW,

    SORT_IN_TEMPDB = ON

    )

  • Thanks for the feedback gvarol!

    #1: I think you can just uncheck the "Expand Comma Lists" option, or am I missing something? If you want to wrap based on a specific line width, you can also set the "Max Width" option to a more sensible value - the 999 default is just to keep it out of the way.

    #2: It's definitely by design that subqueries and derived tables are always indented... Are you saying that you'd want the whole thing on one line, or that you would just want the "SELECT OutVal" portion on the same line as the "CAST((", and wrapping afterwards?

    #3: Looks like a bug, I'll definitely look into it!

    #4: Removing most of those newlines is just a question of unchecking the "Expand Comma Lists" option; the one that you won't be able to remove is the one just before OPTION - I'm always breaking the OPTION clause out. You're saying that you'd like the whole thing to appear on a single line?? Or am I misunderstanding something?

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Tao Klerks (9/20/2011)


    #4: Removing most of those newlines is just a question of unchecking the "Expand Comma Lists" option; the one that you won't be able to remove is the one just before OPTION - I'm always breaking the OPTION clause out. You're saying that you'd like the whole thing to appear on a single line?? Or am I misunderstanding something?

    Sorry, I meant "WITH", not OPTION - was looking in the wrong place (and yes, I also always break on OPTION); so, I always break on ddl WITH clauses - but I guess I could make an option for that if it made sense 🙂

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • It gets complicated, I guess more options will be necessary. In my case I do not want the full insert statement that has 100+ columns followed by comma space but I want them in the new line, but the strings in the IN statement I want them in the same line.

  • the simplest thing is to send me two things, for each situation where you'd like the result to be different:

    • The original SQL, unformatted
    • The SQL formatted exactly how you'd want it

    You can do this here in the forum, by using "" tags to preserve formatting (be sure to preview your post before submitting), or you can just send me an email.

    Then I can take a look to see how practical it would be to change the behaviour across the board, or add an option, etc. As there aren't many users yet I still have a lot of freedom - to a certain extent it's first come first served for enhancements.

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • I prefer the below code not to change when I format

    -- OBJECT transview HAS NOT BEEN LOCATED. DOING A GENERAL SEARCH

    GO

    ALTER PROCEDURE Template.[DataFolder-TransformTransActionTransViewIns]

    @CntyCdList VARCHAR(MAX)

    AS

    SET NOCOUNT ON ;

    IF OBJECT_ID('tempdb..#CNTYCD') IS NOT NULL

    DROP TABLE #CNTYCD ;

    CREATE TABLE #CNTYCD (CNTY_CD CHAR(5) NOT NULL PRIMARY KEY CLUSTERED) ;

    INSERT INTO #CNTYCD (CNTY_CD)

    SELECT CNTY_CD

    FROM Control.GetCntyListFromDelimited(@CntyCdList) AS l ;

    --#region Conditional Table Create

    IF CHARINDEX('-', '[DiabloStg-Diablo]', 0) = 0 -- ONLY DROP AND RECREATE TABLES ON RUNTIME

    BEGIN

    IF OBJECT_ID('[DiabloStg-Diablo].[DataFolder-Trans_view]') IS NOT NULL

    DROP TABLE

    [DiabloStg-Diablo].[DataFolder-Trans_view] ;

    CREATE TABLE [DiabloStg-Diablo].[DataFolder-Trans_view] (DataSupplierId SMALLINT NOT NULL,

    CntyCd CHAR(5) NOT NULL,

    TransId INT NOT NULL,

    BatchDt INT NOT NULL,

    BatchSeq INT NOT NULL,

    MtgSeq TINYINT NOT NULL,

    UnvPropEdition TINYINT NULL,

    UnvPropId INT NULL,

    MtgChronoNbr VARCHAR(16) NULL,

    DocNbr VARCHAR(12) NULL,

    OrigDocNbr VARCHAR(12) NULL,

    OrigRecordingBook VARCHAR(6) NULL,

    OrigRecordingPage VARCHAR(6) NULL,

    RecordingBook VARCHAR(6) NULL,

    RecordingPage VARCHAR(6) NULL,

    SecDCArmsLengthCashInd BIT NOT NULL,

    SecDCArmsLengthMortgageInd BIT NOT NULL,

    SecDCInterrelatedInd BIT NOT NULL,

    SecDCInvestorPurchaseInd BIT NOT NULL,

    SecDCNewConstructionInd BIT NOT NULL,

    SecDCRealEstateOwnedInd BIT NOT NULL,

    SecDCRealEstateOwnedSaleInd BIT NOT NULL,

    SecDCResaleInd BIT NOT NULL,

    SecDCResModelInd BIT NOT NULL,

    SecDCShortSaleInd BIT NOT NULL,

    UpdateId INT NULL,

    EditorId INT NULL,

    AbsenteeIrisCd CHAR(1) NULL,

    AcctNbr VARCHAR(15) NULL,

    AddrCarrt CHAR(4) NULL,

    AddrCmraCd CHAR(2) NULL,

    AddrLacsCd CHAR(2) NULL,

    ApnPortionLotInd CHAR(1) NULL,

    ApnResearchInd CHAR(1) NULL,

    AssdDt INT NULL,

    AuctionAddr VARCHAR(60) NULL,

    AuctionAddrZipCd VARCHAR(9) NULL,

    AuctionCityName VARCHAR(40) NULL,

    AuctionCszId VARCHAR(60) NULL,

    AuctionDt INT NULL,

    AuctionStCd CHAR(2) NULL,

    AuctionTime INT NULL,

    AuditorFeeNbr VARCHAR(10) NULL,

    BatchId VARCHAR(12) NULL,

    BathNbrTotalCalc NUMERIC(7, 2) NULL,

    BathFullNbrTotalCalc NUMERIC(7, 2) NULL,

    BathHalfNbrTotalCalc NUMERIC(7, 2) NULL,

    BathQtrNbrTotalCalc NUMERIC(7, 2) NULL,

    Bath3QtrNbrTotalCalc NUMERIC(7, 2) NULL,

    BdrmNbr INT NULL,

    BeneficiaryPurchasedInd CHAR(1) NULL,

    BlkInfo VARCHAR(6) NULL,

    BypassRejectInd CHAR(1) NULL,

    CaseNbr VARCHAR(20) NULL,

    CashDownAmt NUMERIC(13, 2) NULL,

    CbsaCd CHAR(5) NULL,

    CensID VARCHAR(10) NULL,

    GeoMatchCd CHAR(4) NULL,

    CertificateNbr VARCHAR(6) NULL,

    ChronoNbr VARCHAR(14) NULL,

    CltvPct SMALLINT NULL,

    CntyMapNbr_1370 VARCHAR(20) NULL,

    CntyRgnID CHAR(4) NULL,

    CntySectionCd CHAR(4) NULL,

    CurrIEQAmt BIGINT NULL,

    CurrIEQDt INT NULL,

    CurrIEQPct SMALLINT NULL,

    DeedCatTyp VARCHAR(3) NULL,

    DefaultAmt NUMERIC(13, 2) NULL,

    DefaultDt INT NULL,

    DistCd VARCHAR(8) NULL,

    DocFilingDt INT NULL,

    DocTyp VARCHAR(6) NULL,

    DocYyDt SMALLINT NULL,

    DPIDCd CHAR(2) NULL,

    DpvCd CHAR(2) NULL,

    DqAfnkFlg CHAR(3) NULL,

    EditCounter SMALLINT NULL,

    EditorTimestamp DATETIME NULL,

    EditReqdInd CHAR(1) NULL,

    EditTimestamp INT NULL,

    EscrowOrderAbbrev VARCHAR(6) NULL,

    EscrowOrderNbr VARCHAR(15) NULL,

    ExciseTaxNbr BIGINT NULL,

    FinalJudgmntAmt NUMERIC(13, 2) NULL,

    FirstMissedPaymentDt INT NULL,

    GroundRentAmt_1370 INT NULL,

    HomeAffordableInd CHAR(1) NULL,

    AvmValAmt BIGINT NULL,

    InputPclMatchKey VARCHAR(60) NULL,

    InterrelatedInd CHAR(1) NULL,

    IrisFormatCd CHAR(1) NULL,

    IrisSecKey VARCHAR(60) NULL,

    LandCourtDocNum VARCHAR(8) NULL,

    LandDimSqFt NUMERIC(13, 4) NULL,

    LandLotInfo VARCHAR(6) NULL,

    LandUseCtyCd VARCHAR(10) NULL,

    LandUseStCd VARCHAR(10) NULL,

    LastEditDt INT NULL,

    LatDegr NUMERIC(8, 6) NULL,

    LegalMapTypCd CHAR(3) NULL,

    LegalPlusInd CHAR(1) NULL,

    LienAmt NUMERIC(13, 2) NULL,

    LisPendensTyp CHAR(3) NULL,

    LongDegr NUMERIC(9, 6) NULL,

    MapRegNbr VARCHAR(30) NULL,

    MatchCd CHAR(4) NULL,

    MultiPropCnt SMALLINT NULL,

    MultiSplitCd CHAR(1) NULL,

    MuncCd VARCHAR(8) NULL,

    NameCleanUpReqdInd CHAR(1) NULL,

    NominalInd CHAR(1) NULL,

    OltvPct SMALLINT NULL,

    OrigDocDt INT NULL,

    OrigIeqAmt BIGINT NULL,

    OrigIeqDt INT NULL,

    OrigIeqPct SMALLINT NULL,

    OrigMtgLink CHAR(21) NULL,

    OrigRecordingDt INT NULL,

    OtherMatchInfo VARCHAR(60) NULL,

    OtherMatchSeq SMALLINT NULL,

    OwnrOccupInd CHAR(1) NULL,

    OwnrTransferPct SMALLINT NULL,

    PartialInterestTransferInd CHAR(1) NULL,

    PclidIrisFrmtd VARCHAR(60) NULL,

    PclidUnvFrmtd VARCHAR(60) NULL,

    PclMatchCd CHAR(3) NULL,

    PclMatchInd CHAR(1) NULL,

    PhoneConfidCd VARCHAR(10) NULL,

    PlatMapBk VARCHAR(6) NULL,

    PlatMapPg VARCHAR(6) NULL,

    PlatNbr VARCHAR(10) NULL,

    PlatSourceCd CHAR(3) NULL,

    PortionLotInd CHAR(1) NULL,

    PPRInd CHAR(1) NULL,

    PriceCalcInd CHAR(1) NULL,

    PriceSqFtAmt NUMERIC(13, 2) NULL,

    PriceTypCd CHAR(3) NULL,

    PrimaryCatCd CHAR(2) NULL,

    PrkgLotUnitId_1370 VARCHAR(10) NULL,

    PropIndCd CHAR(3) NULL,

    PropStatusCd CHAR(3) NULL,

    PropTypCd CHAR(3) NULL,

    PropUseCd VARCHAR(10) NULL,

    QtrSectionCd CHAR(3) NULL,

    RangeCd CHAR(3) NULL,

    RealEstateOwnedInd CHAR(1) NULL,

    RealEstateOwnedSaleInd CHAR(1) NULL,

    RecordInd CHAR(1) NULL,

    RecordingDt INT NULL,

    RefiEqInd CHAR(1) NULL,

    RehabRiderInd CHAR(1) NULL,

    RejectDt INT NULL,

    RejectInd CHAR(1) NULL,

    ReRecordedDocInd CHAR(1) NULL,

    ResModelInd CHAR(1) NULL,

    RmNbr INT NULL,

    SaleDt INT NULL,

    SalePosition TINYINT NULL,

    SalePriceAmt NUMERIC(13, 2) NULL,

    SaleTypCd CHAR(4) NULL,

    SdsLatDegr NUMERIC(8, 6) NULL,

    SdsLongDegr NUMERIC(9, 6) NULL,

    SectionCd CHAR(3) NULL,

    SelCarryCd CHAR(1) NULL,

    SevaxAdjImpvPrice INT NULL,

    SevaxAdjLandPrice INT NULL,

    SevaxAdjSalePrice INT NULL,

    SevaxContactName VARCHAR(34) NULL,

    SevaxCreationInd CHAR(1) NULL,

    SevaxExciseTaxYy SMALLINT NULL,

    SevaxInsuredAmt INT NULL,

    SevaxNominalDeedCd CHAR(5) NULL,

    SevaxPctImpvVal NUMERIC(5, 2) NULL,

    SevaxPctLandVal NUMERIC(5, 2) NULL,

    SevaxPctNewMoney NUMERIC(5, 2) NULL,

    SevaxPctOfSale NUMERIC(5, 2) NULL,

    SevaxPolicyNbr VARCHAR(9) NULL,

    SevaxWSPolicyInd CHAR(1) NULL,

    ShortSaleInd CHAR(1) NULL,

    SourceInd CHAR(1) NULL,

    StdAddr1 VARCHAR(60) NULL,

    StdHse1Nbr VARCHAR(10) NULL,

    StdHse2Nbr VARCHAR(10) NULL,

    StdSubdCd INT NULL,

    GendSubKey VARCHAR(30) NULL,

    StndAloneMtgInd CHAR(1) NULL,

    SubcondAbstCd VARCHAR(30) NULL,

    SubdPhase CHAR(4) NULL,

    SubdSection CHAR(5) NULL,

    TaxAreaCd VARCHAR(8) NULL,

    TaxStampAmt NUMERIC(13, 2) NULL,

    TaxStampAmtCd CHAR(1) NULL,

    TaxTotalAmt NUMERIC(11, 2) NULL,

    TaxTotalBegYY SMALLINT NULL,

    TaxTotalEndYY SMALLINT NULL,

    TimeshareOrderAbbrev VARCHAR(6) NULL,

    TimeshareOrderNbr VARCHAR(15) NULL,

    Title VARCHAR(20) NULL,

    TitleCompanyCd CHAR(5) NULL,

    TitleCompanyNm VARCHAR(30) NULL,

    TitleOrderAbbrev VARCHAR(6) NULL,

    TitleOrderAccomodationInd CHAR(1) NULL,

    TitleOrderLegacy VARCHAR(15) NULL,

    TitleOrderNbr VARCHAR(15) NULL,

    TownshipCd CHAR(3) NULL,

    TransAssdLandAmt BIGINT NULL,

    TransAssdTotalAmt BIGINT NULL,

    TransTyp CHAR(3) NULL,

    UnitNbr VARCHAR(8) NULL,

    AssdAgrValTotal INT NULL,

    ApprAgrValTotal INT NULL,

    MktAgrValTotal INT NULL,

    BldSfTtlNbr NUMERIC(12, 3) NULL,

    LivingSqFtNbr NUMERIC(12, 3) NULL,

    GrndFlrSqFt NUMERIC(12, 3) NULL,

    GrossSqFtNbr NUMERIC(12, 3) NULL,

    AdjGrosSqFt NUMERIC(12, 3) NULL,

    UnvDocTyp VARCHAR(6) NULL,

    AssdImpValTotal INT NULL,

    ApprImpValTotal INT NULL,

    MktImpvValTotal INT NULL,

    AssdLndValTotal INT NULL,

    ApprLndValTotal INT NULL,

    MktLndValTotal INT NULL,

    UnvSaleTypCd CHAR(4) NULL,

    AssdTtlValAmt BIGINT NULL,

    ApprTtlValAmt BIGINT NULL,

    MktTtlValAmt BIGINT NULL,

    UpdateTimestamp DATETIME NULL,

    VendorID CHAR(1) NULL,

    YyBltActDt SMALLINT NULL,

    YyBltEffDt SMALLINT NULL,

    ZnCd VARCHAR(15) NULL,

    StatisticalExclusion BIT NULL,

    VestingPosition TINYINT NULL,

    StoriesNbr NUMERIC(7, 2) NULL,

    ArmCatCd VARCHAR(1) NULL,

    ArmIntOnlyFlag TINYINT NOT NULL,

    ArmNegAmFlag TINYINT NOT NULL,

    ArmPaymentOptFlag TINYINT NOT NULL,

    VarRiderInd CHAR(2) NULL,

    MtgAmt NUMERIC(13, 2) NULL,

    MtgAssumpAmt BIGINT NULL,

    MtgAssumpAmtInd CHAR(1) NULL,

    MtgBlanketInd CHAR(1) NULL,

    MtgCnstrLoanInd CHAR(1) NULL,

    ORIG_T_MTG_DOC_NBR VARCHAR(12) NULL,

    MtgDocNbr VARCHAR(10) NULL,

    MtgDocTyp VARCHAR(6) NULL,

    MtgDocYyDt SMALLINT NULL,

    MtgDt INT NULL,

    MtgDueDt INT NULL,

    MtgHolderTyp CHAR(1) NULL,

    MtgIntRateCap NUMERIC(6, 4) NULL,

    MtgIntRatePct NUMERIC(6, 4) NULL,

    MtgIntRateTyp CHAR(3) NULL,

    MtgLienPosition SMALLINT NULL,

    MtgLoanTypCd CHAR(5) NULL,

    MtgLookbackDays CHAR(2) NULL,

    MtgMostRecentInd CHAR(1) NULL,

    MtgPaymentChangeDt VARCHAR(8) NULL,

    MtgPrePaymentExpDt VARCHAR(80) NULL,

    MtgPrePaymentInd CHAR(1) NULL,

    MtgPurposeTyp CHAR(1) NULL,

    MtgRecordingBookPage VARCHAR(12) NULL,

    MtgRecordingBook VARCHAR(6) NULL,

    MtgRecordingDt INT NULL,

    MtgRecordingPage VARCHAR(6) NULL,

    MtgStatusInd CHAR(1) NULL,

    MtgStdCompanyCd CHAR(5) NULL,

    MtgSubordTyp CHAR(3) NULL,

    MtgTermAmt INT NULL,

    MtgTermCd CHAR(4) NULL,

    MtgTypInd CHAR(1) NULL,

    MtgUpsellInd CHAR(1) NULL,

    SevaxLoanNumber VARCHAR(20) NULL,

    UnvMtgLoanTypCd CHAR(5) NULL,

    MtgChangeAmt BIGINT NULL,

    MtgConcurrentJrInd CHAR(1) NULL,

    MtgPayoffDt INT NULL,

    MtgPayoffTyp CHAR(1) NULL,

    MtgReleaseDt INT NULL,

    MtgSilent2ndInd CHAR(1) NULL,

    MtgIntRateChangeDt INT NULL,

    MtgIntRateChangeFreq CHAR(1) NULL,

    MtgIntRateChangeIntvl TINYINT NULL,

    MtgIntRateChangePct NUMERIC(6, 4) NULL,

    MtgIntRateChangePctLimit NUMERIC(6, 4) NULL,

    MtgIntRateIndexTyp CHAR(3) NULL,

    MtgIntRatePctMax NUMERIC(6, 4) NULL,

    SecMCConformingLoanInd BIT NOT NULL,

    SecMCConstructionLoanInd BIT NOT NULL,

    SecMCConventionalLoanInd BIT NOT NULL,

    SecMCEquityLoanInd BIT NOT NULL,

    SecMCFhaLoanInd BIT NOT NULL,

    SecMCFixedRateLoanInd BIT NOT NULL,

    SecMCNonConformingLoanInd BIT NOT NULL,

    SecMCOtherSubordLoanInd BIT NOT NULL,

    SecMCPrivatePartyLoanInd BIT NOT NULL,

    SecMCRefinanceLoanInd BIT NOT NULL,

    SecMCSellerCarriedLoanInd BIT NOT NULL,

    SecMCSmallBusAssocLoanInd BIT NOT NULL,

    SecMCVaLoanInd BIT NOT NULL,

    SecMCVariableRateLoanInd BIT NOT NULL,

    TransRowCnt INT NOT NULL,

    BuyAddrAptNbr VARCHAR(6) NULL,

    BuyAddrHse1Nbr VARCHAR(10) NULL,

    BuyAddrHse2Nbr VARCHAR(10) NULL,

    BuyAddrStreetName VARCHAR(30) NULL,

    BuyStdHse1Nbr VARCHAR(10) NULL,

    BuyStdHse2Nbr VARCHAR(10) NULL,

    LndrStdHse1Nbr VARCHAR(10) NULL,

    LndrStdHse2Nbr VARCHAR(10) NULL,

    SelAddrHse1Nbr VARCHAR(10) NULL,

    SelAddrHse2Nbr VARCHAR(10) NULL,

    SelAddrStreetName VARCHAR(30) NULL,

    SelStdHse1Nbr VARCHAR(10) NULL,

    SelStdHse2Nbr VARCHAR(10) NULL,

    BuyIrisName1 VARCHAR(30) NULL,

    BuyIrisName2 VARCHAR(30) NULL,

    BuyAddrCarrt CHAR(4) NULL,

    BuyAddrCityName VARCHAR(40) NULL,

    BuyAddrCmraCd CHAR(2) NULL,

    BuyAddrCntry VARCHAR(30) NULL,

    BuyAddrForeign VARCHAR(60) NULL,

    BuyAddrDirLeftCd CHAR(2) NULL,

    BuyDpvCd CHAR(2) NULL,

    BuyAddrLacsCd CHAR(2) NULL,

    BuyAddrModeCd CHAR(5) NULL,

    BuyAddrOptInd CHAR(1) NULL,

    BuyAddrPfx1Cd CHAR(5) NULL,

    BuyAddrDirRightCd CHAR(2) NULL,

    BuyAddrSfx1Cd VARCHAR(10) NULL,

    BuyAddrSfx2Cd VARCHAR(10) NULL,

    BuyAddrStCd CHAR(2) NULL,

    BuyAddrTypCd CHAR(1) NULL,

    BuyAddrZipCd VARCHAR(9) NULL,

    BuyCensId VARCHAR(10) NULL,

    BuyGeoMatchCd CHAR(4) NULL,

    BuyDPIDCd CHAR(2) NULL,

    BuyLatDegr NUMERIC(8, 6) NULL,

    BuyLongDegr NUMERIC(9, 6) NULL,

    BuyMatchCd CHAR(4) NULL,

    BuyMultiPartyNbr SMALLINT NULL,

    BuyNameChangeInd CHAR(1) NULL,

    BuyNamePrsdInd CHAR(1) NULL,

    BuyPhoneNbr VARCHAR(10) NULL,

    PrepPhoneNbr VARCHAR(10) NULL,

    PrinAddrCityName VARCHAR(40) NULL,

    PrinAddrZipCd VARCHAR(9) NULL,

    PrinAddrStCd CHAR(2) NULL,

    PrinCszId VARCHAR(60) NULL,

    PrinPhoneNbr VARCHAR(10) NULL,

    SelAddrAptNbr VARCHAR(6) NULL,

    SelAddrCarrt CHAR(4) NULL,

    SelAddrCityName VARCHAR(40) NULL,

    SelAddrCntry VARCHAR(30) NULL,

    SelAddrCszId VARCHAR(60) NULL,

    SelAddrDirLeftCd CHAR(2) NULL,

    SelAddrModeCd CHAR(5) NULL,

    SelAddrPfx1Cd CHAR(5) NULL,

    SelAddrDirRightCd CHAR(2) NULL,

    SelAddrSfx1Cd VARCHAR(10) NULL,

    SelAddrSfx2Cd VARCHAR(10) NULL,

    SelAddrStCd CHAR(2) NULL,

    SelAddrZipCd VARCHAR(9) NULL,

    SelCensId VARCHAR(10) NULL,

    SelCentroid CHAR(4) NULL,

    SelDPIDCd CHAR(2) NULL,

    SelLatDegr NUMERIC(8, 6) NULL,

    SelLongDegr NUMERIC(9, 6) NULL,

    SelMatchCd CHAR(4) NULL,

    SelMultiPartyNbr SMALLINT NULL,

    SelNameChangeInd CHAR(1) NULL,

    TrsteePhoneNbr VARCHAR(10) NULL,

    BuyAKANameTypCd VARCHAR(4) NULL,

    BuyAKAName VARCHAR(60) NULL,

    BuyDBAName VARCHAR(60) NULL,

    PrepCdVal VARCHAR(6) NULL,

    PrepFullName VARCHAR(60) NULL,

    PrinFullName VARCHAR(60) NULL,

    SelAKANameTypCd VARCHAR(4) NULL,

    SelAKAName VARCHAR(60) NULL,

    SelDBAName VARCHAR(60) NULL,

    TrusteeFullName VARCHAR(60) NULL,

    SlsDeedCatTyp CHAR(3) NULL,

    PrinFirstName VARCHAR(32) NULL,

    PrinLastName VARCHAR(30) NULL,

    PrinTitle VARCHAR(20) NULL,

    Work1Field VARCHAR(10) NULL,

    Work2Field VARCHAR(10) NULL,

    Work3Field VARCHAR(10) NULL,

    MtgCompanyCd VARCHAR(10) NULL,

    MtgModAmt NUMERIC(13, 2) NULL,

    MtgModDueDt INT NULL,

    MtgModIntRatePct NUMERIC(6, 4) NULL,

    LandUseMajorCd VARCHAR(10) NULL,

    BldgNbr VARCHAR(8) NULL,

    LandDimAcresNbr NUMERIC(13, 4) NULL,

    LegalPclNbr CHAR(5) NULL)

    WITH (DATA_COMPRESSION = ROW) ;

    CREATE UNIQUE CLUSTERED INDEX CNTY_BATCH ON [DiabloStg-Diablo].[DataFolder-Trans_view](CntyCd ASC, BatchDt ASC, BatchSeq ASC, MtgSeq ASC)WITH(DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON) ;

    CREATE UNIQUE NONCLUSTERED INDEX TransId ON [DiabloStg-Diablo].[DataFolder-Trans_view](DataSupplierId ASC, TransId ASC)WITH(DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON) ;

    END ;

    --#endregion

    --#region Trans_view Insert

    INSERT INTO [DiabloStg-Diablo].[DataFolder-Trans_view] WITH (TABLOCK)

    (DataSupplierId,

    CntyCd,

    TransId,

    BatchDt,

    BatchSeq,

    MtgSeq,

    UnvPropEdition,

    UnvPropId,

    MtgChronoNbr,

    DocNbr,

    OrigDocNbr,

    OrigRecordingBook,

    OrigRecordingPage,

    RecordingBook,

    RecordingPage,

    SecDCArmsLengthCashInd,

    SecDCArmsLengthMortgageInd,

    SecDCInterrelatedInd,

    SecDCInvestorPurchaseInd,

    SecDCNewConstructionInd,

    SecDCRealEstateOwnedInd,

    SecDCRealEstateOwnedSaleInd,

    SecDCResaleInd,

    SecDCResModelInd,

    SecDCShortSaleInd,

    UpdateId,

    EditorId,

    AbsenteeIrisCd,

    AcctNbr,

    AddrCarrt,

    AddrCmraCd,

    AddrLacsCd,

    ApnPortionLotInd,

    ApnResearchInd,

    AssdDt,

    AuctionAddr,

    AuctionAddrZipCd,

    AuctionCityName,

    AuctionCszId,

    AuctionDt,

    AuctionStCd,

    AuctionTime,

    AuditorFeeNbr,

    BatchId,

    BathNbrTotalCalc,

    BathFullNbrTotalCalc,

    BathHalfNbrTotalCalc,

    BathQtrNbrTotalCalc,

    Bath3QtrNbrTotalCalc,

    BdrmNbr,

    BeneficiaryPurchasedInd,

    BlkInfo,

    BypassRejectInd,

    CaseNbr,

    CashDownAmt,

    CbsaCd,

    CensID,

    GeoMatchCd,

    CertificateNbr,

    ChronoNbr,

    CltvPct,

    CntyMapNbr_1370,

    CntyRgnID,

    CntySectionCd,

    CurrIEQAmt,

    CurrIEQDt,

    CurrIEQPct,

    DeedCatTyp,

    DefaultAmt,

    DefaultDt,

    DistCd,

    DocFilingDt,

    DocTyp,

    DocYyDt,

    DPIDCd,

    DpvCd,

    DqAfnkFlg,

    EditCounter,

    EditorTimestamp,

    EditReqdInd,

    EditTimestamp,

    EscrowOrderAbbrev,

    EscrowOrderNbr,

    ExciseTaxNbr,

    FinalJudgmntAmt,

    FirstMissedPaymentDt,

    GroundRentAmt_1370,

    HomeAffordableInd,

    AvmValAmt,

    InputPclMatchKey,

    InterrelatedInd,

    IrisFormatCd,

    IrisSecKey,

    LandCourtDocNum,

    LandDimSqFt,

    LandLotInfo,

    LandUseCtyCd,

    LandUseStCd,

    LastEditDt,

    LatDegr,

    LegalMapTypCd,

    LegalPlusInd,

    LienAmt,

    LisPendensTyp,

    LongDegr,

    MapRegNbr,

    MatchCd,

    MultiPropCnt,

    MultiSplitCd,

    MuncCd,

    NameCleanUpReqdInd,

    NominalInd,

    OltvPct,

    OrigDocDt,

    OrigIeqAmt,

    OrigIeqDt,

    OrigIeqPct,

    OrigMtgLink,

    OrigRecordingDt,

    OtherMatchInfo,

    OtherMatchSeq,

    OwnrOccupInd,

    OwnrTransferPct,

    PartialInterestTransferInd,

    PclidIrisFrmtd,

    PclidUnvFrmtd,

    PclMatchCd,

    PclMatchInd,

    PhoneConfidCd,

    PlatMapBk,

    PlatMapPg,

    PlatNbr,

    PlatSourceCd,

    PortionLotInd,

    PPRInd,

    PriceCalcInd,

    PriceSqFtAmt,

    PriceTypCd,

    PrimaryCatCd,

    PrkgLotUnitId_1370,

    PropIndCd,

    PropStatusCd,

    PropTypCd,

    PropUseCd,

    QtrSectionCd,

    RangeCd,

    RealEstateOwnedInd,

    RealEstateOwnedSaleInd,

    RecordInd,

    RecordingDt,

    RefiEqInd,

    RehabRiderInd,

    RejectDt,

    RejectInd,

    ReRecordedDocInd,

    ResModelInd,

    RmNbr,

    SaleDt,

    SalePosition,

    SalePriceAmt,

    SaleTypCd,

    SdsLatDegr,

    SdsLongDegr,

    SectionCd,

    SelCarryCd,

    SevaxAdjImpvPrice,

    SevaxAdjLandPrice,

    SevaxAdjSalePrice,

    SevaxContactName,

    SevaxCreationInd,

    SevaxExciseTaxYy,

    SevaxInsuredAmt,

    SevaxNominalDeedCd,

    SevaxPctImpvVal,

    SevaxPctLandVal,

    SevaxPctNewMoney,

    SevaxPctOfSale,

    SevaxPolicyNbr,

    SevaxWSPolicyInd,

    ShortSaleInd,

    SourceInd,

    StdAddr1,

    StdHse1Nbr,

    StdHse2Nbr,

    StdSubdCd,

    GendSubKey,

    StndAloneMtgInd,

    SubcondAbstCd,

    SubdPhase,

    SubdSection,

    TaxAreaCd,

    TaxStampAmt,

    TaxStampAmtCd,

    TaxTotalAmt,

    TaxTotalBegYY,

    TaxTotalEndYY,

    TimeshareOrderAbbrev,

    TimeshareOrderNbr,

    Title,

    TitleCompanyCd,

    TitleCompanyNm,

    TitleOrderAbbrev,

    TitleOrderAccomodationInd,

    TitleOrderLegacy,

    TitleOrderNbr,

    TownshipCd,

    TransAssdLandAmt,

    TransAssdTotalAmt,

    TransTyp,

    UnitNbr,

    AssdAgrValTotal,

    ApprAgrValTotal,

    MktAgrValTotal,

    BldSfTtlNbr,

    LivingSqFtNbr,

    GrndFlrSqFt,

    GrossSqFtNbr,

    AdjGrosSqFt,

    UnvDocTyp,

    AssdImpValTotal,

    ApprImpValTotal,

    MktImpvValTotal,

    AssdLndValTotal,

    ApprLndValTotal,

    MktLndValTotal,

    UnvSaleTypCd,

    AssdTtlValAmt,

    ApprTtlValAmt,

    MktTtlValAmt,

    UpdateTimestamp,

    VendorID,

    YyBltActDt,

    YyBltEffDt,

    ZnCd,

    StatisticalExclusion,

    VestingPosition,

    StoriesNbr,

    ArmCatCd,

    ArmIntOnlyFlag,

    ArmNegAmFlag,

    ArmPaymentOptFlag,

    VarRiderInd,

    MtgAmt,

    MtgAssumpAmt,

    MtgAssumpAmtInd,

    MtgBlanketInd,

    MtgCnstrLoanInd,

    ORIG_T_MTG_DOC_NBR,

    MtgDocNbr,

    MtgDocTyp,

    MtgDocYyDt,

    MtgDt,

    MtgDueDt,

    MtgHolderTyp,

    MtgIntRateCap,

    MtgIntRatePct,

    MtgIntRateTyp,

    MtgLienPosition,

    MtgLoanTypCd,

    MtgLookbackDays,

    MtgMostRecentInd,

    MtgPaymentChangeDt,

    MtgPrePaymentExpDt,

    MtgPrePaymentInd,

    MtgPurposeTyp,

    MtgRecordingBookPage,

    MtgRecordingBook,

    MtgRecordingDt,

    MtgRecordingPage,

    MtgStatusInd,

    MtgStdCompanyCd,

    MtgSubordTyp,

    MtgTermAmt,

    MtgTermCd,

    MtgTypInd,

    MtgUpsellInd,

    SevaxLoanNumber,

    UnvMtgLoanTypCd,

    MtgChangeAmt,

    MtgConcurrentJrInd,

    MtgPayoffDt,

    MtgPayoffTyp,

    MtgReleaseDt,

    MtgSilent2ndInd,

    MtgIntRateChangeDt,

    MtgIntRateChangeFreq,

    MtgIntRateChangeIntvl,

    MtgIntRateChangePct,

    MtgIntRateChangePctLimit,

    MtgIntRateIndexTyp,

    MtgIntRatePctMax,

    SecMCConformingLoanInd,

    SecMCConstructionLoanInd,

    SecMCConventionalLoanInd,

    SecMCEquityLoanInd,

    SecMCFhaLoanInd,

    SecMCFixedRateLoanInd,

    SecMCNonConformingLoanInd,

    SecMCOtherSubordLoanInd,

    SecMCPrivatePartyLoanInd,

    SecMCRefinanceLoanInd,

    SecMCSellerCarriedLoanInd,

    SecMCSmallBusAssocLoanInd,

    SecMCVaLoanInd,

    SecMCVariableRateLoanInd,

    TransRowCnt,

    BuyAddrAptNbr,

    BuyAddrHse1Nbr,

    BuyAddrHse2Nbr,

    BuyAddrStreetName,

    BuyStdHse1Nbr,

    BuyStdHse2Nbr,

    LndrStdHse1Nbr,

    LndrStdHse2Nbr,

    SelAddrHse1Nbr,

    SelAddrHse2Nbr,

    SelAddrStreetName,

    SelStdHse1Nbr,

    SelStdHse2Nbr,

    BuyIrisName1,

    BuyIrisName2,

    BuyAddrCarrt,

    BuyAddrCityName,

    BuyAddrCmraCd,

    BuyAddrCntry,

    BuyAddrForeign,

    BuyAddrDirLeftCd,

    BuyDpvCd,

    BuyAddrLacsCd,

    BuyAddrModeCd,

    BuyAddrOptInd,

    BuyAddrPfx1Cd,

    BuyAddrDirRightCd,

    BuyAddrSfx1Cd,

    BuyAddrSfx2Cd,

    BuyAddrStCd,

    BuyAddrTypCd,

    BuyAddrZipCd,

    BuyCensId,

    BuyGeoMatchCd,

    BuyDPIDCd,

    BuyLatDegr,

    BuyLongDegr,

    BuyMatchCd,

    BuyMultiPartyNbr,

    BuyNameChangeInd,

    BuyNamePrsdInd,

    BuyPhoneNbr,

    PrepPhoneNbr,

    PrinAddrCityName,

    PrinAddrZipCd,

    PrinAddrStCd,

    PrinCszId,

    PrinPhoneNbr,

    SelAddrAptNbr,

    SelAddrCarrt,

    SelAddrCityName,

    SelAddrCntry,

    SelAddrCszId,

    SelAddrDirLeftCd,

    SelAddrModeCd,

    SelAddrPfx1Cd,

    SelAddrDirRightCd,

    SelAddrSfx1Cd,

    SelAddrSfx2Cd,

    SelAddrStCd,

    SelAddrZipCd,

    SelCensId,

    SelCentroid,

    SelDPIDCd,

    SelLatDegr,

    SelLongDegr,

    SelMatchCd,

    SelMultiPartyNbr,

    SelNameChangeInd,

    TrsteePhoneNbr,

    BuyAKANameTypCd,

    BuyAKAName,

    BuyDBAName,

    PrepCdVal,

    PrepFullName,

    PrinFullName,

    SelAKANameTypCd,

    SelAKAName,

    SelDBAName,

    TrusteeFullName,

    SlsDeedCatTyp,

    PrinFirstName,

    PrinLastName,

    PrinTitle,

    Work1Field,

    Work2Field,

    Work3Field,

    MtgCompanyCd,

    MtgModAmt,

    MtgModDueDt,

    MtgModIntRatePct,

    LandUseMajorCd,

    BldgNbr,

    LandDimAcresNbr,

    LegalPclNbr)

    SELECT tt.DataSupplierId,

    ds.CntyCd,

    tt.TransId,

    tt.BatchDt,

    tt.BatchSeq,

    tt.MtgSeq,

    prop.UnvPropEdition,

    prop.UnvPropId,

    CAST(CASE WHEN mtg.MtgRecordingDt > 0 THEN CASE WHEN lt.T_SLS_RCDED_DT = 0

    OR mtg.MtgRecordingDt >= lt.T_SLS_RCDED_DT THEN CAST(mtg.MtgRecordingDt AS VARCHAR)

    ELSE CAST(lt.T_SLS_RCDED_DT AS VARCHAR)

    END

    ELSE CASE WHEN lt.T_SLS_RCDED_DT > 0 THEN CAST(lt.T_SLS_RCDED_DT AS VARCHAR)

    ELSE CASE WHEN lt.T_SLS_DT > 0 THEN CAST(lt.T_SLS_DT AS VARCHAR)

    ELSE CASE WHEN lt.T_DOC_YY_DT > 0 THEN CAST(lt.T_DOC_YY_DT AS VARCHAR) + '0101'

    ELSE '19000101'

    END

    END

    END

    END + CASE WHEN mtg.MtgRecordingBookPage > '' THEN SUBSTRING(mtg.MtgRecordingBookPage, 4, 3) + SUBSTRING(mtg.MtgRecordingBookPage, 10, 3)

    WHEN mtg.ORIG_T_MTG_DOC_NBR IS NOT NULL THEN SUBSTRING(mtg.ORIG_T_MTG_DOC_NBR, 7, 6)

    ELSE ''

    END AS VARCHAR(16)) AS MtgChronoNbr,

    (SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (T_DOC_NBR)) AS DocNbr,

    (SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (T_ORIG_DOC_NBR)) AS OrigDocNbr,

    SUBSTRING(T_ORIG_BKPG_NBR, 1, 6) AS OrigRecordingBook,

    SUBSTRING(T_ORIG_BKPG_NBR, 7, 6) AS OrigRecordingPage,

    SUBSTRING(T_SLS_REC_BKPG_NBR, 1, 6) AS RecordingBook,

    SUBSTRING(T_SLS_REC_BKPG_NBR, 7, 6) AS RecordingPage,

    ISNULL(deedcat.SecDCArmsLengthCashInd, 0) AS SecDCArmsLengthCashInd,

    ISNULL(deedcat.SecDCArmsLengthMortgageInd, 0) AS SecDCArmsLengthMortgageInd,

    ISNULL(deedcat.SecDCInterrelatedInd, 0) AS SecDCInterrelatedInd,

    ISNULL(deedcat.SecDCInvestorPurchaseInd, 0) AS SecDCInvestorPurchaseInd,

    ISNULL(deedcat.SecDCNewConstructionInd, 0) AS SecDCNewConstructionInd,

    ISNULL(deedcat.SecDCRealEstateOwnedInd, 0) AS SecDCRealEstateOwnedInd,

    ISNULL(deedcat.SecDCRealEstateOwnedSaleInd, 0) AS SecDCRealEstateOwnedSaleInd,

    ISNULL(deedcat.SecDCResaleInd, 0) AS SecDCResaleInd,

    ISNULL(deedcat.SecDCResModelInd, 0) AS SecDCResModelInd,

    ISNULL(deedcat.SecDCShortSaleInd, 0) AS SecDCShortSaleInd,

    u.UserId AS UpdateId,

    ed.UserId AS EditorId,

    lt.T_ABSENTEE_IRIS_CD AS AbsenteeIrisCd,

    lt.T_ACCT_NBR AS AcctNbr,

    lt.T_SIT_CARRT_CD AS AddrCarrt,

    lt.T_SIT_CMRA_CD AS AddrCmraCd,

    lt.T_SIT_LACS_CD AS AddrLacsCd,

    lt.T_APN_PORTION_IND AS ApnPortionLotInd,

    lt.T_APN_RESEARCH_IND AS ApnResearchInd,

    lt.T_ASSD_DT AS AssdDt,

    lt.T_AUCT_ADDR AS AuctionAddr,

    lt.T_AUCT_ADDRZIP_CD AS AuctionAddrZipCd,

    lt.T_AUCT_ADDRCITY_NM AS AuctionCityName,

    lt.T_AUCT_CSZ_ID AS AuctionCszId,

    lt.T_AUCTION_DT AS AuctionDt,

    lt.T_AUCT_ADDR_ST_CD AS AuctionStCd,

    lt.T_AUCTION_TIME AS AuctionTime,

    lt.T_AUDITOR_FEE_NBR AS AuditorFeeNbr,

    lt.T_BATCH_ID AS BatchId,

    lt.T_BATHS_NBR_TTL AS BathNbrTotalCalc,

    lt.T_BATHS_FULL_NBR AS BathFullNbrTotalCalc,

    lt.T_BATHS_HALF_NBR AS BathHalfNbrTotalCalc,

    lt.T_BATHS_1QTR_NBR AS BathQtrNbrTotalCalc,

    lt.T_BATHS_3QTR_NBR AS Bath3QtrNbrTotalCalc,

    lt.T_BDRM_NBR AS BdrmNbr,

    lt.T_FRCL_BUY_BNF_IND AS BeneficiaryPurchasedInd,

    lt.T_BLOCK_INFO AS BlkInfo,

    lt.T_BYP_SLS_REJ_IND AS BypassRejectInd,

    lt.T_CASE_NBR AS CaseNbr,

    lt.T_CASH_DOWN_AMT AS CashDownAmt,

    lt.T_SIT_CBSA_CD AS CbsaCd,

    lt.T_SIT_CENS_ID AS CensID,

    lt.T_SIT_CENTROID_CD AS GeoMatchCd,

    lt.T_CERTIFICATE_NBR AS CertificateNbr,

    lt.T_SLS_CHRONO_NBR AS ChronoNbr,

    lt.T_CLTV_PCT AS CltvPct,

    lt.T_CNTY_MAP_NBR AS CntyMapNbr_1370,

    lt.T_CNTY_RGN_ID AS CntyRgnID,

    lt.T_CNTY_SECTION_CD AS CntySectionCd,

    lt.T_CURR_IEQ_AMT AS CurrIEQAmt,

    lt.T_CURR_IEQ_DT AS CurrIEQDt,

    lt.T_CURR_IEQ_PCT AS CurrIEQPct,

    CASE WHEN lt.T_SLS_DEED_CAT_TYP = 'X'

    THEN CASE WHEN lt.T_SLS_DEED_CD IN ('MG', 'TR', 'EQ', 'AI', 'AP', 'CL', 'DP', 'RV', 'IM', 'SE', 'TL', 'MO') THEN 'T'

    ELSE 'G'

    END

    ELSE lt.T_SLS_DEED_CAT_TYP

    END AS DeedCatTyp,

    lt.T_DEFAULT_AMT AS DefaultAmt,

    lt.T_DEFAULT_DT AS DefaultDt,

    lt.T_DISTRICT_CD AS DistCd,

    lt.T_DOC_FILING_DT AS DocFilingDt,

    lt.T_SLS_DEED_CD AS DocTyp,

    lt.T_DOC_YY_DT AS DocYyDt,

    lt.T_SIT_DPID_CD AS DPIDCd,

    lt.T_SIT_DPV_CD AS DpvCd,

    lt.T_DQ_AFNK_FLG AS DqAfnkFlg,

    lt.T_SLS_EDIT_CTR AS EditCounter,

    lt.EDITOR_TMSTP AS EditorTimestamp,

    lt.T_SLS_EDIT_REQ_IND AS EditReqdInd,

    lt.T_SLS_EDIT_TIME AS EditTimestamp,

    lt.T_ESCROW_ORD_ABBRV AS EscrowOrderAbbrev,

    lt.T_ESCROW_ORDER_NBR AS EscrowOrderNbr,

    lt.T_EXCISE_TAX_NBR AS ExciseTaxNbr,

    lt.T_FNL_JUDGMNT_AMT AS FinalJudgmntAmt,

    lt.T_1ST_MISS_PAY_DT AS FirstMissedPaymentDt,

    lt.T_GRND_RENT_AMT AS GroundRentAmt_1370,

    lt.T_HOME_AFF_IND AS HomeAffordableInd,

    lt.T_HPI_VALUE_AMT AS AvmValAmt,

    lt.T_PCL_MATCH_KEY AS InputPclMatchKey,

    lt.T_INTERFAM_IND AS InterrelatedInd,

    lt.T_BUYR_CD AS IrisFormatCd,

    lt.T_IRIS_SECONDARY_KEY AS IrisSecKey,

    lt.T_LND_COURT_DOCNUM AS LandCourtDocNum,

    lt.T_LND_DIM_SQ_FT AS LandDimSqFt,

    lt.T_LAND_LOT_INFO AS LandLotInfo,

    lt.T_LND_USE_MINOR_CD AS LandUseCtyCd,

    lt.T_LND_USE_ST_CD AS LandUseStCd,

    lt.T_SLS_EDIT_DT AS LastEditDt,

    lt.T_SIT_LAT_DEGR AS LatDegr,

    lt.T_LGL_MAP_TYP_CD AS LegalMapTypCd,

    lt.T_LGL_PLUS_IND AS LegalPlusInd,

    lt.T_LIEN_AMT AS LienAmt,

    lt.T_LIS_PENDENS_TYP AS LisPendensTyp,

    lt.T_SIT_LONG_DEGR AS LongDegr,

    lt.T_MAP_REG_NBR AS MapRegNbr,

    lt.T_SIT_MATCH_CD AS MatchCd,

    lt.T_SLS_MULTI_NBR AS MultiPropCnt,

    lt.T_SLS_MULTI_SPL_CD AS MultiSplitCd,

    lt.T_MUNICIPALITY_CD AS MuncCd,

    lt.T_SLS_NMCLN_REQ_IND AS NameCleanUpReqdInd,

    lt.T_NOMINAL_IND AS NominalInd,

    lt.T_OLTV_PCT AS OltvPct,

    lt.T_ORIG_DOC_DT AS OrigDocDt,

    lt.T_ORIG_IEQ_AMT AS OrigIeqAmt,

    lt.T_ORIG_IEQ_DT AS OrigIeqDt,

    lt.T_ORIG_IEQ_PCT AS OrigIeqPct,

    lt.T_ORIG_MTG_KEY AS OrigMtgLink,

    lt.T_ORIG_RCDED_DT AS OrigRecordingDt,

    lt.T_OTH_MATCH_INFO AS OtherMatchInfo,

    lt.T_OTH_MATCH_SEQ AS OtherMatchSeq,

    lt.T_OWNR_OCCPIED_IND AS OwnrOccupInd,

    lt.T_OWNRSHP_TFR_PCT AS OwnrTransferPct,

    lt.T_PART_INT_IND AS PartialInterestTransferInd,

    lt.T_PCLID_IRIS_FRMTD AS PclidIrisFrmtd,

    lt.T_PCLID_UNV_FRMTD AS PclidUnvFrmtd,

    lt.T_PCL_MATCH_CD AS PclMatchCd,

    lt.T_PCL_MATCH_IND AS PclMatchInd,

    lt.T_BUY_PHN_CONF_CD AS PhoneConfidCd,

    lt.T_REC_PLAT_MAP_BK AS PlatMapBk,

    lt.T_REC_PLAT_MAP_PG AS PlatMapPg,

    lt.T_PLAT_NBR AS PlatNbr,

    lt.T_PLAT_SOURCE_CD AS PlatSourceCd,

    lt.T_PORTION_LOT_IND AS PortionLotInd,

    lt.T_SLS_PPR_IND AS PPRInd,

    lt.T_SLS_PRC_CALC_IND AS PriceCalcInd,

    lt.T_SLS_PRC_SF_AMT AS PriceSqFtAmt,

    lt.T_SLS_PRC_TYP_CD AS PriceTypCd,

    lt.T_PRI_CAT_CD AS PrimaryCatCd,

    lt.T_PRKG_LOT_UNIT_ID AS PrkgLotUnitId_1370,

    lt.T_PROPIND_CD AS PropIndCd,

    lt.T_PROP_STAT_CD AS PropStatusCd,

    lt.T_PROP_TYPE_CD AS PropTypCd,

    lt.T_PROP_USE_CD AS PropUseCd,

    lt.T_QTR_SECTION_CD AS QtrSectionCd,

    lt.T_RANGE_CD AS RangeCd,

    lt.T_REO_IND AS RealEstateOwnedInd,

    lt.T_REO_SALE_IND AS RealEstateOwnedSaleInd,

    lt.T_SLS_RECORD_IND AS RecordInd,

    lt.T_SLS_RCDED_DT AS RecordingDt,

    lt.T_SLS_REFI_EQ_IND AS RefiEqInd,

    lt.T_REHAB_RIDER_IND AS RehabRiderInd,

    lt.T_REJECT_DT AS RejectDt,

    lt.T_SLS_REJECT_IND AS RejectInd,

    lt.T_RE_REC_DOC_IND AS ReRecordedDocInd,

    lt.T_RESID_MODEL_IND AS ResModelInd,

    lt.T_RMS_NBR AS RmNbr,

    lt.T_SLS_DT AS SaleDt,

    lt.T_SALE_POS AS SalePosition,

    lt.T_SLS_PRICE_AMT AS SalePriceAmt,

    lt.T_SLS_TYP_CD AS SaleTypCd,

    lt.T_SIT_LAT_DG_NAD83 AS SdsLatDegr,

    lt.T_SIT_LONG_D_NAD83 AS SdsLongDegr,

    lt.T_SECTION_CD AS SectionCd,

    lt.T_SELLER_CARRY_CD AS SelCarryCd,

    lt.T_ADJ_IMPV_PRICE AS SevaxAdjImpvPrice,

    lt.T_ADJ_LAND_PRICE AS SevaxAdjLandPrice,

    lt.T_ADJ_SALE_PRICE AS SevaxAdjSalePrice,

    lt.T_CONTACT_NAME AS SevaxContactName,

    lt.T_CREATION_IND AS SevaxCreationInd,

    lt.T_EXCISE_TAX_YEAR AS SevaxExciseTaxYy,

    lt.T_INSURED_AMT AS SevaxInsuredAmt,

    lt.T_NON_DOLLAR_CD AS SevaxNominalDeedCd,

    lt.T_PCT_IMPV_VAL AS SevaxPctImpvVal,

    lt.T_PCT_LAND_VAL AS SevaxPctLandVal,

    lt.T_PCT_NEW_MONEY AS SevaxPctNewMoney,

    lt.T_PCT_OF_SALE AS SevaxPctOfSale,

    lt.T_POLICY_NBR AS SevaxPolicyNbr,

    lt.T_W_S_POLICY_IND AS SevaxWSPolicyInd,

    lt.T_SHORT_SLS_IND AS ShortSaleInd,

    lt.T_SLS_SRCE_IND AS SourceInd,

    lt.T_SIT_STD_ADDR AS StdAddr1,

    lt.T_SIT_STD_HSE1NBR AS StdHse1Nbr,

    lt.T_SIT_STD_HSE2NBR AS StdHse2Nbr,

    lt.T_STD_SUBD_CD AS StdSubdCd,

    lt.T_GEND_SUB_KEY AS GendSubKey,

    lt.T_STND_ALN_MTG_IND AS StndAloneMtgInd,

    lt.T_SUBCOND_ABST_CD AS SubcondAbstCd,

    lt.T_SUBD_PHASE AS SubdPhase,

    lt.T_SUBD_SECTION AS SubdSection,

    lt.T_TAX_AREA_CD AS TaxAreaCd,

    lt.T_SLS_STAMP_AMT AS TaxStampAmt,

    lt.T_STAMP_AMT_CD AS TaxStampAmtCd,

    lt.T_TAX_TTL_AMT AS TaxTotalAmt,

    lt.T_TAX_TTL_BEG_YY AS TaxTotalBegYY,

    lt.T_TAX_TTL_END_YY AS TaxTotalEndYY,

    lt.T_TMSHR_ORD_ABBRV AS TimeshareOrderAbbrev,

    lt.T_TMSHR_ORDER_NBR AS TimeshareOrderNbr,

    lt.T_PRIN_TITLE AS Title,

    lt.T_SLS_TITLE_CO_CD AS TitleCompanyCd,

    lt.T_SLS_TITLE_CO_NM AS TitleCompanyNm,

    lt.T_REM_TTLORD_ABBRV AS TitleOrderAbbrev,

    lt.T_ACCOMMODATN_IND AS TitleOrderAccomodationInd,

    lt.T_SLS_TITLE_ORDER AS TitleOrderLegacy,

    lt.T_REM_TITLE_ORDER AS TitleOrderNbr,

    lt.T_TOWNSHIP_CD AS TownshipCd,

    lt.T_TRN_ASSD_LND_AMT AS TransAssdLandAmt,

    lt.T_TRN_ASSD_TTL_AMT AS TransAssdTotalAmt,

    lt.T_SLS_TRANS_CD AS TransTyp,

    lt.T_UNIT_NBR AS UnitNbr,

    lt.T_ASSD_AGR_VAL_TTL AS AssdAgrValTotal,

    lt.T_APPR_AGR_VAL_TTL AS ApprAgrValTotal,

    lt.T_MKT_AGR_VAL_TTL AS MktAgrValTotal,

    lt.T_BLD_SF_TTL_NBR AS BldSfTtlNbr,

    lt.T_LIVING_SQ_FT_NBR AS LivingSqFtNbr,

    lt.T_GRND_FLR_SQ_FT AS GrndFlrSqFt,

    lt.T_GROSS_SQ_FT_NBR AS GrossSqFtNbr,

    lt.T_ADJ_GROS_SQ_FT AS AdjGrosSqFt,

    lt.T_UNV_SLS_DEED_CD AS UnvDocTyp,

    lt.T_ASSD_IMP_VAL_TTL AS AssdImpValTotal,

    lt.T_APPR_IMP_VAL_TTL AS ApprImpValTotal,

    lt.T_MKT_IMPV_VAL_TTL AS MktImpvValTotal,

    lt.T_ASSD_LND_VAL_TTL AS AssdLndValTotal,

    lt.T_APPR_LND_VAL_TTL AS ApprLndValTotal,

    lt.T_MKT_LND_VAL_TTL AS MktLndValTotal,

    lt.T_UNV_SLS_TYP_CD AS UnvSaleTypCd,

    lt.T_ASSD_TTL_VAL_AMT AS AssdTtlValAmt,

    lt.T_APPR_TTL_VAL_AMT AS ApprTtlValAmt,

    lt.T_MKT_TTL_VAL_AMT AS MktTtlValAmt,

    lt.UPDT_TMSTP AS UpdateTimestamp,

    lt.T_VENDOR_IND AS VendorID,

    lt.T_YY_BLT_ACT_DT AS YyBltActDt,

    lt.T_YY_BLT_EFF_DT AS YyBltEffDt,

    lt.T_ZONING_CD AS ZnCd,

    CAST(CASE WHEN lt.T_SLS_DEED_CAT_TYP = 'X' THEN 1

    ELSE 0

    END AS BIT) AS StatisticalExclusion,

    lt.T_VEST_POS AS VestingPosition,

    lt.T_STORIES_NBR AS StoriesNbr,

    mtg.ArmCatCd,

    ISNULL(mtg.ArmIntOnlyFlag, 0) AS ArmIntOnlyFlag,

    ISNULL(mtg.ArmNegAmFlag, 0) AS ArmNegAmFlag,

    ISNULL(mtg.ArmPaymentOptFlag, 0) AS ArmPaymentOptFlag,

    mtg.VarRiderInd,

    mtg.MtgAmt,

    mtg.MtgAssumpAmt,

    mtg.MtgAssumpAmtInd,

    mtg.MtgBlanketInd,

    mtg.MtgCnstrLoanInd,

    mtg.ORIG_T_MTG_DOC_NBR,

    mtg.MtgDocNbr,

    mtg.MtgDocTyp,

    mtg.MtgDocYyDt,

    mtg.MtgDt,

    mtg.MtgDueDt,

    mtg.MtgHolderTyp,

    mtg.MtgIntRateCap,

    mtg.MtgIntRatePct,

    mtg.MtgIntRateTyp,

    mtg.MtgLienPosition,

    mtg.MtgLoanTypCd,

    mtg.MtgLookbackDays,

    mtg.MtgMostRecentInd,

    mtg.MtgPaymentChangeDt,

    mtg.MtgPrePaymentExpDt,

    mtg.MtgPrePaymentInd,

    mtg.MtgPurposeTyp,

    mtg.MtgRecordingBookPage,

    mtg.MtgRecordingBook,

    mtg.MtgRecordingDt,

    mtg.MtgRecordingPage,

    mtg.MtgStatusInd,

    mtg.MtgStdCompanyCd,

    mtg.MtgSubordTyp,

    mtg.MtgTermAmt,

    mtg.MtgTermCd,

    mtg.MtgTypInd,

    mtg.MtgUpsellInd,

    mtg.SevaxLoanNumber,

    mtg.UnvMtgLoanTypCd,

    mtg.MtgChangeAmt,

    mtg.MtgConcurrentJrInd,

    mtg.MtgPayoffDt,

    mtg.MtgPayoffTyp,

    mtg.MtgReleaseDt,

    mtg.MtgSilent2ndInd,

    mtg.MtgIntRateChangeDt,

    mtg.MtgIntRateChangeFreq,

    mtg.MtgIntRateChangeIntvl,

    mtg.MtgIntRateChangePct,

    mtg.MtgIntRateChangePctLimit,

    mtg.MtgIntRateIndexTyp,

    mtg.MtgIntRatePctMax,

    ISNULL(mtgCat.SecMCConformingLoanInd, 0) AS SecMCConformingLoanInd,

    ISNULL(mtgCat.SecMCConstructionLoanInd, 0) AS SecMCConstructionLoanInd,

    ISNULL(mtgCat.SecMCConventionalLoanInd, 0) AS SecMCConventionalLoanInd,

    ISNULL(mtgCat.SecMCEquityLoanInd, 0) AS SecMCEquityLoanInd,

    ISNULL(mtgCat.SecMCFhaLoanInd, 0) AS SecMCFhaLoanInd,

    ISNULL(mtgCat.SecMCFixedRateLoanInd, 0) AS SecMCFixedRateLoanInd,

    ISNULL(mtgCat.SecMCNonConformingLoanInd, 0) AS SecMCNonConformingLoanInd,

    ISNULL(mtgCat.SecMCOtherSubordLoanInd, 0) AS SecMCOtherSubordLoanInd,

    ISNULL(mtgCat.SecMCPrivatePartyLoanInd, 0) AS SecMCPrivatePartyLoanInd,

    ISNULL(mtgCat.SecMCRefinanceLoanInd, 0) AS SecMCRefinanceLoanInd,

    ISNULL(mtgCat.SecMCSellerCarriedLoanInd, 0) AS SecMCSellerCarriedLoanInd,

    ISNULL(mtgCat.SecMCSmallBusAssocLoanInd, 0) AS SecMCSmallBusAssocLoanInd,

    ISNULL(mtgCat.SecMCVaLoanInd, 0) AS SecMCVaLoanInd,

    ISNULL(mtgCat.SecMCVariableRateLoanInd, 0) AS SecMCVariableRateLoanInd,

    ISNULL(mtgcnt.Counter, 0) AS TransRowCnt,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_ADDR_APT_NBR)) AS VARCHAR(6)) AS BuyAddrAptNbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_ADDR_HSE1NBR)) AS VARCHAR(10)) AS BuyAddrHse1Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_ADDR_HSE2NBR)) AS VARCHAR(10)) AS BuyAddrHse2Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_ADDR_STRT_NM)) AS VARCHAR(30)) AS BuyAddrStreetName,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_STD_HSE1NBR)) AS VARCHAR(10)) AS BuyStdHse1Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_BUY_STD_HSE2NBR)) AS VARCHAR(10)) AS BuyStdHse2Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_LNDR_STD_HSE1NBR)) AS VARCHAR(10)) AS LndrStdHse1Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_LNDR_STD_HSE2NBR)) AS VARCHAR(10)) AS LndrStdHse2Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_SEL_ADDR_HSE1NBR)) AS VARCHAR(10)) AS SelAddrHse1Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_SEL_ADDR_HSE2NBR)) AS VARCHAR(10)) AS SelAddrHse2Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_SEL_ADDR_STRT_NM)) AS VARCHAR(30)) AS SelAddrStreetName,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_SEL_STD_HSE1NBR)) AS VARCHAR(10)) AS SelStdHse1Nbr,

    CAST((SELECT OutVal FROM Util.dbo.RemoveLeadingZerosInline (lt.T_SEL_STD_HSE2NBR)) AS VARCHAR(10)) AS SelStdHse2Nbr,

    lt.T_BUYR_NM1 AS BuyIrisName1,

    lt.T_BUYR_NM2 AS BuyIrisName2,

    lt.T_BUY_ADDR_CARRT AS BuyAddrCarrt,

    lt.T_BUY_ADDR_CITY_NM AS BuyAddrCityName,

    lt.T_BUY_ADDR_CMRA_CD AS BuyAddrCmraCd,

    lt.T_BUY_ADDR_CNTRY AS BuyAddrCntry,

    lt.T_BUY_ADDR_CSZ_ID AS BuyAddrForeign,

    lt.T_BUY_ADDR_DIR_CD AS BuyAddrDirLeftCd,

    lt.T_BUY_ADDR_DPV_CD AS BuyDpvCd,

    lt.T_BUY_ADDR_LACS_CD AS BuyAddrLacsCd,

    lt.T_BUY_ADDR_MODE_CD AS BuyAddrModeCd,

    lt.T_BUY_ADDR_OPT_IND AS BuyAddrOptInd,

    lt.T_BUY_ADDR_PFX1_CD AS BuyAddrPfx1Cd,

    lt.T_BUY_ADDR_QDRNT AS BuyAddrDirRightCd,

    lt.T_BUY_ADDR_SFX1_CD AS BuyAddrSfx1Cd,

    lt.T_BUY_ADDR_SFX2_CD AS BuyAddrSfx2Cd,

    lt.T_BUY_ADDR_ST_CD AS BuyAddrStCd,

    lt.T_BUY_ADDR_TYP_CD AS BuyAddrTypCd,

    lt.T_BUY_ADDR_ZIP_CD AS BuyAddrZipCd,

    lt.T_BUY_CENS_ID AS BuyCensId,

    lt.T_BUY_CENTROID AS BuyGeoMatchCd,

    lt.T_BUY_DPID_CD AS BuyDPIDCd,

    lt.T_BUY_LAT_DEGR AS BuyLatDegr,

    lt.T_BUY_LONG_DEGR AS BuyLongDegr,

    lt.T_BUY_MATCH_CD AS BuyMatchCd,

    lt.T_BUY_MULTI_NBR AS BuyMultiPartyNbr,

    lt.T_BUY_NM_CHNG_IND AS BuyNameChangeInd,

    lt.T_BUY_NM_PRSD_IND AS BuyNamePrsdInd,

    lt.T_BUY_PHONE_NBR AS BuyPhoneNbr,

    lt.T_PREP_PHONE_NBR AS PrepPhoneNbr,

    lt.T_PRIN_ADDRCITY_NM AS PrinAddrCityName,

    lt.T_PRIN_ADDRZIP_CD AS PrinAddrZipCd,

    lt.T_PRIN_ADDR_ST_CD AS PrinAddrStCd,

    lt.T_PRIN_CSZ_ID AS PrinCszId,

    lt.T_PRIN_PHONE_NBR AS PrinPhoneNbr,

    lt.T_SEL_ADDR_APT_NBR AS SelAddrAptNbr,

    lt.T_SEL_ADDR_CARRT AS SelAddrCarrt,

    lt.T_SEL_ADDR_CITY_NM AS SelAddrCityName,

    lt.T_SEL_ADDR_CNTRY AS SelAddrCntry,

    lt.T_SEL_ADDR_CSZ_ID AS SelAddrCszId,

    lt.T_SEL_ADDR_DIR_CD AS SelAddrDirLeftCd,

    lt.T_SEL_ADDR_MODE_CD AS SelAddrModeCd,

    lt.T_SEL_ADDR_PFX1_CD AS SelAddrPfx1Cd,

    lt.T_SEL_ADDR_QDRNT AS SelAddrDirRightCd,

    lt.T_SEL_ADDR_SFX1_CD AS SelAddrSfx1Cd,

    lt.T_SEL_ADDR_SFX2_CD AS SelAddrSfx2Cd,

    lt.T_SEL_ADDR_ST_CD AS SelAddrStCd,

    lt.T_SEL_ADDR_ZIP_CD AS SelAddrZipCd,

    lt.T_SEL_CENS_ID AS SelCensId,

    lt.T_SEL_CENTROID AS SelCentroid,

    lt.T_SEL_DPID_CD AS SelDPIDCd,

    lt.T_SEL_LAT_DEGR AS SelLatDegr,

    lt.T_SEL_LONG_DEGR AS SelLongDegr,

    lt.T_SEL_MATCH_CD AS SelMatchCd,

    lt.T_SEL_MULTI_NBR AS SelMultiPartyNbr,

    lt.T_SEL_NM_CHNG_IND AS SelNameChangeInd,

    lt.T_TRSTEE_PHONE_NBR AS TrsteePhoneNbr,

    LTRIM(lt.T_BUY_AKANM_TYP_CD) AS BuyAKANameTypCd,

    LTRIM(lt.T_BUY_AKA_NM) AS BuyAKAName,

    LTRIM(lt.T_BUY_DBA_NM) AS BuyDBAName,

    LTRIM(lt.T_PREP_CD) AS PrepCdVal,

    LTRIM(lt.T_PREP_NM) AS PrepFullName,

    LTRIM(lt.T_PRIN_NM) AS PrinFullName,

    LTRIM(lt.T_SEL_AKANM_TYP_CD) AS SelAKANameTypCd,

    LTRIM(lt.T_SEL_AKA_NM) AS SelAKAName,

    LTRIM(lt.T_SEL_DBA_NM) AS SelDBAName,

    LTRIM(lt.T_TRUSTEE_NM) AS TrusteeFullName,

    lt.T_SLS_DEED_CAT_TYP AS SlsDeedCatTyp,

    LTRIM(lt.T_PRIN_FRST_NM) AS PrinFirstName,

    LTRIM(lt.T_PRIN_LST_NM) AS PrinLastName,

    LTRIM(lt.T_PRIN_TITLE) AS PrinTitle,

    lt.T_WORK1_FIELD AS Work1Field,

    lt.T_WORK2_FIELD AS Work2Field,

    lt.T_WORK3_FIELD AS Work3Field,

    mtg.MtgCompanyCd,

    mtg.MtgModAmt,

    mtg.MtgModDueDt,

    mtg.MtgModIntRatePct,

    lt.T_LND_USE_MAJOR_CD AS LandUseMajorCd,

    lt.T_BLD_NBR AS BldgNbr,

    lt.T_LND_DIM_ACRES AS LandDimAcresNbr,

    lt.T_CNTY_PCL_NBR AS LegalPclNbr

    FROM [DiabloStg-DiabloLoad].[DataFolder-trans] AS lt (NOLOCK)

    INNER JOIN #CNTYCD AS l ON l.CNTY_CD = lt.CNTY_CD

    INNER JOIN Common.vDataSupplier AS ds (NOLOCK) ON ds.CntyCd = lt.Cnty_Cd

    INNER JOIN [Diablo-tTrans].[DataFolder-Trans] AS tt (NOLOCK) ON ds.DataSupplierId = tt.DataSupplierID

    AND lt.t_batch_dt = tt.BatchDt

    AND lt.t_batch_seq = tt.BatchSeq

    LEFT OUTER JOIN tCommon.PropertyParcelId AS pp (NOLOCK) ON lt.t_pcl_id IS NOT NULL

    AND lt.t_pcl_id = pp.PclId

    AND ISNULL(lt.t_pcl_seq_nbr, 1) = pp.PclSeqNbr

    LEFT OUTER JOIN tTax.Property AS prop (NOLOCK) ON ds.NaturalDataSupplierId = prop.DataSupplierId

    AND lt.t_pcl_id IS NOT NULL

    AND pp.PropId IS NOT NULL

    AND pp.PropId = prop.PropId

    LEFT OUTER JOIN [DiabloStg-Diablo].[DataFolder-Trans_DeedCat] AS deedcat (NOLOCK) ON lt.cnty_cd = deedcat.cnty_cd

    AND lt.T_batch_dt = deedcat.T_batch_dt

    AND lt.t_batch_Seq = deedcat.t_batch_Seq

    LEFT OUTER JOIN tCommon.Users AS u (NOLOCK) ON lt.UPDT_ID = u.UserName

    LEFT OUTER JOIN tCommon.Users AS ed (NOLOCK) ON lt.EDITOR_ID = ed.UserName

    LEFT OUTER JOIN [DiabloStg-Diablo].[DataFolder-Trans_MtgInfo] AS mtg (NOLOCK) ON mtg.cnty_cd = lt.Cnty_Cd

    AND mtg.t_batch_dt = lt.t_batch_dt

    AND mtg.t_batch_seq = lt.t_batch_seq

    AND mtg.Count1 = tt.MtgSeq

    LEFT OUTER JOIN [DiabloStg-Diablo].[DataFolder-Trans_MtgCat] AS mtgcat (NOLOCK) ON mtgCat.cnty_cd = lt.Cnty_Cd

    AND mtgCat.t_batch_dt = lt.t_batch_dt

    AND mtgCat.t_batch_seq = lt.t_batch_seq

    AND mtgCat.count1 = tt.MtgSeq

    OUTER APPLY (SELECT COUNT(Count1) AS Counter

    FROM [DiabloStg-Diablo].[DataFolder-Trans_MtgInfo] AS mtg (NOLOCK)

    WHERE mtg.cnty_cd = lt.Cnty_Cd

    AND mtg.t_batch_dt = lt.t_batch_dt

    AND mtg.t_batch_seq = lt.t_batch_seq) AS mtgcnt

    OPTION (RECOMPILE) ;

    --#endregion

    GO

  • thats grt, i will check it once for sure, as i use redgate sql prompt i found it very net and clean product for sql formatter, but the only pain is its a paid porduct.:crying:

  • I use redgate sql prompt too. I also check every version of apexsql refactor (free), they have many options but I still cannot get it format the way I want it, but they have gotten better and better at it. None of the products format in a batch process too, that's why I am really interested in the formatter here.

  • Quick update on this thread, the latest version of this formatter was published today, including Notepad++ plugin, SSMS 2012/Demali support, a couple of translations (French/Spanish), pipeline support on the command-line formatter, a basic minifier, lots of bugfixes, and more bits & bobs:

    http://www.architectshack.com/PoorMansTSqlFormatter.ashx

    As usual, poorsql.com has been upgraded to the latest version: http://poorsql.com/

    gvarol (9/20/2011)


    I prefer the below code not to change when I format

    Unfortunately the actual format of the code in your post was lost (and I forgot to respond here, sorry), but there's something in the latest version that may help you out: You can now specify to the formatter that there are regions of your code that shouldn't be formatted. Just put "[noformat]" in a comment before the block to be avoided, and "[/noformat]" afterwards (looks a bit like the IFCode used in the forums here actually:) ). Sample here: http://www.architectshack.com/PoorMansTSqlFormatter.ashx#Tips_amp_Tricks_8

    I hope this helps!

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply