CODEWITHSUNDEEP
sql-server
cdrrr
cdrrr

Reputation: 1114

Cast nvarchar to tinyint

I'm kinda new to SQL coding and I am trying to select a nvarchar value and insert it into a tinyint column.

I use the following query 

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'

But I get the following error:

Conversion failed when converting the nvarchar value 'YES' to data type tinyint.

I have tried to cast the nvarchar column but I cannot figure it out how to do it.

Can you help me? Thanks

Upvotes: 0

Views: 786

Answers (2)

DVT
DVT

Reputation: 3127

First, I think that SqlZim answer is good.

But I just want to add something here.

First, in the join, when you use the value of a table on the right side of a left join in the where clause, then it is an inner join, not a left join any more. Second, in this case, because of the where clause, no need for a CASE statement, just put a 1 in place of esfl.Stringfield1 in the select list.

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'

Upvotes: 3

SqlZim
SqlZim

Reputation: 38063

If you need to change the value to 1 where Stringfield1 = 'Yes' then you can do this easily with a case expression:

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'

Upvotes: 1

Related Questions