Red Gate forums :: View topic - I have version 6.0.0.270 and below script fails to format. P
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 5
SQL Prompt 5 forum

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

Search in SQL Prompt 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
gvarol@corelogic.com



Joined: 09 Mar 2011
Posts: 63
Location: USA

PostPosted: Fri Aug 09, 2013 2:58 pm    Post subject: I have version 6.0.0.270 and below script fails to format. P Reply with quote

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 Mortgage
Back to top
View user's profile Send private message
gvarol@corelogic.com



Joined: 09 Mar 2011
Posts: 63
Location: USA

PostPosted: Fri Aug 09, 2013 3:32 pm    Post subject: Script fails to format Reply with quote

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=2971E3767783F89B!6138&authkey=!AMyOZMWMeFA5tQA
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 248
Location: Red Gate Software

PostPosted: Fri Aug 09, 2013 5:00 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
gvarol@corelogic.com



Joined: 09 Mar 2011
Posts: 63
Location: USA

PostPosted: Fri Aug 09, 2013 5:33 pm    Post subject: Re: Reply with quote

Thank you Smile

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group