我怎么会收到这个错误？ Msg 8117，Level 16，State 1，Line 6 [英] How come I am getting this error? Msg 8117, Level 16, State 1, Line 6

问题描述

嗨



我认为代码是正确的，因为没有更多的红线。但当我运行它时，它会显示以下消息：

Hi

I was thinking the codes were correct because there as no more red lines. But when I run it, it shows this message:

Msg 8117, Level 16, State 1, Line 6<br />
Operand data type varchar is invalid for divide operator

这是我的查询：

Here is my query:

SELECT b.pnNumber
    ,b.loanAmount
    ,b.term
    ,((CAST(SUBSTRING(b.interestRate, PATINDEX('%[^0 ]%', b.interestRate + ' '), LEN(b.interestRate)) AS FLOAT) /100)/12) monthlyInterestRate
    ,POWER(((CAST(SUBSTRING(b.interestRate, PATINDEX('%[^0 ]%', b.interestRate + ' '), LEN(b.interestRate)) AS FLOAT) /100)/12), b.term) SuperScript
    ,CASE WHEN b.interestRate = 0 THEN b.loanAmount/b.term ELSE
        (b.loanAmount*((((CAST(SUBSTRING(b.interestRate, PATINDEX('%[^0 ]%', b.interestRate + ' '), LEN(b.interestRate)) AS FLOAT) /100)/12)*POWER(((CAST(SUBSTRING(b.interestRate, PATINDEX('%[^0 ]%', b.interestRate + ' '), LEN(b.interestRate)) AS FLOAT) /100)/12), b.term))-(POWER(((CAST(SUBSTRING(b.interestRate, PATINDEX('%[^0 ]%', b.interestRate + ' '), LEN(b.interestRate)) AS FLOAT) /100)/12), b.term)-1))) END MonthlyPayment
FROM vw_LoanMas_AcctRel AS b
WHERE b.billingBucket1 = 'U'

感谢您帮助解决我的问题。

Thank you for helping me out in my problem.

推荐答案

b.term的数据类型是什么？我怀疑它的varchar。请检查

What is the data type of b.term? I suspect its varchar. Please check

您的查询包含，例如， /b.term 。如果 term 是 varchar ，那么你必须将它强制转换为数字（如果有意义）才能使用它作为除法操作数。

Your query contains, for instance, /b.term. If term is a varchar then you have to cast it to a number (if it makes sense) in order to use it as divide operand.

你必须检查无论你在哪里使用分子和分母都必须是相同的数据类型即数字数据类型。



另外，请确保表中是否存在数据不应包含varchar数据。



和无论上面提出什么建议，如使用CAST / CONVERT，如果需要，请使用它。







希望这个会帮助你。



干杯

Hi,

You have to check out that wherever you are using numerator and denominator all must be same data type i.e. Numeric data type.

Also, kindly make sure whether data exists in your table should not contain varchar data.

And whatever suggestion made above like use of CAST / CONVERT, if required use it.



Hope this will help you.

Cheers

这篇关于我怎么会收到这个错误？ Msg 8117，Level 16，State 1，Line 6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！