将5/1转换为int时，转换faild下面的查询出错 [英] Error on query below conversion faild when converting 5/1 to int

问题描述

problem

error on query below conversion fails when converting 5/1 to int

<pre lang="CSS">DataSample

SpecialCode   expr1   EXPR2
000151/1      000151    1
000151/2      000151    2
000151/3      000151    3
006495/1      006495    1
006495/2      006495    2

以上数据没有任何问题，但我让它显示什么查询做什么



但是上面的错误可能发生在expr1上的数据上，而EXPR2发生转换为整数时



那么



1-我在查询时修改了什么以避免显示此错误因为更多使用写特殊代码错误并保存错误？



2 - 为什么会出现此错误以及出现此错误的情况？



什么我试过了：

data above not have any problem but i make it to show what query doing

but error above may be occurred on data on expr1,and EXPR2 when conversion to integer

So that

1- what i modify on query to avoid this error display because more use write special code wrong and save it wrong ?

2-why this error happen and cases that do this error ?

What I have tried:

Query sql server 2014 as below :

SELECT  FirstPrintCardFooter.FooterNotes,
FirstPrintCardFooter.PrintFlag,   dbo.VMainMembers.TransactionNo,
dbo.VMainMembers.PaymentDate,   
dbo.Members.MemberCode,MSRelation.MSRelationName,MSRelation.MSRelationId, 
dbo.Members.SpecialCode, dbo.Members.Name,dbo.Members.RelatedMemberCode,MemberImage,VMainMembers.totalcredit,
CAST(CAST(LEFT(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode + '/') - 1) AS nvarchar) AS int) AS expr1 ,
CAST(CAST(substring(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode)+1 , len(dbo.Members.SpecialCode) - CHARINDEX('/', dbo.Members.SpecialCode)) AS nvarchar) AS int) AS EXPR2 
        
FROM            dbo.Members left join MSRelation on Members.MSRelation=MSRelation.MSRelationId 
LEFT JOIN       dbo.VMainMembers 
ON SUBSTRING(dbo.Members.SpecialCode, 0, CHARINDEX('/', dbo.Members.SpecialCode, 0))=SUBSTRING(dbo.VMainMembers.SpecialCode, 0, CHARINDEX('/', dbo.VMainMembers.SpecialCode, 0)) 						
LEFT JOIN FirstPrintCardFooter 
ON MEMBERS.MemberCode = FirstPrintCardFooter.MemberCode
AND  dbo.VMainMembers.[Year] = FirstPrintCardFooter.TrxYear
WHERE FirstPrintCardFooter.MemberCode IS  NULL    
and dbo.VMainMembers.TransactionNo is not null  and VMainMembers.Year=2018 order by expr1 ,EXPR2

推荐答案

如果我提取你的代码进行检查，并使用你的样本数据：

If I extract your code to check, and use your sample data:

DECLARE @SpecialCode NVARCHAR(MAX);
SET @SpecialCode = '000151/1'
SELECT CAST(CAST(LEFT(@SpecialCode, CHARINDEX('/', @SpecialCode + '/') - 1) AS nvarchar) AS int) AS expr1,
CAST(CAST(substring(@SpecialCode, CHARINDEX('/', @SpecialCode)+1 , len(@SpecialCode) - CHARINDEX('/', @SpecialCode)) AS nvarchar) AS int) AS EXPR2,
SUBSTRING(@SpecialCode, 0, CHARINDEX('/', @SpecialCode, 0)),
SUBSTRING(@SpecialCode, 0, CHARINDEX('/', @SpecialCode, 0))

然后它可以工作：我得到nop错误：

Then it works: I get nop errors:

expr1   EXPR2   (No column name)   (No column name)
151     1       000151             000151

因此，您的表格中的数据与您提供的数据模式不符。



回到你的数据库并仔细检查你的Members.SpecialCode的内容 - 我怀疑那里有不好的数据！

So at a guess, there is data in your table that does not match the pattern of the data you provided.

Go back to your DB and check the content of your Members.SpecialCode very carefully - I suspect there is bad data in there!

这篇关于将5/1转换为int时，转换faild下面的查询出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！