asked on

Need help with sql statement. Cannot find

Greetings,
I have the following query:

truncate table propertyparameters;
	insert into PropertyParameters
	select
		SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID,
		0 as Score,
		0 as Score1,
		0 as Score2,
		0 as Score3,
		0 as Score4,
		0 as Score5,
		case
			when LEFT(PurchaseDate,4) = '0000' then 0
			else year(getdate()) - CAST(LEFT(PurchaseDate,4)as INT) 
			end as NumYearsOwned,
		0 as PrevNumYearsOwned,
		case
			when LEN(LoanDate2) > 2 then year(getdate()) - CAST(LEFT(LoanDate2,4)as INT)
			when LEN(LoanDate2) < 2 then 0
			else  year(getdate()) - CAST(LEFT(LoanDate1,4)as INT)
			end as NumYearsLastLoan,
		case when ISNUMERIC(AvailHomeEquity) = 1 then cast(AvailHomeEquity as float) else 0 end AS HomeEquity,
		case
			when ISNULL(HomeMarketValue,0) = 0 then 0
			when ISNUMERIC(HomeMarketValue) = 0 then 0
			when ISNUMERIC(TotalLoans) = 0 then 0
			when HomeMarketValue = 0 then 0
			else ISNULL(TotalLoans,0)/cast(HomeMarketValue as float)
			end as LoanToValue,
		case when ISNUMERIC(HomeMarketValue) = 1 then HomeMarketValue else 0 end AS MarketValueHome,
		case when ISNUMERIC(TotalLoans) = 1 then TotalLoans else 0 end AS TotalLoans,
		case
			when TrustOwned = 1 then 'T'
			else OwnerType
			end as OwnerType,
		PropertyTypeDetail as PropertyType,
		case
			when ISNUMERIC(EconStabInd) = 0 then 0
			else cast(EconStabInd as INT)
			end as OwnerEconStab,
		case
			when IncomeRange = 'A' then 10
			when IncomeRange = 'B' then 11
			when IncomeRange = 'C' then 12
			when IncomeRange = 'D' then 13
			when isnumeric(IncomeRange) = 1 then IncomeRange
			else 0
			end as OwnerIncome,
		case
			when ISNUMERIC(NetworthRange) = 1 then NetworthRange
			else 0
			end as OwnerNetworth,
		Gender as OwnerGender,
		case
			when MaritalStatus = 'M' then 'Y'
			when MaritalStatus = 'A' then 'Y'
			else 'N'
			end as OwnerMarriage,
		case
			when ChildF1617 = 1 then 'Y'
			when ChildM1617 = 1 then 'Y'
			when ChildU1617 = 1 then 'Y'
			else 'N'
			end as OwnerCollegeKids,
		SiteZip as ZipCode,
		case
			when MailOrderResponder = 'R' then 'Y'
			when MailOrderBuyer = 'B' then 'Y'
			else 'N'
			end as MailOrderResponder,
		Occupation as OwnerOccupation,
		1 as Updated,
		GETDATE() as DateStamp,
		PurchaseDate,
		0 as PurchasePrice,
		MONTH(GETDATE()) as Month,
		YEAR(GETDATE()) as Year,
		ltrim(rtrim(SiteHouseNumber)) as AddNum,
		ltrim(rtrim(SiteDirection)) as AddDir,
		ltrim(rtrim(SiteStreet)) as AddStreet,
		ltrim(rtrim(SiteUnitNum)) as AddUnitNum,
		ltrim(rtrim(SiteCity)) as AddCity,
		ltrim(rtrim(SiteState)) as AddState,
		ltrim(rtrim(SiteZip)) as AddZip,
		ltrim(rtrim(SiteZip4)) as AddZip4,
		ltrim(rtrim(SiteLat)) as AddLat,
		ltrim(rtrim(SiteLon)) as AddLon,
		case
			when ISNUMERIC(NumBeds) = 1 then CAST(NumBeds as INT)
			else 0
			end as NumBeds,
		0 as NumBath,
		case
			when PurchaseDate = '0' then NULL
			when right(PurchaseDate, 4) = '0000' then convert(date, left(PurchaseDate,4) + '1231', 112)
			when RIGHT(PurchaseDate, 2) = '00' then DATEADD(day,-1,DATEADD(month,cast(left(RIGHT(Purchasedate,4),2) AS INT),DATEADD(year,cast(LEFT(Purchasedate, 4) AS int)-1900,0)))
			else convert(date, ltrim(rtrim(cast(PurchaseDate as varchar(50)))), 112)
			end as SaleDate,
		0 as IsListed,
		0 as ID,
		NULL as GEOID,
		Phone,
		OwnerFName,
		OwnerMName,
		OwnerLName,
		case when ISNUMERIC(SiteLon) = 1 and ISNUMERIC(SiteLat) = 1 then
			geography::STPointFromText('POINT(' + SiteLon + ' ' + SiteLat + ')', 4326)
			else null
			end as GeoLocation,
		NULL as EmailChecked
	from dbo.PropertyNew

Open in new window

My problem is that I am receiving this error:
Conversion failed when converting the varchar value 'B' to data type int. The problem is that the source table is 75M records strong. and I can't find the record that could cause the problem because the error is not telling me where to look, ie. what column.Can anyone look at the query and see if they see anything glaring wrong here?

Here's my Source Table:

CREATE TABLE [dbo].[PropertyNew](
	[Zip] [varchar](50) NULL,
	[Zip4] [varchar](50) NULL,
	[OwnerTitle] [varchar](50) NULL,
	[OwnerFName] [varchar](50) NULL,
	[OwnerMName] [varchar](50) NULL,
	[OwnerLName] [varchar](50) NULL,
	[MailStrNum10] [varchar](50) NULL,
	[MailStrDir] [varchar](50) NULL,
	[MailStr] [varchar](50) NULL,
	[MailCity] [varchar](50) NULL,
	[MailState] [varchar](50) NULL,
	[MailSecNum] [varchar](50) NULL,
	[AddressCat] [varchar](50) NULL,
	[NameCat] [varchar](50) NULL,
	[AddressLines] [varchar](50) NULL,
	[LineOfTravel] [varchar](50) NULL,
	[MailRecType] [varchar](50) NULL,
	[CRCCode] [varchar](50) NULL,
	[SeasonalMail] [varchar](50) NULL,
	[MailFIPSState] [varchar](50) NULL,
	[PurchaseDate] [varchar](50) NULL,
	[LengthResidence] [varchar](50) NULL,
	[LoanDate1] [varchar](50) NULL,
	[LoanDate2] [varchar](50) NULL,
	[SpouseFName] [varchar](50) NULL,
	[SpouseMName] [varchar](50) NULL,
	[SpouseLName] [varchar](50) NULL,
	[SpouseGender] [varchar](50) NULL,
	[SpouseTitle] [varchar](50) NULL,
	[SiteHouseNumber] [varchar](50) NULL,
	[SiteDirection] [varchar](50) NULL,
	[SiteStreet] [varchar](50) NULL,
	[SiteUnitNum] [varchar](50) NULL,
	[SiteCity] [varchar](50) NULL,
	[SiteState] [varchar](50) NULL,
	[SiteZip] [varchar](50) NULL,
	[SiteZip4] [varchar](50) NULL,
	[SiteLat] [varchar](50) NULL,
	[SiteLon] [varchar](50) NULL,
	[AvailHomeEquity] [varchar](50) NULL,
	[AvailHomeEquityRange] [varchar](50) NULL,
	[Fireplace] [varchar](50) NULL,
	[Garage] [varchar](50) NULL,
	[HeatCool] [varchar](50) NULL,
	[FirstLoanIntRateType] [varchar](50) NULL,
	[SecLoanIntRateType] [varchar](50) NULL,
	[LendableHomeEquity] [varchar](50) NULL,
	[LendableHomeEquityRange] [varchar](50) NULL,
	[FirstLoanLenderName] [varchar](50) NULL,
	[FirstLoanLenderType] [varchar](50) NULL,
	[LoanToValue] [varchar](50) NULL,
	[LoanToValueRange] [varchar](50) NULL,
	[FirstLoanAmount] [varchar](50) NULL,
	[SecondLoanAmount] [varchar](50) NULL,
	[FirstLoanAmountRange] [varchar](50) NULL,
	[SecLoanAmountRange] [varchar](50) NULL,
	[LotSize] [varchar](50) NULL,
	[LotSizeRange] [varchar](50) NULL,
	[HomeMarketValue] [varchar](50) NULL,
	[HomeMarketValueRange] [varchar](50) NULL,
	[NumBeds] [varchar](50) NULL,
	[NumberUnitsRange] [varchar](50) NULL,
	[TrustOwned] [varchar](50) NULL,
	[OwnerType] [varchar](50) NULL,
	[OwnerTypeDetail] [varchar](50) NULL,
	[Pool] [varchar](50) NULL,
	[PropertyType] [varchar](50) NULL,
	[PropertyTypeDetail] [varchar](50) NULL,
	[HomeSizeSQFT] [varchar](50) NULL,
	[HomeSizeSQFTRange] [varchar](50) NULL,
	[TotalLoans] [varchar](50) NULL,
	[Total_Loan_Range] [varchar](50) NULL,
	[HomePurchaseAmtRange] [varchar](50) NULL,
	[YearHomeBuilt] [varchar](50) NULL,
	[ChildM02] [varchar](50) NULL,
	[ChildF02] [varchar](50) NULL,
	[ChildU02] [varchar](50) NULL,
	[ChildM35] [varchar](50) NULL,
	[ChildF35] [varchar](50) NULL,
	[ChildU35] [varchar](50) NULL,
	[ChildM610] [varchar](50) NULL,
	[ChildF610] [varchar](50) NULL,
	[ChildU610] [varchar](50) NULL,
	[ChildM1115] [varchar](50) NULL,
	[ChildF1115] [varchar](50) NULL,
	[ChildU1115] [varchar](50) NULL,
	[ChildM1617] [varchar](50) NULL,
	[ChildF1617] [varchar](50) NULL,
	[ChildU1617] [varchar](50) NULL,
	[DwellingType] [varchar](50) NULL,
	[MaritalStatus] [varchar](50) NULL,
	[AssessedValue] [varchar](50) NULL,
	[AssessedValueRange] [varchar](50) NULL,
	[InvestorPropCounts] [varchar](50) NULL,
	[EconStabInd] [varchar](50) NULL,
	[PurchaseMonth] [varchar](50) NULL,
	[CBSA] [varchar](50) NULL,
	[Gender] [varchar](50) NULL,
	[NetworthRange] [varchar](50) NULL,
	[ParcelID] [varchar](50) NULL,
	[Phone] [varchar](50) NULL,
	[Occupation] [varchar](50) NULL,
	[MailOrderResponder] [varchar](50) NULL,
	[IncomeRange] [varchar](50) NULL,
	[MailOrderBuyer] [varchar](50) NULL,
	[InfobasePosMatch] [varchar](50) NULL,
	[NumSources] [varchar](50) NULL,
	[OverallMath] [varchar](50) NULL,
	[SuppressionMail] [varchar](50) NULL
) ON [PRIMARY]

Open in new window

and here's my target table:

CREATE TABLE [dbo].[PropertyParameters](

	[PropertyID] [varchar](50) NULL,

	[ParcelID] [varchar](50) NULL,

	[Score] [real] NULL,

	[Score1] [real] NULL,

	[Score2] [real] NULL,

	[NumYearsOwned] [int] NULL,

	[PrevNumYearsOwned] [int] NULL,

	[NumYearsLastLoan] [int] NULL,

	[HomeEquity] [real] NOT NULL,

	[LoanToValue] [real] NULL,

	[MarketValueHome] [real] NULL,

	[TotalLoans] [real] NULL,

	[OwnerType] [varchar](50) NULL,

	[PropertyType] [varchar](50) NOT NULL,

	[OwnerEconStab] [int] NULL,

	[OwnerIncome] [int] NULL,

	[OwnerNetworth] [int] NULL,

	[OwnerGender] [varchar](50) NULL,

	[OwnerMarriage] [varchar](50) NULL,

	[OwnerCollegeKids] [varchar](50) NULL,

	[ZipCode] [varchar](50) NULL,

	[MailOrderResponder] [varchar](50) NULL,

	[OwnerOccupation] [varchar](50) NULL,

	[Updated] [int] NULL,

	[DateStamp] [smalldatetime] NULL,

	[PurchaseDate] [varchar](50) NULL,

	[PurchasePrice] [real] NULL,

	[Month] [varchar](50) NULL,

	[Year] [varchar](50) NULL,

	[AddNum] [varchar](50) NULL,

	[AddDir] [varchar](50) NULL,

	[AddStreet] [varchar](50) NULL,

	[AddUnitNum] [varchar](50) NULL,

	[AddCity] [varchar](50) NULL,

	[AddState] [varchar](50) NULL,

	[AddZip] [varchar](50) NULL,

	[AddZip4] [varchar](50) NULL,

	[AddLat] [varchar](50) NULL,

	[AddLon] [varchar](50) NULL,

	[NumBeds] [int] NULL,

	[NumBath] [int] NULL,

	[SaleDate] [date] NULL,

	[IsListed] [int] NULL,

	[ID] [int] NULL,

	[GEOID] [varchar](50) NULL,

	[Phone] [varchar](50) NULL,

	[OwnerFName] [varchar](50) NULL,

	[OwnerMName] [varchar](50) NULL,

	[OwnerLName] [varchar](50) NULL,

	[GeoLocation] [geography] NULL,

	[EmailChecked] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

Qlemo

Use an explicit field list in INSERT. Always. Naming the columns in SELECT does not ensure exactly those columns are set in INSERT - there is only a positional reference, not by name.
I see you selecting 6 scores, but there are only 3 in the target table.

Evan Cutler

ASKER

Thanks.
There's six scores in the target table. I just used a bad target code in my question.

I updated the insert statement as follows. any other ideas?

	insert into PropertyParameters
	([PropertyID],[ParcelID],[Score],[Score1],[Score2]
     ,[Score3],[Score4],[Score5],[NumYearsOwned],[PrevNumYearsOwned]
     ,[NumYearsLastLoan],[HomeEquity],[LoanToValue],[MarketValueHome],[TotalLoans]
     ,[OwnerType],[PropertyType],[OwnerEconStab],[OwnerIncome],[OwnerNetworth]
     ,[OwnerGender],[OwnerMarriage],[OwnerCollegeKids],[ZipCode],[MailOrderResponder]
     ,[OwnerOccupation],[Updated],[DateStamp],[PurchaseDate],[PurchasePrice]
     ,[Month],[Year],[AddNum],[AddDir],[AddStreet]
     ,[AddUnitNum],[AddCity],[AddState],[AddZip],[AddZip4]
     ,[AddLat],[AddLon],[NumBeds],[NumBath],[SaleDate]
     ,[IsListed],[ID],[GEOID],[Phone],[OwnerFName]
     ,[OwnerMName],[OwnerLName],[GeoLocation],[EmailChecked])
	select
		SiteZip + replace(REPLACE(ParcelID,' ','0'), '-', '0') as PropertyID,
		ParcelID,
		0 as Score,
		0 as Score1,
		0 as Score2,
		0 as Score3,
		0 as Score4,
		0 as Score5,
		case
			when LEFT(PurchaseDate,4) = '0000' then 0
			else year(getdate()) - CAST(LEFT(PurchaseDate,4)as INT) 
			end as NumYearsOwned,
		0 as PrevNumYearsOwned,
		case
			when LEN(LoanDate2) > 2 then year(getdate()) - CAST(LEFT(LoanDate2,4)as INT)
			when LEN(LoanDate2) < 2 then 0
			else  year(getdate()) - CAST(LEFT(LoanDate1,4)as INT)
			end as NumYearsLastLoan,
		case when ISNUMERIC(AvailHomeEquity) = 1 then cast(AvailHomeEquity as float) else 0 end AS HomeEquity,
		case
			when ISNULL(HomeMarketValue,0) = 0 then 0
			when ISNUMERIC(HomeMarketValue) = 0 then 0
			when ISNUMERIC(TotalLoans) = 0 then 0
			when HomeMarketValue = 0 then 0
			else ISNULL(TotalLoans,0)/cast(HomeMarketValue as float)
			end as LoanToValue,
		case when ISNUMERIC(HomeMarketValue) = 1 then HomeMarketValue else 0 end AS MarketValueHome,
		case when ISNUMERIC(TotalLoans) = 1 then TotalLoans else 0 end AS TotalLoans,
		case
			when TrustOwned = 1 then 'T'
			else OwnerType
			end as OwnerType,
		PropertyTypeDetail as PropertyType,
		case
			when ISNUMERIC(EconStabInd) = 0 then 0
			else cast(EconStabInd as INT)
			end as OwnerEconStab,
		case
			when IncomeRange = 'A' then 10
			when IncomeRange = 'B' then 11
			when IncomeRange = 'C' then 12
			when IncomeRange = 'D' then 13
			when isnumeric(IncomeRange) = 1 then IncomeRange
			else 0
			end as OwnerIncome,
		case
			when ISNUMERIC(NetworthRange) = 1 then NetworthRange
			else 0
			end as OwnerNetworth,
		Gender as OwnerGender,
		case
			when MaritalStatus = 'M' then 'Y'
			when MaritalStatus = 'A' then 'Y'
			else 'N'
			end as OwnerMarriage,
		case
			when ChildF1617 = 1 then 'Y'
			when ChildM1617 = 1 then 'Y'
			when ChildU1617 = 1 then 'Y'
			else 'N'
			end as OwnerCollegeKids,
		SiteZip as ZipCode,
		case
			when MailOrderResponder = 'R' then 'Y'
			when MailOrderBuyer = 'B' then 'Y'
			else 'N'
			end as MailOrderResponder,
		Occupation as OwnerOccupation,
		1 as Updated,
		GETDATE() as DateStamp,
		PurchaseDate,
		0 as PurchasePrice,
		MONTH(GETDATE()) as Month,
		YEAR(GETDATE()) as Year,
		ltrim(rtrim(SiteHouseNumber)) as AddNum,
		ltrim(rtrim(SiteDirection)) as AddDir,
		ltrim(rtrim(SiteStreet)) as AddStreet,
		ltrim(rtrim(SiteUnitNum)) as AddUnitNum,
		ltrim(rtrim(SiteCity)) as AddCity,
		ltrim(rtrim(SiteState)) as AddState,
		ltrim(rtrim(SiteZip)) as AddZip,
		ltrim(rtrim(SiteZip4)) as AddZip4,
		ltrim(rtrim(SiteLat)) as AddLat,
		ltrim(rtrim(SiteLon)) as AddLon,
		case
			when ISNUMERIC(NumBeds) = 1 then CAST(NumBeds as INT)
			else 0
			end as NumBeds,
		0 as NumBath,
		case
			when PurchaseDate = '0' then NULL
			when right(PurchaseDate, 4) = '0000' then convert(date, left(PurchaseDate,4) + '1231', 112)
			when RIGHT(PurchaseDate, 2) = '00' then DATEADD(day,-1,DATEADD(month,cast(left(RIGHT(Purchasedate,4),2) AS INT),DATEADD(year,cast(LEFT(Purchasedate, 4) AS int)-1900,0)))
			else convert(date, ltrim(rtrim(cast(PurchaseDate as varchar(50)))), 112)
			end as SaleDate,
		0 as IsListed,
		0 as ID,
		NULL as GEOID,
		Phone,
		OwnerFName,
		OwnerMName,
		OwnerLName,
		case when ISNUMERIC(SiteLon) = 1 and ISNUMERIC(SiteLat) = 1 then
			geography::STPointFromText('POINT(' + SiteLon + ' ' + SiteLat + ')', 4326)
			else null
			end as GeoLocation,
		NULL as EmailChecked
	from dbo.PropertyNew

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Evan Cutler

ASKER

Thanks Guys.

Qlemo

I disagree to Vitor getting points. The very first comment was "Use an explicit field list in INSERT. Always.", and it was me posting. Vitor just repeated that.