September 16, 2010 at 5:36 pm
Hi folks:
Forgive me for my lack of SQL knowledge as I'm thinking this is a fairly easy fix but this dummy here doesn't have a clue.
Anyway, in the code below, I'm getting the error message that's in my Subject line. So I did a process of elimination and just excluded certain values until the job would run.....and it did after I excluded all of the values within the case statements.
So...now I know what's causing the error message(I'm guessing it's some sort of format issue from the table that contains the values within the case statement)....but I don't know what's wrong or how to fix it.
Can you please take a look and advise me....super thx!
select cast(convert(char(6),a.Shaw_Upload_Date, 112) as int) as 'Funding_Date',
a.application_key as 'App ID',
d.Employee_Full_Name as 'Credit Analyst',
--a.Lob_Name as 'Program',
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then 'PTI'
when c.Total_Income_Amt < b.Min_Income_Amt then 'Min Income'
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then 'Min Cash'
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then 'Max Vehicle Year'
when c.Term_Nbr > b.Max_Term_Nbr then 'Term'
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then 'Max Payment'
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then 'Max Mileage'
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then 'LTV'
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then 'DTI'
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then 'Advance' else 'No Exception' end as ExceptionType,
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then b.Max_PTI
when c.Total_Income_Amt < b.Min_Income_Amt then b.Min_Income_Amt
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then b.Min_Cash_Down_Amt
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then Vehicle_Year_Nbr
when c.Term_Nbr > b.Max_Term_Nbr then b.Max_Term_Nbr
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then b.Max_Payment_Amt
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then b.Max_Mileage_Nbr
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then b.Max_LTV_Pct
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then b.Max_DTI
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then b.Max_Net_Advance_Pct else 'NULL' end as Tier_Rule,
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 - b.Max_PTI
when c.Total_Income_Amt < b.Min_Income_Amt then c.Total_Income_Amt - b.Min_Income_Amt
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then c.Cash_Down_Amt - b.Min_Cash_Down_Amt
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr
when c.Term_Nbr > b.Max_Term_Nbr then c.Term_Nbr - b.Max_Term_Nbr
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then c.Monthly_Payment_Amt - b.Max_Payment_Amt
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then a.Vehicle_Mileage_Nbr - b.Max_Mileage_Nbr
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 - b.Max_LTV_Pct
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then ((c.total_debt_amt) / (c.total_income_amt)) * 100 - b.Max_DTI
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 - b.Max_Net_Advance_Pct else 'NULL' end as Exception_Amt
from DM_Originations.dbo.Vw_Application_Dim as a
join DM_Originations.dbo.Vw_Purchase_Funding_Fact as c
on a.application_key = c.application_key
join dm_originations.dbo.vw_Credit_Policy_By_Tier_OCS as b
on a.Scoring_Policy_Version_Id = b.Scoring_Policy_Version_Id
and a.Credit_Policy_Group_Id = b.Credit_Policy_Group_Id
and a.Pricing_Tier_key = b.Pricing_Tier_id
join DM_Originations.dbo.Vw_Credit_Analyst_Dim as d
on a.Final_Credit_Analyst_Key = d.Employee_Key
where a.Shaw_Upload_Date >= '20100901'
and a.Finance_Company_Id = 1
and a.Product_Id = 1
and a.Carmax_Ind = 'N'
and d.Employee_Full_Name like 'Corley Cowan'
group by cast(convert(char(6),a.Shaw_Upload_Date, 112) as int),
a.application_key, d.Employee_Full_Name,
--a.Lob_Name,
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then 'PTI'
when c.Total_Income_Amt < b.Min_Income_Amt then 'Min Income'
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then 'Min Cash'
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then 'Max Vehicle Year'
when c.Term_Nbr > b.Max_Term_Nbr then 'Term'
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then 'Max Payment'
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then 'Max Mileage'
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then 'LTV'
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then 'DTI'
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then 'Advance' else 'No Exception' end,
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then b.Max_PTI
when c.Total_Income_Amt < b.Min_Income_Amt then b.Min_Income_Amt
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then b.Min_Cash_Down_Amt
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then Vehicle_Year_Nbr
when c.Term_Nbr > b.Max_Term_Nbr then b.Max_Term_Nbr
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then b.Max_Payment_Amt
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then b.Max_Mileage_Nbr
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then b.Max_LTV_Pct
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then b.Max_DTI
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then b.Max_Net_Advance_Pct else 'NULL' end,
case when ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 > b.Max_PTI then ((c.Monthly_Payment_Amt) / (c.total_income_amt)) * 100 - b.Max_PTI
when c.Total_Income_Amt < b.Min_Income_Amt then c.Total_Income_Amt - b.Min_Income_Amt
when c.Cash_Down_Amt < b.Min_Cash_Down_Amt then c.Cash_Down_Amt - b.Min_Cash_Down_Amt
when year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr > b.Max_Vehicle_Age_Nbr then year(a.Shaw_Upload_Date) - Vehicle_Year_Nbr
when c.Term_Nbr > b.Max_Term_Nbr then c.Term_Nbr - b.Max_Term_Nbr
when c.Monthly_Payment_Amt > b.Max_Payment_Amt then c.Monthly_Payment_Amt - b.Max_Payment_Amt
when a.Vehicle_Mileage_Nbr > b.Max_Mileage_Nbr then a.Vehicle_Mileage_Nbr - b.Max_Mileage_Nbr
when ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 > b.Max_LTV_Pct then ((c.Financed_Amt) / case when c.Book_Value_Amt = 0 or c.Book_Value_Amt is null then null else (c.Book_Value_Amt) end) * 100 - b.Max_LTV_Pct
when ((c.total_debt_amt) / (c.total_income_amt)) * 100 > b.Max_DTI then ((c.total_debt_amt) / (c.total_income_amt)) * 100 - b.Max_DTI
when ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 > b.Max_Net_Advance_Pct then ((c.Total_Sales_Amt - c.Total_Down_Amt) / (c.Book_Value_Amt)) * 100 - b.Max_Net_Advance_Pct else 'NULL' end
order by 1,3
September 16, 2010 at 5:43 pm
Drix,
More then happy to help you, but need a little more information from you, since we're going to need to actually test that code. It's too dense to spot check it for the fail point. In my sig you'll find a walkthrough on the best way to provide us the table definitions and some sample data.
From the 10,000 foot view, though, there's a lot of math going on in them-there case statements. At a guess, you've got a field that looks numeric in the data, but is actually stored as char/varchar. And in said field, you've got a value that can't convert (think 1 000, which can't become 1000) either due to typo, or bad data.
Finding it's gonna be interesting. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 16, 2010 at 6:32 pm
Have you tried using the ISNUMERIC function.
"WHERE ISNUMERIC(YourColumn) = 1"
The function isn't perfect as characters that are used for money ($,.) are counted as valid, but it'll get you most of the way there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply