数据类型nvarchar和varchar在模运算符中不兼容 [英] The data types nvarchar and varchar are incompatible in the modulo operator

问题描述

我使用下面的cursor命令来更新我的表上的记录，基于连接。

我有一些类似的代码在我的程序的另一部分工作正常，但是没有在连接上使用LIKE逻辑。

但是当我执行这个代码时会抛出一个错误。

< blockquote>

消息402，级别16，状态1，第12行

数据类型nvarchar和varchar在模运算符中不兼容。

< blockquote>

这是我的代码：

  DECLARE @tablevalue NVARCHAR @sql NVARCHAR（MAX）; 
 
 DECLARE table_value_cursor CURSOR 
 FOR 
 SELECT DISTINCT [资费查询] 
 FROM [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06- 01-customer325]; 
 
 OPEN table_value_cursor 
 
 FETCH NEXT FROM table_value_cursor INTO @tablevalue 
 
 WHILE @@ FETCH_STATUS = 0 
 BEGIN 
 SELECT @ sql = N'
 
 UPDATE [masked_2014-06-30-2014-06-01-customer325] 
 SET [masked_2014-06-30-2014-06-01-customer325]。[ [价格]] = 
 ROUND（[Orbisrates]。[dbo]。[Orbis_Import_June2014]。[Peakperminute] / 60 * [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325 ] [ChargedTimeSecs] + [Orbisrates]。[dbo]。[Orbis_Import_June2014]。[Peakconnect]，4）
 FROM [OrbisRates]。[dbo]。[Orbis_Import_June2014] 
 INNER JOIN [OrbisBilling]。 [dbo]。[to] LIKE [Orbis_Import_June2014] [dbo]。[masked_2014-06-30-2014-06-01-customer325] on [OrbisBilling]。 ]。[Destination] +'％'
 WHERE 
 [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325]。[tariff lookup] =' + @tablevalue +''''; 
 
 EXEC sp_executesql @sql; 
 
 FETCH NEXT FROM table_value_cursor INTO @tablevalue; 
 END 
 
关闭table_value_cursor 
 DEALLOCATE table_value_cursor; 
  

PS。

任何帮助非常感谢。

解决方案

由于使用错误而导致错误 LIKE b

  INNER JOIN [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325] 
 LIKE [Orbis_Import_June2014 ]。[Destination] +'％'<  -  Here 
  

  select * from table1 
其中order_id像'％' '+ order_no +'％'''
  

所以在你的情况下， p>

  SELECT @sql = N'
< ... Rest Of Code ...> 
 on [OrbisBilling]。[dbo]。[to] 
 LIKE'+'％'''+'[Orbis_Import_June2014]。 [destination]'+'％'''+ 
'WHERE [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325] ...'
  
 
 
 
 编辑：
 
 
 
 t需要一个光标你想要做什么。对于下的 [Tariff Lookup]  下面的每个值，你实际上做的是
 [masked_2014-06-30-2014-06- 01-customer325] 你是
尝试做 UPDATE 。同样可以简单地通过单个查询来实现;通过使
选择 [Tariff Lookup] ，如下所示的子查询。 
 更新masked325 
 SET masked325 [售价] = ROUND orbit14。[Peakperminute] / 60 * masked325。
 [ChargedTimeSecs] + orbit14。[Peakconnect]，4）
 FROM [OrbisRates]。[dbo]。[Orbis_Import_June2014] orbit14 
 INNER JOIN [ OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325] masked325 
 on orbit14。[Destination] = masked325。[To] 
 
 WHERE masked325。 [关税查询] 
 IN（
 SELECT DISTINCT [资费查询] 
 FROM [OrbisBilling]。[dbo]。[masked_2014-06-30-2014-06-01-customer325] 
）; 
  
 Sidenote： WHERE 在这里很有意义。
 
I am using the following cursor command to update a record on my table based on a join.

I have some similar code working fine in another part of my program, but without using LIKE logic on the join.

But when I execute the this code it throws an error.

  
Msg 402, Level 16, State 1, Line 12

  The data types nvarchar and varchar are incompatible in the modulo operator.
Here is my code:
DECLARE @tablevalue NVARCHAR(MAX), @sql NVARCHAR(MAX); 

DECLARE table_value_cursor CURSOR 
FOR
    SELECT DISTINCT [Tariff Lookup]
    FROM [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325];

OPEN table_value_cursor 

FETCH NEXT FROM table_value_cursor INTO @tablevalue 

WHILE @@FETCH_STATUS = 0 
BEGIN
   SELECT @sql = N'

   UPDATE [masked_2014-06-30-2014-06-01-customer325] 
   SET [masked_2014-06-30-2014-06-01-customer325].[Sell Price] = 
ROUND ([Orbisrates].[dbo].[Orbis_Import_June2014].[Peakperminute] / 60 * [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325].[ChargedTimeSecs] + [Orbisrates].[dbo].[Orbis_Import_June2014].[Peakconnect], 4) 
   FROM [OrbisRates].[dbo].[Orbis_Import_June2014]
   INNER JOIN [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325] on [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325].[To] LIKE [Orbis_Import_June2014].[Destination]+'%'
   WHERE 
      [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325].[tariff lookup] = '''+ @tablevalue +'''';

   EXEC sp_executesql @sql; 

   FETCH NEXT FROM table_value_cursor INTO @tablevalue; 
END 

CLOSE table_value_cursor 
DEALLOCATE table_value_cursor;
PS. I have tested the update section in the middle and that runs fine on its own.

Any help greatly appreciated.
解决方案
You are getting that error because of wrong use LIKE operator as in below statement from your code
INNER JOIN [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325] 
on  LIKE [Orbis_Import_June2014].[Destination]+'%' <-- Here
Just put it inside quote. You should do something like below (a sample example)
select * from table1
where order_id like '%''' + order_no + '%'''
So in your case you should change it as below
   SELECT @sql = N'
   <....Rest Of Code ...> 
   on [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325].[To] 
   LIKE' + '%''' + '[Orbis_Import_June2014].[Destination]' + '%''' +
   ' WHERE [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325] ...'
EDIT:

You don't need a cursor for what you are trying to do. What you are essentially doing is 
for each value of [Tariff Lookup] under table [masked_2014-06-30-2014-06-01-customer325] you are
trying to do the UPDATE. The same can be achieved simply by a single query; by means of making the 
selection of [Tariff Lookup] a subquery like below. give it a try and see how it goes.
UPDATE masked325 
SET masked325.[Sell Price] = ROUND (orbit14.[Peakperminute] / 60 * masked325.
[ChargedTimeSecs] + orbit14.[Peakconnect], 4) 
FROM [OrbisRates].[dbo].[Orbis_Import_June2014] orbit14
INNER JOIN [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325] masked325
on orbit14.[Destination] = masked325.[To]

WHERE masked325.[tariff lookup] 
IN (
    SELECT DISTINCT [Tariff Lookup]
    FROM [OrbisBilling].[dbo].[masked_2014-06-30-2014-06-01-customer325]
  );
Sidenote: The WHERE condition actually doesn't make much sense here.

                        
这篇关于数据类型nvarchar和varchar在模运算符中不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！