将nvarchar铸造成丁质

Question

我对SQL编码有点陌生，我试图选择一个nvarchar值并将其插入到tinyint列中。

我使用以下查询

insert into COV (GID, DocumentNumber, RegistrationDate, CustomerCode, CustomerName, DeliveryDateAndTime, LineGID, Item, Quantity, DeliveryDate, MU, ExportedToSchnell)
    select 
        esd.GID, esd.ADCode, esd.ADRegistrationDate, esc.Code, esc.Name,
        esd.DeliveryDueDate, esfl.GID, esf.Code, esfl.Quantity,  
        esfl.DeliveryDate, esmm.fMUCode, esfl.Stringfield1 
    from 
        ESFIDocumentTrade esd
    left join 
        ESFITradeAccount esc on esd.fTradeAccountGID = esc.GID
    left join 
        ESFIDocumentType est on esd.fADDocumentTypeGID = est.GID
    left join 
        ESFILineItem esfl on esfl.fDocumentGID = esd.GID
    left join 
        ESFIItem esf on esf.GID = esfl.fItemGID
    left join 
        ESMMItemMU esmm on esmm.fItemGID = esf.GID
    where 
        est.Code = 'COV' 
        and esfl.StringField1 = 'YES' 
        and esd.ADRegistrationDate > '2017-02-01'

但我得到了以下错误：

当将nvarchar值'YES‘转换为数据类型tinyint时，转换失败。

我尝试过转换nvarchar列，但我想不出如何实现它。

你能帮帮我吗?谢谢

Answer 1

首先，我认为SqlZim的答案是好的。

但我只想在这里补充一些东西。

首先，在联接中，当在where子句中使用左侧联接右侧的表的值时，它是内部联接，不再是左联接。第二，在这种情况下，由于where子句，不需要case语句，只需在select列表中用1代替esfl.Stringfield1。

insert into COV (GID, DocumentNumber, RegistrationDate, CustomerCode, CustomerName, DeliveryDateAndTime, LineGID, Item, Quantity, DeliveryDate, MU, ExportedToSchnell)
select esd.GID, esd.ADCode, esd.ADRegistrationDate, esc.Code, esc.Name,
esd.DeliveryDueDate, esfl.GID, esf.Code, esfl.Quantity,  esfl.DeliveryDate,
esmm.fMUCode, 1
from ESFIDocumentTrade esd
inner join ESFIDocumentType est on esd.fADDocumentTypeGID=est.GID
inner join ESFILineItem esfl on esfl.fDocumentGID=esd.GID
left join ESFITradeAccount esc on esd.fTradeAccountGID=esc.GID
left join ESFIItem esf on esf.GID=esfl.fItemGID
left join ESMMItemMU esmm on esmm.fItemGID=esf.GID
where est.Code='COV' and esfl.StringField1='YES' and esd.ADRegistrationDate>'2017-02-01'

Answer 2

如果您需要将值更改为1其中的Stringfield1 = 'Yes'，那么您可以使用一个case表达式轻松地做到这一点：

insert into COV (
   GID
 , DocumentNumber
 , RegistrationDate
 , CustomerCode
 , CustomerName
 , DeliveryDateAndTime
 , LineGID
 , Item
 , Quantity
 , DeliveryDate
 , MU
 , ExportedToSchnell
 )
select 
   esd.GID
 , esd.ADCode
 , esd.ADRegistrationDate
 , esc.Code
 , esc.name
 , esd.DeliveryDueDate
 , esfl.GID
 , esf.Code
 , esfl.Quantity
 , esfl.DeliveryDate
 , esmm.fMUCode
 , case when esfl.Stringfield1 = 'YES' then 1 else 0 end
from ESFIDocumentTrade esd
  left join ESFITradeAccount esc
    on esd.fTradeAccountGID = esc.GID
  left join ESFIDocumentType est
    on esd.fADDocumentTypeGID = est.GID
  left join ESFILineItem esfl
    on esfl.fDocumentGID = esd.GID
  left join ESFIItem esf
    on esf.GID = esfl.fItemGID
  left join ESMMItemMU esmm
    on esmm.fItemGID = esf.GID
where est.Code = 'COV' 
  and esfl.StringField1 = 'YES' 
  and esd.ADRegistrationDate > '2017-02-01'