I have version 6.0.0.270 and below script fails to format. P

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

I have version 6.0.0.270 and below script fails to format. P

Postby gvarol@corelogic.com » Fri Aug 09, 2013 2:58 pm

I have version 6.0.0.270 and below script fails to format. Please advise



CREATE FUNCTION aExtract.DiabloDWInlineInner
(
@CntyCd char (5)
, @PclHeader bigint
, @Edition tinyint
)
RETURNS TABLE
AS
RETURN
SELECT
pp.UnvPclId
, pp.ForeClosureDate
, pp.ForeclosureStatus
, pp.MetropolitanStatisticalArea
, pp.OwnerOccupiedInd
, pp.PropertyZipId
, pp.PropertyType
, pp.SitusCbsaCd
, pp.FrclChronoDate
, pp.UnvHomeSteadExemptCd
, pp.UnvImpvValAmount
, pp.UnvImpvValSourceInd
, pp.UnvLandValAmount
, pp.UnvLandValSourceInd
, pp.UnvLocationInfoCd
, pp.UnvOccupTypCd
, pp.UnvSeniorExemptCd
, pp.UnvTotalValAmount
, pp.UnvTotalValSourceInd
, pp.UnvVeteranExemptCd
, pp.UnvWidowExemptCd
, pp.AbsenteeOwnrIrisCd
, pp.AssdDate
, pp.BldgCntTotal
, pp.CntySubdCd
, pp.LandUseId
, pp.LandDimSqFtTotal
, pp.LotUnitsTotal
, pp.MobileHomeInd
, pp.NeighborhoodCd
, pp.NetTaxBegDate
, pp.NetTaxEndDate
, pp.OccupTypCd
, pp.OwnrOccupInd
, pp.PropUseCd
, pp.SitusCSZTypInd
, pp.SitusDpvCd
, pp.SitusGeoMatchCd
, pp.SitusMatchCd
, pp.SitusMsa
, pp.StdSubdCd
, pp.SumAreaAboveGround
, pp.SumAreaAdj
, pp.SumAreaBldg
, pp.SumAreaBsmt
, pp.SumAreaCanopy
, pp.SumAreaLiv
, pp.SumAreaOffice
, pp.SumAreaRental
, pp.SumNbrBath
, pp.SumNbrBathHalf
, pp.SumNbrBdrm
, pp.SumNbrBldg
, pp.SumNbrCondo
, pp.SumNbrElevator
, pp.SumNbrFirepl
, pp.SumNbrRm
, pp.SumNbrUnits
, pp.SumNbrUnits1Bdrm
, pp.SumNbrUnits2Bdrm
, pp.SumNbrUnits3Bdrm
, pp.SumNbrUnitsEff
, pp.TaxCalcAmount
, pp.TaxDelinqAmount
, pp.TaxDelinqDate
, pp.TaxDelinqInd
, pp.TaxRollDate
, pp.TotalTaxAmount
, pp.TotalTaxBegDate
, pp.TotalTaxEndDate
, pp.UnvClassCd
, pp.UnvElectCd
, pp.UnvFuelCd
, pp.UnvGovtExemptCd
, pp.ZnCd
, pp.OwnerAddrOptInd
, pp.OwnerPhoneOptInd
, pp.IrisCorpInd
, pp.OwnerRelationshipTypCd1
, pp.OwnerRelationshipTypCd2
, pp.OwnerShipRightsCd1
, pp.OwnerShipRightsCd2
, pp.CorpInd1
, pp.CorpInd2
, pp.LandDimAcresNbr
, pp.LandDimDepthNbr
, pp.LandDimFrontNbr
, pp.LandDimSqFtNbr
, pp.LotUnitsNbr
, pp.GovtExmpt
, pp.HmeStdExmpt
, pp.MiscExmpt
, pp.ReligExmpt
, pp.SenrExmpt
, pp.UtilExmpt
, pp.VeteranExmpt
, pp.WidowExmpt
, pp.AmericanExpressPremium
, pp.AmericanExpressRegular
, pp.AnimalWelfareContributions
, pp.BooksDMMercByrCat
, pp.CatEnthusiasts
, pp.CDsMoneyMarketCurrently
, pp.ChildrensWelfare
, pp.ColSpcFoodDMMercByrCat
, pp.ComputersPeripherals
, pp.ConfirmedPOCModel
, pp.CraftsHobbieDMMercByrCat
, pp.CulinaryInterestMagByrCat
, pp.CulturalActivities
, pp.DiscoverRegular
, pp.DogEnthusiasts
, pp.DoItYourselfers
, pp.DwellingTyp
, pp.DwellingUnitSizeCd
, pp.EnhancedEstimatedHHIncome
, pp.Environmental
, pp.EstimatedHHIncome
, pp.EthnicInsightMatchFlg
, pp.FamilyGeneralMagByrCat
, pp.FemaleOrientDMMercByrCat
, pp.FemaleOrientMagByrCat
, pp.GardenFarmDMMercByrCat
, pp.GardenFarmingMagByrCat
, pp.GasCard
, pp.GeneralContributorCat
, pp.GeneralDMMercByrCat
, pp.GiftGadgetDMMercByrCat
, pp.HealthAndFitnessMagByrCat
, pp.HealthAndInstContributorCat
, pp.HealthRelated
, pp.HHCompositionCd
, pp.HHStatusCd
, pp.HomeDecoratingFurnishing
, pp.InterestInAutomotive
, pp.InterestInFitness
, pp.InterestInGourmetCooking
, pp.InterestInMoneyAndInvesting
, pp.InterestInSports
, pp.InterestInTheOutdoors
, pp.Investors
, pp.InvestsMutualFundsAnnuities
, pp.IRAsCurrently
, pp.IRAsFutureInterest
, pp.LifeInsuranceCurrently
, pp.MaleAndSportOrienMagByrCat
, pp.MaleOrientDMMercByrCat
, pp.MastercardPremium
, pp.MastercardRegular
, pp.MedEducationYearsAttained
, pp.MiscMailResponder
, pp.LifeStyle
, pp.MutualFundsCurrently
, pp.MutualFundsFutureInterest
, pp.NbrOfChildren18OrLess
, pp.NbrOfPersonsOnRecord
, pp.NewsAndFinancial
, pp.OddsAndEndsMailResponder
, pp.OtherCardRegular
, pp.OtherInvestmentsCurrently
, pp.OtherInvestmentsFutureInterest
, pp.PetEnthusiast
, pp.Photography
, pp.POC00To03CombTypGender
, pp.POC10To12CombTypGender
, pp.POC13To18CombTypGender
, pp.POC04To06CombTypGender
, pp.POC07To09CombTypGender
, pp.Political
, pp.PoliticalContributorCat
, pp.ProbableHomeOwnr
, pp.RealEstateCurrently
, pp.RealEstateFutureInterest
, pp.RecipientReliabilityCd
, pp.ReligiousContributions
, pp.ReligiousContributorCat
, pp.ReligiousMagByrCat
, pp.RuralUrbanCntySizeCd
, pp.SocialServices
, pp.StocksBondsCurrently
, pp.StocksBondsFutureInterest
, pp.StoreOrRetailRegular
, pp.SurveyNameOutputRABucket
, pp.Sweepstakes
, pp.SweepstakesGambling
, pp.TimeZone
, pp.TotalEnhancementMatchTyp
, pp.UnitsInStructureMobileHome
, pp.UpscaleDMMercByrCat
, pp.VisaPremium
, pp.VisaRegular
, pp.YOwnsHome
, pp.YProbableRenter
, pp.YRenter
, pp.NbrOfAdultsInHH
, pp.I1_BusnsOwnrFlg
, pp.I1_CntryOfOrigin
, pp.I1_Age
, pp.I1_Education
, pp.I1_EthnicExpGrpCd
, pp.I1_EthnicGrpCd
, pp.I1_EthnicCd
, pp.I1_GenderCd
, pp.I1_Language
, pp.I1_MaritalStatus
, pp.I1_Occupation
, pp.I1_OccupationGrp
, pp.I1_PersonTyp
, pp.I1_Religion
, pp.I2_BusnsOwnrFlg
, pp.I2_CntryOfOrigin
, pp.I2_Education
, pp.I2_Age
, pp.I2_EthnicExpGrpCd
, pp.I2_EthnicGrpCd
, pp.I2_EthnicCd
, pp.I2_GenderCd
, pp.I2_Language
, pp.I2_MaritalStatus
, pp.I2_Occupation
, pp.I2_OccupationGrp
, pp.I2_PersonTyp
, pp.I2_Religion
, pp.I2_TitleOfRespect
, pp.ConventionalRefi
, pp.HomeEquity
, pp.CashOutRefi
, pp.FHARefi
, pp.IncomeEstimateInDollars
, pp.TotalLiquidAssets
, pp.LifeInsurance
, pp.CharitableDonor
, pp.SubprimeBorrowerFlag
, pp.AvmAmount
, pp.GeoCoreDate
, pp.GeoCoreAmount
, pp.GeoCoreScore
, pp.GeoCoreFSDScore
, pp.GeoCoreSource
, pp.HarpEligibility
, tmt.BeneficiaryPurchasedInd
, tmt.SaleArmsLengthCashInd
, tmt.SaleArmsLengthMortgageInd
, tmt.SaleInterrelatedInd
, tmt.SaleInvestorPurchaseInd
, tmt.SaleNewConstructionInd
, tmt.SaleRealEstateOwnedInd
, tmt.SaleRealEstateOwnedSaleInd
, tmt.SaleResaleInd
, tmt.SaleResModelInd
, tmt.SaleShortSaleInd
, tmt.MultipleSplitCode
, tmt.NominalIndicator
, tmt.SaleDate
, tmt.SalesPriceAmount
, tmt.SaleRecordedDate
, tmt.TransactionType
, tmt.MortgageAmount1
, tmt.MortgageAmount2
, tmt.MortgageAmount3
, tmt.MortgageAmount4
, tmt.MortgageCompanyCd1
, tmt.MortgageCompanyCd2
, tmt.MortgageCompanyCd3
, tmt.MortgageCompanyCd4
, tmt.MortgageDocumentDate1
, tmt.MortgageDocumentDate2
, tmt.MortgageDocumentDate3
, tmt.MortgageDocumentDate4
, tmt.MortgageDate1
, tmt.MortgageDate2
, tmt.MortgageDate3
, tmt.MortgageDate4
, tmt.MortgageDueDate1
, tmt.MortgageDueDate2
, tmt.MortgageDueDate3
, tmt.MortgageDueDate4
, tmt.MortgageInterestRateCap1
, tmt.MortgageInterestRateCap2
, tmt.MortgageInterestRateCap3
, tmt.MortgageInterestRateCap4
, tmt.MortgageInterestRateChangeDate1
, tmt.MortgageInterestRateChangeDate2
, tmt.MortgageInterestRateChangeDate3
, tmt.MortgageInterestRateChangeDate4
, tmt.MortgageInterestRateChangeIntervalMonths1
, tmt.MortgageInterestRateChangeIntervalMonths2
, tmt.MortgageInterestRateChangeIntervalMonths3
, tmt.MortgageInterestRateChangeIntervalMonths4
, tmt.MortgageInterestRateChangePercentage1
, tmt.MortgageInterestRateChangePercentage2
, tmt.MortgageInterestRateChangePercentage3
, tmt.MortgageInterestRateChangePercentage4
, tmt.MortgageInterestRateChangePercentageLimit1
, tmt.MortgageInterestRateChangePercentageLimit2
, tmt.MortgageInterestRateChangePercentageLimit3
, tmt.MortgageInterestRateChangePercentageLimit4
, tmt.MortgageInterestRateIndexType1
, tmt.MortgageInterestRateIndexType2
, tmt.MortgageInterestRateIndexType3
, tmt.MortgageInterestRateIndexType4
, tmt.MortgageInterestRatePercentage1
, tmt.MortgageInterestRatePercentage2
, tmt.MortgageInterestRatePercentage3
, tmt.MortgageInterestRatePercentage4
, tmt.MortgageInterestRatePercentMaximum1
, tmt.MortgageInterestRatePercentMaximum2
, tmt.MortgageInterestRatePercentMaximum3
, tmt.MortgageInterestRatePercentMaximum4
, tmt.MortgageRateType1
, tmt.MortgageRateType2
, tmt.MortgageRateType3
, tmt.MortgageRateType4
, tmt.MortgageLoanTypeCode1
, tmt.MortgageLoanTypeCode2
, tmt.MortgageLoanTypeCode3
, tmt.MortgageLoanTypeCode4
, tmt.MortgageModifiedDueDate1
, tmt.MortgageModifiedDueDate2
, tmt.MortgageModifiedDueDate3
, tmt.MortgageModifiedDueDate4
, tmt.MostRecentJR1
, tmt.MostRecentJR2
, tmt.MostRecentJR3
, tmt.MostRecentJR4
, tmt.MostRecentFirst1
, tmt.MostRecentFirst2
, tmt.MostRecentFirst3
, tmt.MostRecentFirst4
, tmt.MortgageLoanPurpose1
, tmt.MortgageLoanPurpose2
, tmt.MortgageLoanPurpose3
, tmt.MortgageLoanPurpose4
, tmt.MortgageRecordingDate1
, tmt.MortgageRecordingDate2
, tmt.MortgageRecordingDate3
, tmt.MortgageRecordingDate4
, tmt.MortgageStatusInd1
, tmt.MortgageStatusInd2
, tmt.MortgageStatusInd3
, tmt.MortgageStatusInd4
, tmt.MortgageTermAmount1
, tmt.MortgageTermAmount2
, tmt.MortgageTermAmount3
, tmt.MortgageTermAmount4
, tmt.MortgageTrustPosition1
, tmt.MortgageTrustPosition2
, tmt.MortgageTrustPosition3
, tmt.MortgageTrustPosition4
, tmt.MortgageEquityLoanInd1
, tmt.MortgageEquityLoanInd2
, tmt.MortgageEquityLoanInd3
, tmt.MortgageEquityLoanInd4
, tmt.MortgagePrivatePartyLoanInd1
, tmt.MortgagePrivatePartyLoanInd2
, tmt.MortgagePrivatePartyLoanInd3
, tmt.MortgagePrivatePartyLoanInd4
, tmt.MortgageRefinanceLoanInd1
, tmt.MortgageRefinanceLoanInd2
, tmt.MortgageRefinanceLoanInd3
, tmt.MortgageRefinanceLoanInd4
, tmt.MortgageMortgageEquityLoanType1
, tmt.MortgageMortgageEquityLoanType2
, tmt.MortgageMortgageEquityLoanType3
, tmt.MortgageMortgageEquityLoanType4
, CAST (BINARY_CHECKSUM (tmt.TransFiller1 + tmt.TransFiller2 + tmt.MortgageFiller1 + tmt.MortgageFiller2) % 2 AS bit) AS Filler
FROM (SELECT
p.PclId
, up.UnvPclId
, p.ForeClosureDate
, p.ForeclosureStatus
, p.MetropolitanStatisticalArea
, p.OwnerOccupiedInd
, p.PropertyZipId
, p.PropertyType
, p.SitusCbsaCd
, p.FrclChronoDate
, p.UnvHomeSteadExemptCd
, p.UnvImpvValAmount
, p.UnvImpvValSourceInd
, p.UnvLandValAmount
, p.UnvLandValSourceInd
, p.UnvLocationInfoCd
, p.UnvOccupTypCd
, p.UnvSeniorExemptCd
, p.UnvTotalValAmount
, p.UnvTotalValSourceInd
, p.UnvVeteranExemptCd
, p.UnvWidowExemptCd
, p.AbsenteeOwnrIrisCd
, p.AssdDate
, p.BldgCntTotal
, p.CntySubdCd
, p.LandUseId
, p.LandDimSqFtTotal
, p.LotUnitsTotal
, p.MobileHomeInd
, p.NeighborhoodCd
, p.NetTaxBegDate
, p.NetTaxEndDate
, p.OccupTypCd
, p.OwnrOccupInd
, p.PropUseCd
, p.SitusCSZTypInd
, p.SitusDpvCd
, p.SitusGeoMatchCd
, p.SitusMatchCd
, p.SitusMsa
, p.StdSubdCd
, p.SumAreaAboveGround
, p.SumAreaAdj
, p.SumAreaBldg
, p.SumAreaBsmt
, p.SumAreaCanopy
, p.SumAreaLiv
, p.SumAreaOffice
, p.SumAreaRental
, p.SumNbrBath
, p.SumNbrBathHalf
, p.SumNbrBdrm
, p.SumNbrBldg
, p.SumNbrCondo
, p.SumNbrElevator
, p.SumNbrFirepl
, p.SumNbrRm
, p.SumNbrUnits
, p.SumNbrUnits1Bdrm
, p.SumNbrUnits2Bdrm
, p.SumNbrUnits3Bdrm
, p.SumNbrUnitsEff
, p.TaxCalcAmount
, p.TaxDelinqAmount
, p.TaxDelinqDate
, p.TaxDelinqInd
, p.TaxRollDate
, p.TotalTaxAmount
, p.TotalTaxBegDate
, p.TotalTaxEndDate
, p.UnvClassCd
, p.UnvElectCd
, p.UnvFuelCd
, p.UnvGovtExemptCd
, p.ZnCd
, plp.OwnerAddrOptInd
, plp.OwnerPhoneOptInd
, plp.IrisCorpInd
, plpn.OwnerRelationshipTypCd1
, plpn.OwnerRelationshipTypCd2
, plpn.OwnerShipRightsCd1
, plpn.OwnerShipRightsCd2
, plpn.CorpInd1
, plpn.CorpInd2
, pld.LandDimAcresNbr
, pld.LandDimDepthNbr
, pld.LandDimFrontNbr
, pld.LandDimSqFtNbr
, pld.LotUnitsNbr
, pte.GovtExmpt
, pte.HmeStdExmpt
, pte.MiscExmpt
, pte.ReligExmpt
, pte.SenrExmpt
, pte.UtilExmpt
, pte.VeteranExmpt
, pte.WidowExmpt
, dm.AmericanExpressPremium
, dm.AmericanExpressRegular
, dm.AnimalWelfareContributions
, dm.BooksDMMercByrCat
, dm.CatEnthusiasts
, dm.CDsMoneyMarketCurrently
, dm.ChildrensWelfare
, dm.ColSpcFoodDMMercByrCat
, dm.ComputersPeripherals
, dm.ConfirmedPOCModel
, dm.CraftsHobbieDMMercByrCat
, dm.CulinaryInterestMagByrCat
, dm.CulturalActivities
, dm.DiscoverRegular
, dm.DogEnthusiasts
, dm.DoItYourselfers
, dm.DwellingTyp
, dm.DwellingUnitSizeCd
, dm.EnhancedEstimatedHHIncome
, dm.Environmental
, dm.EstimatedHHIncome
, dm.EthnicInsightMatchFlg
, dm.FamilyGeneralMagByrCat
, dm.FemaleOrientDMMercByrCat
, dm.FemaleOrientMagByrCat
, dm.GardenFarmDMMercByrCat
, dm.GardenFarmingMagByrCat
, dm.GasCard
, dm.GeneralContributorCat
, dm.GeneralDMMercByrCat
, dm.GiftGadgetDMMercByrCat
, dm.HealthAndFitnessMagByrCat
, dm.HealthAndInstContributorCat
, dm.HealthRelated
, dm.HHCompositionCd
, dm.HHStatusCd
, dm.HomeDecoratingFurnishing
, dm.InterestInAutomotive
, dm.InterestInFitness
, dm.InterestInGourmetCooking
, dm.InterestInMoneyAndInvesting
, dm.InterestInSports
, dm.InterestInTheOutdoors
, dm.Investors
, dm.InvestsMutualFundsAnnuities
, dm.IRAsCurrently
, dm.IRAsFutureInterest
, dm.LifeInsuranceCurrently
, dm.MaleAndSportOrienMagByrCat
, dm.MaleOrientDMMercByrCat
, dm.MastercardPremium
, dm.MastercardRegular
, dm.MedEducationYearsAttained
, dm.MiscMailResponder
, dm.LifeStyle
, dm.MutualFundsCurrently
, dm.MutualFundsFutureInterest
, dm.NbrOfChildren18OrLess
, dm.NbrOfPersonsOnRecord
, dm.NewsAndFinancial
, dm.OddsAndEndsMailResponder
, dm.OtherCardRegular
, dm.OtherInvestmentsCurrently
, dm.OtherInvestmentsFutureInterest
, dm.PetEnthusiast
, dm.Photography
, dm.POC00To03CombTypGender
, dm.POC10To12CombTypGender
, dm.POC13To18CombTypGender
, dm.POC04To06CombTypGender
, dm.POC07To09CombTypGender
, dm.Political
, dm.PoliticalContributorCat
, dm.ProbableHomeOwnr
, dm.RealEstateCurrently
, dm.RealEstateFutureInterest
, dm.RecipientReliabilityCd
, dm.ReligiousContributions
, dm.ReligiousContributorCat
, dm.ReligiousMagByrCat
, dm.RuralUrbanCntySizeCd
, dm.SocialServices
, dm.StocksBondsCurrently
, dm.StocksBondsFutureInterest
, dm.StoreOrRetailRegular
, dm.SurveyNameOutputRABucket
, dm.Sweepstakes
, dm.SweepstakesGambling
, dm.TimeZone
, dm.TotalEnhancementMatchTyp
, dm.UnitsInStructureMobileHome
, dm.UpscaleDMMercByrCat
, dm.VisaPremium
, dm.VisaRegular
, dm.YOwnsHome
, dm.YProbableRenter
, dm.YRenter
, dm.NbrOfAdultsInHH
, dm.I1_BusnsOwnrFlg
, dm.I1_CntryOfOrigin
, dm.I1_Age
, dm.I1_Education
, dm.I1_EthnicExpGrpCd
, dm.I1_EthnicGrpCd
, dm.I1_EthnicCd
, dm.I1_GenderCd
, dm.I1_Language
, dm.I1_MaritalStatus
, dm.I1_Occupation
, dm.I1_OccupationGrp
, dm.I1_PersonTyp
, dm.I1_Religion
, dm.I2_BusnsOwnrFlg
, dm.I2_CntryOfOrigin
, dm.I2_Age
, dm.I2_Education
, dm.I2_EthnicExpGrpCd
, dm.I2_EthnicGrpCd
, dm.I2_EthnicCd
, dm.I2_GenderCd
, dm.I2_Language
, dm.I2_MaritalStatus
, dm.I2_Occupation
, dm.I2_OccupationGrp
, dm.I2_PersonTyp
, dm.I2_Religion
, dm.I2_TitleOfRespect
, sc.ConventionalRefi
, sc.HomeEquity
, sc.CashOutRefi
, sc.FHARefi
, sc.IncomeEstimateInDollars
, sc.TotalLiquidAssets
, sc.LifeInsurance
, sc.CharitableDonor
, sc.SubprimeBorrowerFlag
, avm.AvmAmount
, gc.GeoCoreDate
, gc.GeoCoreAmount
, gc.GeoCoreScore
, gc.GeoCoreFSDScore
, gc.GeoCoreSource
, CAST (CASE
WHEN he.PclId IS NOT NULL THEN 1
ELSE 0
END AS bit) AS HarpEligibility
FROM (
SELECT TOP (999999999999)
p.PclId
, p.FrclChronoDt AS ForeClosureDate
, CAST (CASE p.FrclExistFlg
WHEN 'RR' THEN 1
WHEN 'SS' THEN 2
WHEN 'YA' THEN 3
WHEN 'YD' THEN 4
END AS tinyint) AS ForeclosureStatus

/*SELECT DISTINCT try_cast(MSACd AS smallint) AS MSACd, MSAName FROM tCommon.GeoMapping2010 WHERE MSACd <> ''*/

, TRY_CONVERT (smallint, p.SitusMsa) AS MetropolitanStatisticalArea
, CAST (CASE
WHEN p.OwnrOccupInd = 'Y' THEN 1
ELSE 0
END AS bit) AS OwnerOccupiedInd
, CASE
WHEN p.SitusStdZipCd LIKE '[0-9][0-9][0-9][0-9][0-9]%' THEN CASE
WHEN LEFT (p.SitusStdZipCd, 5) NOT BETWEEN '00501' AND '99950' THEN NULL
ELSE @PclHeader + TRY_CONVERT (bigint, LEFT (p.SitusStdZipCd, 5))
END
END AS PropertyZipId
, TRY_CONVERT (tinyint, p.PropIndCd) AS PropertyType

/*SELECT DISTINCT try_cast(CBSACd AS int) AS CBSACd, CBSACd, CBSAName FROM tCommon.GeoMapping2010 WHERE CBSACd <> ''*/

, TRY_CONVERT (int, p.SitusCbsaCd) AS SitusCbsaCd
, p.FrclChronoDt AS FrclChronoDate
, p.UnvHomeSteadExemptCd
, CAST (p.UnvImpvValAmt AS bigint) AS UnvImpvValAmount
, p.UnvImpvValSourceInd
, CAST (p.UnvLandValAmt AS bigint) AS UnvLandValAmount
, p.UnvLandValSourceInd
, p.UnvLocationInfoCd
, p.UnvOccupTypCd
, p.UnvSeniorExemptCd
, p.UnvTotalValAmt AS UnvTotalValAmount
, p.UnvTotalValSourceInd
, p.UnvVeteranExemptCd
, p.UnvWidowExemptCd
, p.AbsenteeOwnrIrisCd
, p.AssdDt AS AssdDate
, p.BldgCntTotal
, TRY_CONVERT (smallint, p.CntySubdCd) AS CntySubdCd
, ISNULL ((
SELECT
lu.LandUseId
FROM Temporary.dbo.LandUseCntyMap AS lu WITH (FORCESEEK (1 (CntyCd, CdVal)))
WHERE lu.CntyCd = @CntyCd
AND lu.CdVal = p.LandUseMajorCd) , TRY_CONVERT (int, p.IrisLandUseCd)) AS LandUseId
, p.LandDimSqFtTotal
, p.LotUnitsTotal
, p.MobileHomeInd
, p.NeighborhoodCd
, NetTaxBegYy * 10000 + 101 AS NetTaxBegDate
, NetTaxEndYy * 10000 + 101 AS NetTaxEndDate
, p.OccupTypCd
, p.OwnrOccupInd
, p.PropUseCd
, p.SitusCSZTypInd
, p.SitusDpvCd
, p.SitusGeoMatchCd
, p.SitusMatchCd
, p.SitusMsa
, p.StdSubdCd
, CAST (p.SumAreaAboveGround AS bigint) AS SumAreaAboveGround
, CAST (p.SumAreaAdj AS bigint) AS SumAreaAdj
, CAST (p.SumAreaBldg AS bigint) AS SumAreaBldg
, CAST (p.SumAreaBsmt AS bigint) AS SumAreaBsmt
, CAST (p.SumAreaCanopy AS bigint) AS SumAreaCanopy
, CAST (p.SumAreaLiv AS bigint) AS SumAreaLiv
, CAST (p.SumAreaOffice AS bigint) AS SumAreaOffice
, CAST (p.SumAreaRental AS bigint) AS SumAreaRental
, p.SumNbrBath
, p.SumNbrBathHalf
, p.SumNbrBdrm
, p.SumNbrBldg
, p.SumNbrCondo
, p.SumNbrElevator
, p.SumNbrFirepl
, p.SumNbrRm
, p.SumNbrUnits
, p.SumNbrUnits1Bdrm
, p.SumNbrUnits2Bdrm
, p.SumNbrUnits3Bdrm
, p.SumNbrUnitsEff
, CAST (p.TaxCalcAmt AS money) AS TaxCalcAmount
, CAST (p.TaxDelinqAmt AS money) AS TaxDelinqAmount
, p.TaxDelinqDt AS TaxDelinqDate
, p.TaxDelinqInd
, p.TaxRollDt AS TaxRollDate
, CAST (p.TotalTaxAmt AS money) AS TotalTaxAmount
, p.TotalTaxBegYY * 10000 + 101 AS TotalTaxBegDate
, p.TotalTaxEndYY * 10000 + 101 AS TotalTaxEndDate
, p.UnvClassCd
, p.UnvElectCd
, p.UnvFuelCd
, p.UnvGovtExemptCd
, p.ZnCd
FROM tTax.Property AS p
WHERE p.CntyCd = @CntyCd
AND p.PclSeqNbr = 1
ORDER BY
p.PclId) AS p
INNER JOIN (
SELECT TOP (999999999999)
up.PclId
, @PclHeader + up.UnvPclId AS UnvPclId
FROM tCommon.UnvPclId AS up
WHERE up.CntyCd = @CntyCd
AND up.PclSeqNbr = 1
AND up.Edition = @Edition
ORDER BY
up.PclId) AS up
ON p.PclId = up.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
he.PclId
FROM Temporary.dbo.HarpEligibility AS he
WHERE he.CntyCd = @CntyCd
ORDER BY
he.PclId) AS he
ON p.PclId = he.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
b.PclId
, b.CommUnitsNbr
, b.ResUnitsNbr
FROM tTax.Building AS b
WHERE b.CntyCd = @CntyCd
AND b.PclSeqNbr = 1
AND b.BldgSeqNbr = 1
ORDER BY
b.PclId) AS b
ON p.PclId = b.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
PclId
, plp.AddrOptSourceId AS OwnerAddrOptInd
, plp.PhoneOptInd AS OwnerPhoneOptInd
, plp.IrisCorpInd
FROM tTax.PropertyLegalParty AS plp
WHERE plp.CntyCd = @CntyCd
AND plp.PclSeqNbr = 1
AND plp.NameTypId = 2

/* Owner */

ORDER BY
plp.PclId) AS plp
ON p.PclId = plp.PclId
LEFT MERGE JOIN (
SELECT
PclId
, MAX (CASE
WHEN NameSeq = 1 THEN OwnerRelationshipTypCd
END) AS OwnerRelationshipTypCd1
, MAX (CASE
WHEN NameSeq = 2 THEN OwnerRelationshipTypCd
END) AS OwnerRelationshipTypCd2
, MAX (CASE
WHEN NameSeq = 1 THEN OwnerShipRightsCd
END) AS OwnerShipRightsCd1
, MAX (CASE
WHEN NameSeq = 2 THEN OwnerShipRightsCd
END) AS OwnerShipRightsCd2
, MAX (CASE
WHEN NameSeq = 2 THEN CorpInd
END) AS CorpInd1
, MAX (CASE
WHEN NameSeq = 2 THEN CorpInd
END) AS CorpInd2
FROM (
SELECT TOP (999999999999)
PclId
, NameSeq
, plpn.RelTypCd AS OwnerRelationshipTypCd
, plpn.OwnrShipRightsCd AS OwnerShipRightsCd
, plpn.CorpInd
FROM tTax.PropertyLegalPartyName AS plpn
WHERE plpn.CntyCd = @CntyCd
AND plpn.PclSeqNbr = 1
AND plpn.NameTypId = 2

/* Owner */

AND plpn.NameSeq IN (1, 2)
ORDER BY
plpn.PclId) AS plpn
GROUP BY
PclId) AS plpn
ON p.PclId = plpn.PclId
LEFT MERGE JOIN (
SELECT
pld.PclId
, SUM (CAST (LandDimAcresNbr AS bigint)) AS LandDimAcresNbr
, SUM (TRY_CONVERT (int, LandDimDepthNbr)) AS LandDimDepthNbr
, SUM (TRY_CONVERT (int, LandDimFrontNbr)) AS LandDimFrontNbr
, SUM (CAST (LandDimSqFtNbr AS bigint)) AS LandDimSqFtNbr
, SUM (TRY_CONVERT (int, LotUnitsNbr)) AS LotUnitsNbr
FROM (
SELECT TOP (999999999999)
*
FROM tTax.PropertyLandDimensionDetail AS pld
WHERE pld.CntyCd = @CntyCd
AND pld.PclSeqNbr = 1
ORDER BY
pld.PclId) AS pld
GROUP BY
pld.PclId) AS pld
ON p.PclId = pld.PclId
LEFT MERGE JOIN (
SELECT
pte.PclId
, CAST (MAX (CASE
WHEN TaxExemptId = 14 THEN TaxExemptCd
END) AS bit) AS GovtExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 1 THEN TaxExemptCd
END) AS bit) AS HmeStdExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 20 THEN TaxExemptCd
END) AS bit) AS MiscExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 7 THEN TaxExemptCd
END) AS bit) AS ReligExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 5 THEN TaxExemptCd
END) AS bit) AS SenrExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 9 THEN TaxExemptCd
END) AS bit) AS UtilExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 2 THEN TaxExemptCd
END) AS bit) AS VeteranExmpt
, CAST (MAX (CASE
WHEN TaxExemptId = 4 THEN TaxExemptCd
END) AS bit) AS WidowExmpt
FROM (
SELECT TOP (999999999999)
pte.PclId
, TaxExemptId
, CAST (CASE
WHEN pte.TaxExemptCd <> '' THEN 1
ELSE 0
END AS tinyint) AS TaxExemptCd
FROM tTax.PropertyTaxExemption AS pte
WHERE pte.CntyCd = @CntyCd
AND pte.PclSeqNbr = 1
AND pte.TaxExemptId IN (1, 14, 2, 20, 4, 5, 7, 9)
ORDER BY
pte.PclId) AS pte
GROUP BY
pte.PclId) AS pte
ON p.PclId = pte.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
dm.PclId
, dm.AmericanExpressPremium
, dm.AmericanExpressRegular
, dm.AnimalWelfareContributions
, dm.BooksDMMercByrCat
, dm.CatEnthusiasts
, dm.CDsMoneyMarketCurrently
, dm.ChildrensWelfare
, dm.ColSpcFoodDMMercByrCat
, dm.ComputersPeripherals
, dm.ConfirmedPOCModel
, dm.CraftsHobbieDMMercByrCat
, dm.CulinaryInterestMagByrCat
, dm.CulturalActivities
, dm.DiscoverRegular
, dm.DogEnthusiasts
, dm.DoItYourselfers
, dm.DwellingTyp
, dm.DwellingUnitSizeCd
, dm.EnhancedEstimatedHHIncome
, dm.Environmental
, dm.EstimatedHHIncome
, dm.EthnicInsightMatchFlg
, dm.FamilyGeneralMagByrCat
, dm.FemaleOrientDMMercByrCat
, dm.FemaleOrientMagByrCat
, dm.GardenFarmDMMercByrCat
, dm.GardenFarmingMagByrCat
, dm.GasCard
, dm.GeneralContributorCat
, dm.GeneralDMMercByrCat
, dm.GiftGadgetDMMercByrCat
, dm.HealthAndFitnessMagByrCat
, dm.HealthAndInstContributorCat
, dm.HealthRelated
, dm.HHCompositionCd
, dm.HHStatusCd
, dm.HomeDecoratingFurnishing
, dm.InterestInAutomotive
, dm.InterestInFitness
, dm.InterestInGourmetCooking
, dm.InterestInMoneyAndInvesting
, dm.InterestInSports
, dm.InterestInTheOutdoors
, dm.Investors
, dm.InvestsMutualFundsAnnuities
, dm.IRAsCurrently
, dm.IRAsFutureInterest
, dm.LifeInsuranceCurrently
, dm.MaleAndSportOrienMagByrCat
, dm.MaleOrientDMMercByrCat
, dm.MastercardPremium
, dm.MastercardRegular
, dm.MedEducationYearsAttained
, dm.MiscMailResponder
, dm.MosaicGrpTyp AS LifeStyle
, dm.MutualFundsCurrently
, dm.MutualFundsFutureInterest
, dm.NbrOfChildren18OrLess
, dm.NbrOfPersonsOnRecord
, dm.NewsAndFinancial
, dm.OddsAndEndsMailResponder
, dm.OtherCardRegular
, dm.OtherInvestmentsCurrently
, dm.OtherInvestmentsFutureInterest
, dm.PetEnthusiast
, dm.Photography
, dm.POC00To03CombTypGender
, dm.POC04To06CombTypGender
, dm.POC07To09CombTypGender
, dm.POC10To12CombTypGender
, dm.POC13To18CombTypGender
, dm.Political
, dm.PoliticalContributorCat
, dm.ProbableHomeOwnr
, dm.RealEstateCurrently
, dm.RealEstateFutureInterest
, dm.RecipientReliabilityCd
, dm.ReligiousContributions
, dm.ReligiousContributorCat
, dm.ReligiousMagByrCat
, dm.RuralUrbanCntySizeCd
, dm.SocialServices
, dm.StocksBondsCurrently
, dm.StocksBondsFutureInterest
, dm.StoreOrRetailRegular
, dm.SurveyNameOutputRABucket
, dm.Sweepstakes
, dm.SweepstakesGambling
, dm.TimeZone
, dm.TotalEnhancementMatchTyp
, dm.UnitsInStructureMobileHome
, dm.UpscaleDMMercByrCat
, dm.VisaPremium
, dm.VisaRegular
, dm.YOwnsHome
, dm.YProbableRenter
, dm.YRenter
, dm.NbrOfAdultsInHH
, dm.I1_BusnsOwnrFlg
, dm.I1_CntryOfOrigin
, dm.I1_Education
, dm.I1_Age
, dm.I1_EthnicExpGrpCd
, dm.I1_EthnicGrpCd
, dm.I1_EthnicCd
, dm.I1_GenderCd
, dm.I1_Language
, dm.I1_MaritalStatus
, dm.I1_Occupation
, dm.I1_OccupationGrp
, dm.I1_PersonTyp
, dm.I1_Religion
, dm.I2_BusnsOwnrFlg
, dm.I2_CntryOfOrigin
, dm.I2_Education
, dm.I2_Age
, dm.I2_EthnicExpGrpCd
, dm.I2_EthnicGrpCd
, dm.I2_EthnicCd
, dm.I2_GenderCd
, dm.I2_Language
, dm.I2_MaritalStatus
, dm.I2_Occupation
, dm.I2_OccupationGrp
, dm.I2_PersonTyp
, dm.I2_Religion
, dm.I2_TitleOfRespect
FROM [Temporary].[dbo].[DiabloDW_Demographics] AS dm
WHERE dm.CntyCd = @CntyCd
ORDER BY
dm.PclId) AS dm
ON p.PclId = dm.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
sc.CntyCd
, sc.PclId
, sc.ConventionalRefi
, sc.HomeEquity
, sc.CashOutRefi
, sc.FHARefi
, TRY_CONVERT (smallint, sc.IncomeEstimateInDollars) AS IncomeEstimateInDollars
, CAST (sc.TotalLiquidAssets AS bigint) AS TotalLiquidAssets
, sc.LifeInsurance
, sc.CharitableDonor
, sc.SubprimeBorrowerFlag
FROM LTL.dbo.SMRScores AS sc
WHERE sc.CntyCd = @CntyCd
ORDER BY
sc.PclId) AS sc
ON p.PclId = sc.PclId
LEFT MERGE JOIN (
SELECT TOP (999999999999)
avm.CNTY_CD
, avm.PCL_ID
, avm.VALUE AS AvmAmount
FROM AVM.dbo.AVMX AS avm
WHERE avm.CNTY_CD = @CntyCd
AND VALUE > 0
ORDER BY
avm.PCL_ID) AS avm
ON p.PclId = avm.PCL_ID
LEFT MERGE JOIN (
SELECT TOP (999999999999)
gc.CNTY_CD
, gc.PCL_ID
, CAST (CONVERT (varchar (30) , gc.[VALUATION_DATE], 112) AS int) AS GeoCoreDate
, gc.[VALUE] AS GeoCoreAmount
, TRY_CONVERT (tinyint, gc.[SCORE]) AS GeoCoreScore

/* 0 - 99 */

, TRY_CONVERT (tinyint, gc.[FSD_SCORE]) AS GeoCoreFSDScore

/* 0 - 25 */

, CAST (CASE gc.[VALUATION_CD]
WHEN 'HPA' THEN 1
WHEN 'PASS' THEN 2
WHEN 'PB6' THEN 3
WHEN 'VP4' THEN 4
END AS tinyint) AS GeoCoreSource
FROM AVM.[dbo].[GEO_CORE] AS gc
WHERE gc.CNTY_CD = @CntyCd
ORDER BY
gc.PCL_ID) AS gc
ON p.PclId = gc.PCL_ID) AS pp
LEFT OUTER MERGE JOIN (SELECT
PclId
, MAX (tm3.TransFiller1) AS TransFiller1
, MAX (tm3.TransFiller2) AS TransFiller2
, MAX (tm3.MortgageFiller1) AS MortgageFiller1
, MAX (tm3.MortgageFiller2) AS MortgageFiller2
, CAST (MAX (tm3.BeneficiaryPurchasedInd) AS bit) AS BeneficiaryPurchasedInd
, CAST (MAX (tm3.SaleArmsLengthCashInd) AS bit) AS SaleArmsLengthCashInd
, CAST (MAX (tm3.SaleArmsLengthMortgageInd) AS bit) AS SaleArmsLengthMortgageInd
, CAST (MAX (tm3.SaleInterrelatedInd) AS bit) AS SaleInterrelatedInd
, CAST (MAX (tm3.SaleInvestorPurchaseInd) AS bit) AS SaleInvestorPurchaseInd
, CAST (MAX (tm3.SaleNewConstructionInd) AS bit) AS SaleNewConstructionInd
, CAST (MAX (tm3.SaleRealEstateOwnedInd) AS bit) AS SaleRealEstateOwnedInd
, CAST (MAX (tm3.SaleRealEstateOwnedSaleInd) AS bit) AS SaleRealEstateOwnedSaleInd
, MAX (tm3.SaleResaleInd) AS SaleResaleInd
, MAX (tm3.SaleResModelInd) AS SaleResModelInd
, MAX (tm3.SaleShortSaleInd) AS SaleShortSaleInd
, MAX (tm3.MultipleSplitCode) AS MultipleSplitCode
, CAST (MAX (tm3.NominalIndicator) AS bit) AS NominalIndicator
, MAX (tm3.SaleDate) AS SaleDate
, MAX (tm3.SalesPriceAmount) AS SalesPriceAmount
, MAX (tm3.SaleRecordedDate) AS SaleRecordedDate
, MAX (tm3.TransactionType) AS TransactionType
, MAX (tm3.MortgageAmount1) AS MortgageAmount1
, MAX (tm3.MortgageAmount2) AS MortgageAmount2
, MAX (tm3.MortgageAmount3) AS MortgageAmount3
, MAX (tm3.MortgageAmount4) AS MortgageAmount4
, MAX (tm3.MortgageCompanyCd1) AS MortgageCompanyCd1
, MAX (tm3.MortgageCompanyCd2) AS MortgageCompanyCd2
, MAX (tm3.MortgageCompanyCd3) AS MortgageCompanyCd3
, MAX (tm3.MortgageCompanyCd4) AS MortgageCompanyCd4
, MAX (tm3.MortgageDocumentDate1) AS MortgageDocumentDate1
, MAX (tm3.MortgageDocumentDate2) AS MortgageDocumentDate2
, MAX (tm3.MortgageDocumentDate3) AS MortgageDocumentDate3
, MAX (tm3.MortgageDocumentDate4) AS MortgageDocumentDate4
, MAX (tm3.MortgageDate1) AS MortgageDate1
, MAX (tm3.MortgageDate2) AS MortgageDate2
, MAX (tm3.MortgageDate3) AS MortgageDate3
, MAX (tm3.MortgageDate4) AS MortgageDate4
, MAX (tm3.MortgageDueDate1) AS MortgageDueDate1
, MAX (tm3.MortgageDueDate2) AS MortgageDueDate2
, MAX (tm3.MortgageDueDate3) AS MortgageDueDate3
, MAX (tm3.MortgageDueDate4) AS MortgageDueDate4
, MAX (tm3.MortgageInterestRateCap1) AS MortgageInterestRateCap1
, MAX (tm3.MortgageInterestRateCap2) AS MortgageInterestRateCap2
, MAX (tm3.MortgageInterestRateCap3) AS MortgageInterestRateCap3
, MAX (tm3.MortgageInterestRateCap4) AS MortgageInterestRateCap4
, MAX (tm3.MortgageInterestRateChangeDate1) AS MortgageInterestRateChangeDate1
, MAX (tm3.MortgageInterestRateChangeDate2) AS MortgageInterestRateChangeDate2
, MAX (tm3.MortgageInterestRateChangeDate3) AS MortgageInterestRateChangeDate3
, MAX (tm3.MortgageInterestRateChangeDate4) AS MortgageInterestRateChangeDate4
, MAX (tm3.MortgageInterestRateChangeIntervalMonths1) AS MortgageInterestRateChangeIntervalMonths1
, MAX (tm3.MortgageInterestRateChangeIntervalMonths2) AS MortgageInterestRateChangeIntervalMonths2
, MAX (tm3.MortgageInterestRateChangeIntervalMonths3) AS MortgageInterestRateChangeIntervalMonths3
, MAX (tm3.MortgageInterestRateChangeIntervalMonths4) AS MortgageInterestRateChangeIntervalMonths4
, MAX (tm3.MortgageInterestRateChangePercentage1) AS MortgageInterestRateChangePercentage1
, MAX (tm3.MortgageInterestRateChangePercentage2) AS MortgageInterestRateChangePercentage2
, MAX (tm3.MortgageInterestRateChangePercentage3) AS MortgageInterestRateChangePercentage3
, MAX (tm3.MortgageInterestRateChangePercentage4) AS MortgageInterestRateChangePercentage4
, MAX (tm3.MortgageInterestRateChangePercentageLimit1) AS MortgageInterestRateChangePercentageLimit1
, MAX (tm3.MortgageInterestRateChangePercentageLimit2) AS MortgageInterestRateChangePercentageLimit2
, MAX (tm3.MortgageInterestRateChangePercentageLimit3) AS MortgageInterestRateChangePercentageLimit3
, MAX (tm3.MortgageInterestRateChangePercentageLimit4) AS MortgageInterestRateChangePercentageLimit4
, MAX (tm3.MortgageInterestRateIndexType1) AS MortgageInterestRateIndexType1
, MAX (tm3.MortgageInterestRateIndexType2) AS MortgageInterestRateIndexType2
, MAX (tm3.MortgageInterestRateIndexType3) AS MortgageInterestRateIndexType3
, MAX (tm3.MortgageInterestRateIndexType4) AS MortgageInterestRateIndexType4
, MAX (tm3.MortgageInterestRatePercentage1) AS MortgageInterestRatePercentage1
, MAX (tm3.MortgageInterestRatePercentage2) AS MortgageInterestRatePercentage2
, MAX (tm3.MortgageInterestRatePercentage3) AS MortgageInterestRatePercentage3
, MAX (tm3.MortgageInterestRatePercentage4) AS MortgageInterestRatePercentage4
, MAX (tm3.MortgageInterestRatePercentMaximum1) AS MortgageInterestRatePercentMaximum1
, MAX (tm3.MortgageInterestRatePercentMaximum2) AS MortgageInterestRatePercentMaximum2
, MAX (tm3.MortgageInterestRatePercentMaximum3) AS MortgageInterestRatePercentMaximum3
, MAX (tm3.MortgageInterestRatePercentMaximum4) AS MortgageInterestRatePercentMaximum4
, MAX (tm3.MortgageRateType1) AS MortgageRateType1
, MAX (tm3.MortgageRateType2) AS MortgageRateType2
, MAX (tm3.MortgageRateType3) AS MortgageRateType3
, MAX (tm3.MortgageRateType4) AS MortgageRateType4
, MAX (tm3.MortgageLoanTypeCode1) AS MortgageLoanTypeCode1
, MAX (tm3.MortgageLoanTypeCode2) AS MortgageLoanTypeCode2
, MAX (tm3.MortgageLoanTypeCode3) AS MortgageLoanTypeCode3
, MAX (tm3.MortgageLoanTypeCode4) AS MortgageLoanTypeCode4
, MAX (tm3.MortgageModifiedDueDate1) AS MortgageModifiedDueDate1
, MAX (tm3.MortgageModifiedDueDate2) AS MortgageModifiedDueDate2
, MAX (tm3.MortgageModifiedDueDate3) AS MortgageModifiedDueDate3
, MAX (tm3.MortgageModifiedDueDate4) AS MortgageModifiedDueDate4
, CAST (MAX (tm3.MostRecentJR1) AS bit) AS MostRecentJR1
, CAST (MAX (tm3.MostRecentJR2) AS bit) AS MostRecentJR2
, CAST (MAX (tm3.MostRecentJR3) AS bit) AS MostRecentJR3
, CAST (MAX (tm3.MostRecentJR4) AS bit) AS MostRecentJR4
, CAST (MAX (tm3.MostRecentFirst1) AS bit) AS MostRecentFirst1
, CAST (MAX (tm3.MostRecentFirst2) AS bit) AS MostRecentFirst2
, CAST (MAX (tm3.MostRecentFirst3) AS bit) AS MostRecentFirst3
, CAST (MAX (tm3.MostRecentFirst4) AS bit) AS MostRecentFirst4
, MAX (tm3.MortgageLoanPurpose1) AS MortgageLoanPurpose1
, MAX (tm3.MortgageLoanPurpose2) AS MortgageLoanPurpose2
, MAX (tm3.MortgageLoanPurpose3) AS MortgageLoanPurpose3
, MAX (tm3.MortgageLoanPurpose4) AS MortgageLoanPurpose4
, MAX (tm3.MortgageRecordingDate1) AS MortgageRecordingDate1
, MAX (tm3.MortgageRecordingDate2) AS MortgageRecordingDate2
, MAX (tm3.MortgageRecordingDate3) AS MortgageRecordingDate3
, MAX (tm3.MortgageRecordingDate4) AS MortgageRecordingDate4
, MAX (tm3.MortgageStatusInd1) AS MortgageStatusInd1
, MAX (tm3.MortgageStatusInd2) AS MortgageStatusInd2
, MAX (tm3.MortgageStatusInd3) AS MortgageStatusInd3
, MAX (tm3.MortgageStatusInd4) AS MortgageStatusInd4
, MAX (tm3.MortgageTermAmount1) AS MortgageTermAmount1
, MAX (tm3.MortgageTermAmount2) AS MortgageTermAmount2
, MAX (tm3.MortgageTermAmount3) AS MortgageTermAmount3
, MAX (tm3.MortgageTermAmount4) AS MortgageTermAmount4
, MAX (tm3.MortgageTrustPosition1) AS MortgageTrustPosition1
, MAX (tm3.MortgageTrustPosition2) AS MortgageTrustPosition2
, MAX (tm3.MortgageTrustPosition3) AS MortgageTrustPosition3
, MAX (tm3.MortgageTrustPosition4) AS MortgageTrustPosition4
, CAST (MAX (tm3.MortgageEquityLoanInd1) AS bit) AS MortgageEquityLoanInd1
, CAST (MAX (tm3.MortgageEquityLoanInd2) AS bit) AS MortgageEquityLoanInd2
, CAST (MAX (tm3.MortgageEquityLoanInd3) AS bit) AS MortgageEquityLoanInd3
, CAST (MAX (tm3.MortgageEquityLoanInd4) AS bit) AS MortgageEquityLoanInd4
, CAST (MAX (tm3.MortgagePrivatePartyLoanInd1) AS bit) AS MortgagePrivatePartyLoanInd1
, CAST (MAX (tm3.MortgagePrivatePartyLoanInd2) AS bit) AS MortgagePrivatePartyLoanInd2
, CAST (MAX (tm3.MortgagePrivatePartyLoanInd3) AS bit) AS MortgagePrivatePartyLoanInd3
, CAST (MAX (tm3.MortgagePrivatePartyLoanInd4) AS bit) AS MortgagePrivatePartyLoanInd4
, CAST (MAX (tm3.MortgageRefinanceLoanInd1) AS bit) AS MortgageRefinanceLoanInd1
, CAST (MAX (tm3.MortgageRefinanceLoanInd2) AS bit) AS MortgageRefinanceLoanInd2
, CAST (MAX (tm3.MortgageRefinanceLoanInd3) AS bit) AS MortgageRefinanceLoanInd3
, CAST (MAX (tm3.MortgageRefinanceLoanInd4) AS bit) AS MortgageRefinanceLoanInd4
, MAX (tm3.MortgageMortgageEquityLoanType1) AS MortgageMortgageEquityLoanType1
, MAX (tm3.MortgageMortgageEquityLoanType2) AS MortgageMortgageEquityLoanType2
, MAX (tm3.MortgageMortgageEquityLoanType3) AS MortgageMortgageEquityLoanType3
, MAX (tm3.MortgageMortgageEquityLoanType4) AS MortgageMortgageEquityLoanType4
FROM (
SELECT TOP (999999999999)
t.PclId
, t.TransFiller1
, t.TransFiller2
, tm.MortgageFiller1
, tm.MortgageFiller2
, CASE
WHEN SalePosition = 1
AND t.BeneficiaryPurchasedInd = 'Y' THEN 1
ELSE 0
END AS BeneficiaryPurchasedInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleArmsLengthCashInd AS tinyint)
END AS SaleArmsLengthCashInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleArmsLengthMortgageInd AS tinyint)
END AS SaleArmsLengthMortgageInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleInterrelatedInd AS tinyint)
END AS SaleInterrelatedInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleInvestorPurchaseInd AS tinyint)
END AS SaleInvestorPurchaseInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleNewConstructionInd AS tinyint)
END AS SaleNewConstructionInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleRealEstateOwnedInd AS tinyint)
END AS SaleRealEstateOwnedInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleRealEstateOwnedSaleInd AS tinyint)
END AS SaleRealEstateOwnedSaleInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleResaleInd AS tinyint)
END AS SaleResaleInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleResModelInd AS tinyint)
END AS SaleResModelInd
, CASE
WHEN SalePosition = 1 THEN CAST (t.SaleShortSaleInd AS tinyint)
END AS SaleShortSaleInd
, CASE
WHEN SalePosition = 1 THEN t.MultipleSplitCode
END AS MultipleSplitCode
, CASE
WHEN SalePosition = 1
AND t.NominalIndicator = 'Y' THEN 1
END AS NominalIndicator
, CASE
WHEN SalePosition = 1 THEN t.SaleDateInt
END AS SaleDate
, CASE
WHEN SalePosition = 1 THEN t.SalesPriceAmount
END AS SalesPriceAmount
, CASE
WHEN SalePosition = 1 THEN t.SaleRecordedDateInt
END AS SaleRecordedDate
, CASE
WHEN SalePosition = 1 THEN t.TransactionType
END AS TransactionType
, tm.MortgageAmount1
, tm.MortgageAmount2
, tm.MortgageAmount3
, tm.MortgageAmount4
, tm.MortgageCompanyCd1
, tm.MortgageCompanyCd2
, tm.MortgageCompanyCd3
, tm.MortgageCompanyCd4
, tm.MortgageDocumentDate1
, tm.MortgageDocumentDate2
, tm.MortgageDocumentDate3
, tm.MortgageDocumentDate4
, tm.MortgageDate1
, tm.MortgageDate2
, tm.MortgageDate3
, tm.MortgageDate4
, tm.MortgageDueDate1
, tm.MortgageDueDate2
, tm.MortgageDueDate3
, tm.MortgageDueDate4
, tm.MortgageInterestRateCap1
, tm.MortgageInterestRateCap2
, tm.MortgageInterestRateCap3
, tm.MortgageInterestRateCap4
, tm.MortgageInterestRateChangeDate1
, tm.MortgageInterestRateChangeDate2
, tm.MortgageInterestRateChangeDate3
, tm.MortgageInterestRateChangeDate4
, tm.MortgageInterestRateChangeIntervalMonths1
, tm.MortgageInterestRateChangeIntervalMonths2
, tm.MortgageInterestRateChangeIntervalMonths3
, tm.MortgageInterestRateChangeIntervalMonths4
, tm.MortgageInterestRateChangePercentage1
, tm.MortgageInterestRateChangePercentage2
, tm.MortgageInterestRateChangePercentage3
, tm.MortgageInterestRateChangePercentage4
, tm.MortgageInterestRateChangePercentageLimit1
, tm.MortgageInterestRateChangePercentageLimit2
, tm.MortgageInterestRateChangePercentageLimit3
, tm.MortgageInterestRateChangePercentageLimit4
, tm.MortgageInterestRateIndexType1
, tm.MortgageInterestRateIndexType2
, tm.MortgageInterestRateIndexType3
, tm.MortgageInterestRateIndexType4
, tm.MortgageInterestRatePercentage1
, tm.MortgageInterestRatePercentage2
, tm.MortgageInterestRatePercentage3
, tm.MortgageInterestRatePercentage4
, tm.MortgageInterestRatePercentMaximum1
, tm.MortgageInterestRatePercentMaximum2
, tm.MortgageInterestRatePercentMaximum3
, tm.MortgageInterestRatePercentMaximum4
, tm.MortgageRateType1
, tm.MortgageRateType2
, tm.MortgageRateType3
, tm.MortgageRateType4
, tm.MortgageLoanTypeCode1
, tm.MortgageLoanTypeCode2
, tm.MortgageLoanTypeCode3
, tm.MortgageLoanTypeCode4
, tm.MortgageModifiedDueDate1
, tm.MortgageModifiedDueDate2
, tm.MortgageModifiedDueDate3
, tm.MortgageModifiedDueDate4
, tm.MostRecentJR1
, tm.MostRecentJR2
, tm.MostRecentJR3
, tm.MostRecentJR4
, tm.MostRecentFirst1
, tm.MostRecentFirst2
, tm.MostRecentFirst3
, tm.MostRecentFirst4
, tm.MortgageLoanPurpose1
, tm.MortgageLoanPurpose2
, tm.MortgageLoanPurpose3
, tm.MortgageLoanPurpose4
, tm.MortgageRecordingDate1
, tm.MortgageRecordingDate2
, tm.MortgageRecordingDate3
, tm.MortgageRecordingDate4
, tm.MortgageStatusInd1
, tm.MortgageStatusInd2
, tm.MortgageStatusInd3
, tm.MortgageStatusInd4
, tm.MortgageTermAmount1
, tm.MortgageTermAmount2
, tm.MortgageTermAmount3
, tm.MortgageTermAmount4
, tm.MortgageTrustPosition1
, tm.MortgageTrustPosition2
, tm.MortgageTrustPosition3
, tm.MortgageTrustPosition4
, tm.MortgageEquityLoanInd1
, tm.MortgageEquityLoanInd2
, tm.MortgageEquityLoanInd3
, tm.MortgageEquityLoanInd4
, tm.MortgagePrivatePartyLoanInd1
, tm.MortgagePrivatePartyLoanInd2
, tm.MortgagePrivatePartyLoanInd3
, tm.MortgagePrivatePartyLoanInd4
, tm.MortgageRefinanceLoanInd1
, tm.MortgageRefinanceLoanInd2
, tm.MortgageRefinanceLoanInd3
, tm.MortgageRefinanceLoanInd4
, tm.MortgageMortgageEquityLoanType1
, tm.MortgageMortgageEquityLoanType2
, tm.MortgageMortgageEquityLoanType3
, tm.MortgageMortgageEquityLoanType4
FROM (
SELECT TOP (999999999999)
t.BatchDt
, t.BatchSeq
, t.PclId
, t.SalePosition
, t.BeneficiaryPurchasedInd
, t.SecDCArmsLengthCashInd AS SaleArmsLengthCashInd
, t.SecDCArmsLengthMortgageInd AS SaleArmsLengthMortgageInd
, t.SecDCInterrelatedInd AS SaleInterrelatedInd
, t.SecDCInvestorPurchaseInd AS SaleInvestorPurchaseInd
, t.SecDCNewConstructionInd AS SaleNewConstructionInd
, t.SecDCRealEstateOwnedInd AS SaleRealEstateOwnedInd
, t.SecDCRealEstateOwnedSaleInd AS SaleRealEstateOwnedSaleInd
, t.SecDCResaleInd AS SaleResaleInd
, t.SecDCResModelInd AS SaleResModelInd
, t.SecDCShortSaleInd AS SaleShortSaleInd
, t.MultiSplitCd AS MultipleSplitCode
, t.NominalInd AS NominalIndicator
, CASE
WHEN SalePosition = 1 THEN t.SaleDt
END AS SaleDateInt
, CAST (t.SalePriceAmt AS bigint) AS SalesPriceAmount
, CASE
WHEN SalePosition = 1 THEN t.RecordingDt
END AS SaleRecordedDateInt
, CAST (ASCII (RIGHT (t.TransTyp, 1)) AS tinyint) AS TransactionType
, CAST (CASE
WHEN BatchSeq = -1 THEN CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000))
END AS varchar (8000)) AS TransFiller1
, CAST (CASE
WHEN BatchSeq = -2 THEN CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000)) + CAST (LegalText AS char (1000))
END AS varchar (8000)) AS TransFiller2
FROM tTrans.Trans AS t
WHERE t.CntyCd = @CntyCd
AND EditReqdInd IS NULL
AND PclId IS NOT NULL
AND t.PclSeqNbr = 1
ORDER BY
t.BatchDt, t.BatchSeq) AS t
LEFT OUTER MERGE JOIN (
SELECT
tm.BatchDt
, tm.BatchSeq
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageAmount
END) AS MortgageAmount1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageAmount
END) AS MortgageAmount2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageAmount
END) AS MortgageAmount3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageAmount
END) AS MortgageAmount4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageCompanyCd
END) AS MortgageCompanyCd1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageCompanyCd
END) AS MortgageCompanyCd2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageCompanyCd
END) AS MortgageCompanyCd3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageCompanyCd
END) AS MortgageCompanyCd4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageDocumentDate
END) AS MortgageDocumentDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageDocumentDate
END) AS MortgageDocumentDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageDocumentDate
END) AS MortgageDocumentDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageDocumentDate
END) AS MortgageDocumentDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageDate
END) AS MortgageDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageDate
END) AS MortgageDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageDate
END) AS MortgageDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageDate
END) AS MortgageDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageDueDate
END) AS MortgageDueDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageDueDate
END) AS MortgageDueDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageDueDate
END) AS MortgageDueDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageDueDate
END) AS MortgageDueDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateCap
END) AS MortgageInterestRateCap1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateCap
END) AS MortgageInterestRateCap2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateCap
END) AS MortgageInterestRateCap3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateCap
END) AS MortgageInterestRateCap4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateChangeDate
END) AS MortgageInterestRateChangeDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateChangeDate
END) AS MortgageInterestRateChangeDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateChangeDate
END) AS MortgageInterestRateChangeDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateChangeDate
END) AS MortgageInterestRateChangeDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateChangeIntervalMonths
END) AS MortgageInterestRateChangeIntervalMonths1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateChangeIntervalMonths
END) AS MortgageInterestRateChangeIntervalMonths2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateChangeIntervalMonths
END) AS MortgageInterestRateChangeIntervalMonths3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateChangeIntervalMonths
END) AS MortgageInterestRateChangeIntervalMonths4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateChangePercentage
END) AS MortgageInterestRateChangePercentage1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateChangePercentage
END) AS MortgageInterestRateChangePercentage2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateChangePercentage
END) AS MortgageInterestRateChangePercentage3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateChangePercentage
END) AS MortgageInterestRateChangePercentage4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateChangePercentageLimit
END) AS MortgageInterestRateChangePercentageLimit1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateChangePercentageLimit
END) AS MortgageInterestRateChangePercentageLimit2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateChangePercentageLimit
END) AS MortgageInterestRateChangePercentageLimit3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateChangePercentageLimit
END) AS MortgageInterestRateChangePercentageLimit4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRateIndexType
END) AS MortgageInterestRateIndexType1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRateIndexType
END) AS MortgageInterestRateIndexType2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRateIndexType
END) AS MortgageInterestRateIndexType3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRateIndexType
END) AS MortgageInterestRateIndexType4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRatePercentage
END) AS MortgageInterestRatePercentage1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRatePercentage
END) AS MortgageInterestRatePercentage2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRatePercentage
END) AS MortgageInterestRatePercentage3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRatePercentage
END) AS MortgageInterestRatePercentage4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageInterestRatePercentMaximum
END) AS MortgageInterestRatePercentMaximum1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageInterestRatePercentMaximum
END) AS MortgageInterestRatePercentMaximum2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageInterestRatePercentMaximum
END) AS MortgageInterestRatePercentMaximum3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageInterestRatePercentMaximum
END) AS MortgageInterestRatePercentMaximum4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageRateType
END) AS MortgageRateType1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageRateType
END) AS MortgageRateType2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageRateType
END) AS MortgageRateType3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageRateType
END) AS MortgageRateType4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageLoanTypeCode
END) AS MortgageLoanTypeCode1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageLoanTypeCode
END) AS MortgageLoanTypeCode2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageLoanTypeCode
END) AS MortgageLoanTypeCode3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageLoanTypeCode
END) AS MortgageLoanTypeCode4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageModifiedDueDate
END) AS MortgageModifiedDueDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageModifiedDueDate
END) AS MortgageModifiedDueDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageModifiedDueDate
END) AS MortgageModifiedDueDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageModifiedDueDate
END) AS MortgageModifiedDueDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN CAST (tm.MostRecentJR AS tinyint)
END) AS MostRecentJR1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN CAST (tm.MostRecentJR AS tinyint)
END) AS MostRecentJR2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN CAST (tm.MostRecentJR AS tinyint)
END) AS MostRecentJR3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN CAST (tm.MostRecentJR AS tinyint)
END) AS MostRecentJR4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN CAST (tm.MostRecentFirst AS tinyint)
END) AS MostRecentFirst1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN CAST (tm.MostRecentFirst AS tinyint)
END) AS MostRecentFirst2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN CAST (tm.MostRecentFirst AS tinyint)
END) AS MostRecentFirst3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN CAST (tm.MostRecentFirst AS tinyint)
END) AS MostRecentFirst4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageLoanPurpose
END) AS MortgageLoanPurpose1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageLoanPurpose
END) AS MortgageLoanPurpose2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageLoanPurpose
END) AS MortgageLoanPurpose3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageLoanPurpose
END) AS MortgageLoanPurpose4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageRecordingDate
END) AS MortgageRecordingDate1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageRecordingDate
END) AS MortgageRecordingDate2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageRecordingDate
END) AS MortgageRecordingDate3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageRecordingDate
END) AS MortgageRecordingDate4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageStatusInd
END) AS MortgageStatusInd1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageStatusInd
END) AS MortgageStatusInd2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageStatusInd
END) AS MortgageStatusInd3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageStatusInd
END) AS MortgageStatusInd4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageTermAmount
END) AS MortgageTermAmount1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageTermAmount
END) AS MortgageTermAmount2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageTermAmount
END) AS MortgageTermAmount3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageTermAmount
END) AS MortgageTermAmount4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageTrustPosition
END) AS MortgageTrustPosition1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageTrustPosition
END) AS MortgageTrustPosition2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageTrustPosition
END) AS MortgageTrustPosition3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageTrustPosition
END) AS MortgageTrustPosition4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN CAST (tm.MortgageEquityLoanInd AS tinyint)
END) AS MortgageEquityLoanInd1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN CAST (tm.MortgageEquityLoanInd AS tinyint)
END) AS MortgageEquityLoanInd2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN CAST (tm.MortgageEquityLoanInd AS tinyint)
END) AS MortgageEquityLoanInd3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN CAST (tm.MortgageEquityLoanInd AS tinyint)
END) AS MortgageEquityLoanInd4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN CAST (tm.MortgagePrivatePartyLoanInd AS tinyint)
END) AS MortgagePrivatePartyLoanInd1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN CAST (tm.MortgagePrivatePartyLoanInd AS tinyint)
END) AS MortgagePrivatePartyLoanInd2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN CAST (tm.MortgagePrivatePartyLoanInd AS tinyint)
END) AS MortgagePrivatePartyLoanInd3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN CAST (tm.MortgagePrivatePartyLoanInd AS tinyint)
END) AS MortgagePrivatePartyLoanInd4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN CAST (tm.MortgageRefinanceLoanInd AS tinyint)
END) AS MortgageRefinanceLoanInd1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN CAST (tm.MortgageRefinanceLoanInd AS tinyint)
END) AS MortgageRefinanceLoanInd2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN CAST (tm.MortgageRefinanceLoanInd AS tinyint)
END) AS MortgageRefinanceLoanInd3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN CAST (tm.MortgageRefinanceLoanInd AS tinyint)
END) AS MortgageRefinanceLoanInd4
, MAX (CASE
WHEN MtgLienPosition = 1 THEN tm.MortgageEquityLoanType
END) AS MortgageMortgageEquityLoanType1
, MAX (CASE
WHEN MtgLienPosition = 2 THEN tm.MortgageEquityLoanType
END) AS MortgageMortgageEquityLoanType2
, MAX (CASE
WHEN MtgLienPosition = 3 THEN tm.MortgageEquityLoanType
END) AS MortgageMortgageEquityLoanType3
, MAX (CASE
WHEN MtgLienPosition = 4 THEN tm.MortgageEquityLoanType
END) AS MortgageMortgageEquityLoanType4
, MAX (MortgageFiller1) AS MortgageFiller1
, MAX (MortgageFiller2) AS MortgageFiller2
FROM (
SELECT TOP (999999999999)
tm.BatchDt
, tm.BatchSeq
, tm.MtgLienPosition
, CAST (ASCII (RIGHT (tm.MtgSubordTyp, 1)) AS tinyint) AS MortgageEquityLoanType
, CAST (CASE
WHEN tm.MtgAmt > 0 THEN tm.MtgAmt
END AS bigint) AS MortgageAmount
, tm.MtgCompanyCd AS MortgageCompanyCd
, tm.MtgDt AS MortgageDate
, tm.MtgDocYYDt * 10000 + 101 AS MortgageDocumentDate
, tm.MtgDueDt AS MortgageDueDate
, tm.MtgModDueDt AS MortgageModifiedDueDate
, tm.SecMCEquityLoanInd AS MortgageEquityLoanInd
, CAST (tm.MtgIntRateCap AS smallmoney) AS MortgageInterestRateCap
, tm.MtgIntRateChangeDt AS MortgageInterestRateChangeDate
, CAST (CASE
WHEN tm.MtgIntRateChangeFreq = 'Y' THEN NULLIF (tm.MtgIntRateChangeIntvl, 0) * 12
WHEN tm.MtgIntRateChangeFreq = 'M'
OR tm.MtgIntRateChangeFreq IS NULL THEN NULLIF (tm.MtgIntRateChangeIntvl, 0)
WHEN tm.MtgIntRateChangeFreq = 'Q' THEN NULLIF (tm.MtgIntRateChangeIntvl, 0) * 3
WHEN tm.MtgIntRateChangeFreq = 'W'
AND tm.MtgIntRateChangeIntvl >= 4 THEN tm.MtgIntRateChangeIntvl / 4
END AS smallint) AS MortgageInterestRateChangeIntervalMonths
, CAST (tm.MtgIntRateChangePct AS smallmoney) AS MortgageInterestRateChangePercentage
, CAST (tm.MtgIntRateChangePctLimit AS smallmoney) AS MortgageInterestRateChangePercentageLimit
, tm.MtgIntRateIndexTyp AS MortgageInterestRateIndexType
, CAST (tm.MtgIntRatePct AS smallmoney) AS MortgageInterestRatePercentage
, CAST (tm.MtgIntRatePctMax AS smallmoney) AS MortgageInterestRatePercentMaximum
, tm.MtgLienPosition AS MortgageLienPosition
, tm.MtgLoanTypCd AS MortgageLoanTypeCode
, CAST (CASE tm.MtgIntRateTyp
WHEN 'ADJ' THEN 1
WHEN 'BAL' THEN 2
WHEN 'FIX' THEN 3
WHEN '' THEN 4
END AS tinyint) AS MortgageRateType
, tm.SecMCPrivatePartyLoanInd AS MortgagePrivatePartyLoanInd
, tm.MtgPurposeTyp AS MortgageLoanPurpose
, tm.MtgRecordingDt AS MortgageRecordingDate
, tm.SecmcRefinanceLoanInd AS MortgageRefinanceLoanInd
, tm.MtgStatusInd AS MortgageStatusInd
, TRY_CONVERT (int, CASE
WHEN tm.MtgTermCd = 'Y'
AND tm.MtgTermAmt > 0 THEN tm.MtgTermAmt * 12
WHEN tm.MtgTermCd = 'M'
AND tm.MtgTermAmt > 0 THEN tm.MtgTermAmt
END) AS MortgageTermAmount
, tm.MtgTypInd AS MortgageTrustPosition
, CAST (CASE
WHEN MtgTypInd IN ('P', 'R')
AND tm.MtgMostRecentInd = 'Y' THEN 1
END AS bit) AS MostRecentFirst
, CAST (CASE
WHEN MtgTypInd = 'J'
AND tm.MtgMostRecentInd = 'Y' THEN 1
END AS bit) AS MostRecentJR
, CAST (CASE
WHEN BatchSeq = -1 THEN CAST (MtgCommentTxt1 AS char (3000)) + CAST (MtgCommentTxt2 AS char (3000)) + CAST (MtgCommentTxt3 AS char (2000))
END AS varchar (8000)) AS MortgageFiller1
, CAST (CASE
WHEN BatchSeq = -2 THEN CAST (MtgCommentTxt3 AS char (3000)) + CAST (MtgCommentTxt2 AS char (3000)) + CAST (MtgCommentTxt1 AS char (2000))
END AS varchar (8000)) AS MortgageFiller2
FROM tTrans.TransMortgage AS tm
WHERE tm.CntyCd = @CntyCd
AND MtgLienPosition BETWEEN 1 AND 4
ORDER BY
tm.BatchDt, tm.BatchSeq, tm.MtgSeq) AS tm
GROUP BY
tm.BatchDt
, tm.BatchSeq) AS tm
ON t.BatchDt = tm.BatchDt
AND t.BatchSeq = tm.BatchSeq
WHERE (t.SalePosition = 1
OR tm.BatchDt IS NOT NULL)
ORDER BY
PclId) AS tm3
GROUP BY
PclId) AS tmt
ON tmt.PclId = pp.PclId
GO
gvarol@corelogic.com
 
Posts: 63
Joined: Wed Mar 09, 2011 9:43 pm
Location: USA

Script fails to format

Postby gvarol@corelogic.com » Fri Aug 09, 2013 3:32 pm

I have just installed version 6.0.0.324 and the format still fails.

I put the script to skydrive, I think it got cut off in the original post.

https://skydrive.live.com/redir?resid=2 ... MWMeFA5tQA
gvarol@corelogic.com
 
Posts: 63
Joined: Wed Mar 09, 2011 9:43 pm
Location: USA

Postby Aaron Law » Fri Aug 09, 2013 5:00 pm

It looks like SQL Prompt is tripping up on this bit of code:
WITH (FORCESEEK (1 (CntyCd, CdVal)))

As a work around you can delete that bit of SQL, format everything and then reinsert it.
We are planning on improving some of the prompt parser over the next few weeks so i'll make sure adding support for this is on our list.

Thanks,
Aaron.
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Re:

Postby gvarol@corelogic.com » Fri Aug 09, 2013 5:33 pm

Thank you :-)

Aaron Law wrote:It looks like SQL Prompt is tripping up on this bit of code:
WITH (FORCESEEK (1 (CntyCd, CdVal)))

As a work around you can delete that bit of SQL, format everything and then reinsert it.
We are planning on improving some of the prompt parser over the next few weeks so i'll make sure adding support for this is on our list.

Thanks,
Aaron.
gvarol@corelogic.com
 
Posts: 63
Joined: Wed Mar 09, 2011 9:43 pm
Location: USA


Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests