在sql查询中如下 [英] In sql query as follows

问题描述

在数据库记录中如下（表名指定）

姓名指定头型
 Ram Courseofficer 0 
 Sekar导演1 
 

i我将案例查询如下

当Headtype 0表示我想要文本courseofficer 
当Headtype 1意味着我想要文本Authorisedsignatoury。 
 

该查询如下

 选择指定，（案例 何时 Head_Type = '  0' 然后 '  Courseofficer' 
 何时 Head_Type = '  1' 然后 '  Authorisedsignatory'  else  Head_Type 结束） as  类型 
 来自名称

但是会显示以下错误消息：

<前lang =text>转换varchar值时转换失败'< code> courseofficer< / code>'到数据类型位。

我做错了什么？

解决方案

试试这个：

  SELECT  [Name]， CASE   WHEN  [名称] = '  Director' 那么 '  Authorisedsignatory' < span class =code-keyword> ELSE  [名称]  END   AS  NewDesignation 
  FROM 指定

或

  SELECT  [名称]， CASE   WHEN  [ Headtype] = 1  THEN  '  Authorisedsignatory'  ELSE  [名称]  END   AS  NewDesignation 
  FROM 名称

出现问题是因为您有两种不同的数据类型从案件结构中返回。 Head_Type 有点，你在else分支中返回它。在所有其他分支中，您将返回字符数据。因为没有从bit到varchar的隐式转换，所以你面临错误



所以为了纠正这个问题，你应该明确地转换这个位。如下所示：

 选择名称，
（案例 
 当 Head_Type = '  0' 然后 '  Courseofficer' 
 当 Head_Type = '  1' 然后 '  Authorisedsignatory'  
  else  cast（Head_Type  as   varchar ）
  end ） as  输入 
 来自名称

接下来是您比较的字段有点。使用bit，你不应该使用char比较，因为它会导致额外的转换。所以情况会更好用

 选择指定，
（ Case  
   Head_Type =  0    '  Courseofficer' 
 当 Head_Type =  1  然后 '  Authorisedsignatory' 
  else  cast（Head_Type  as   varchar ）
  end ） as  类型 
 来自名称

因为你正在使用它单值比较我还会简化案例结构，以便

 选择名称，
（ 案例 Head_Type 
    0    '  Courseofficer' 
 当  1  然后 '  Authorisedsignatory' 
  else  cast（Head_Type  as   varchar ）
  end ）< span class =code-keyword> as  类型 
 来自名称

也是一个的 Maciej Los [ ^ ]在第一个解决方案中指出，案例的条件可以简化为使用已经现有数据。





作为附注说明else分支处理head_type可能为null的情况，因此您可能需要调整更好地处理这种情况的代码......

 选择标识， Case    Head_Type = '  0' 然后 '  Courseofficer'  
 当 Head_Type = '  1' 然后 '  Authorisedsignatory'  else  Head_Type  end   as  输入 
 来自名称

以类型结尾表示...您按类型分隔的类型的名称）类型

以des结尾表示列名将为des

 选择标识，案例 当 Head_Type = '  0' 然后 '  Courseofficer' 
 当 Head_Type = '  1' 然后 '  Authorisedsignatory'  else  Head_Type  end  
 来自名称

第二个使用任何指定的列名输出，它只显示（没有列名） ）

如果你使用支架（）意味着停止只有结束不使用..需要列名

 选择标识，（案例 当 Head_Type = '  0' 然后  '  Courseofficer' 
 当 Head_Type = '  1' 然后 '  Authorisedsignatory'  else  Head_Type  end ）
 来自名称

In database record as follows (Table Name Designation)

Name      Designation    Headtype
Ram       Courseofficer     0
Sekar     Director          1

i am putting case query as follows

when Headtype 0 means  i want text    courseofficer
when Headtype 1 means  i want text    Authorisedsignatoury.

for that query as follows

Select designation , (Case when Head_Type = '0' then 'Courseofficer'
 When Head_Type = '1' then 'Authorisedsignatory' else Head_Type end) as Type
from Designations

But below error message is displayed:

Conversion failed when converting the varchar value '<code>courseofficer</code>' to data type bit.

What am i doing wrong?

解决方案

Try this:

SELECT [Name], CASE WHEN [Designation]='Director' THEN 'Authorisedsignatory' ELSE [Designation] END AS NewDesignation
FROM Designation

or

SELECT [Name], CASE WHEN [Headtype]=1 THEN 'Authorisedsignatory' ELSE [Designation] END AS NewDesignation
FROM Designation

The problem occurs because you have two different data types returned from the case structure. The Head_Type is a bit and you're returning it in the else branch. In all other branches you're returning character data. Because there is no implicit conversion from bit to varchar you face the error

So to correct this you should explicitely convert the bit. Something like the following:

Select designation , 
       (Case 
           when Head_Type = '0' then 'Courseofficer'
           When Head_Type = '1' then 'Authorisedsignatory' 
           else cast(Head_Type as varchar) 
        end) as Type
from Designations

The next thing is that the field you compare is a bit. With bit you shouldn't use char comparison since it causes an extra conversion. So the case would be better with

Select designation , 
       (Case 
           when Head_Type = 0 then 'Courseofficer'
           When Head_Type = 1 then 'Authorisedsignatory' 
           else cast(Head_Type as varchar) 
        end) as Type
from Designations

since you're using just a single value comparison I would also simplify the case structure to

Select designation ,
       (Case Head_Type
           when 0 then 'Courseofficer'
           When 1 then 'Authorisedsignatory'
           else cast(Head_Type as varchar)
        end) as Type
from Designations

Also as Maciej Los[^] pointed out in the first solution, the condition for the case could be simplified to use the already existing data.


As a side note The else branch handles the situation that the head_type is possibly null so you may need to adjust the code to better handle such situation...

Select designation , Case when Head_Type = '0' then 'Courseofficer'
 When Head_Type = '1' then 'Authorisedsignatory' else Head_Type end as Type
from Designations

end as "type" mean.. the name of the "type" you seperated by end) as type
end as "des" mean column name will be des

Select designation , Case when Head_Type = '0' then 'Courseofficer'
 When Head_Type = '1' then 'Authorisedsignatory' else Head_Type end
from Designations

The second one make the output with any specified column name,It just show (No column name)
if u use bracket () mean stop with only end dont use..as requires column name

Select designation , (Case when Head_Type = '0' then 'Courseofficer'
 When Head_Type = '1' then 'Authorisedsignatory' else Head_Type end)
from Designations

这篇关于在sql查询中如下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！