将nvarchar值'FALL'转换为数据类型时转换失败int

Question

我有一个查询，我一直在努力。我知道我的evt_code列是整数，但也包括nvarchar值（例如FALL2015，JP19200202和许多其他字母）。

我需要在结果集中包含nvarchar代码。你能帮我吗？我相信我需要在我的join中添加一个条件，以包含nvarchar数据类型的代码，但不知道如何。

对于这么长的脚本，我表示歉意。你可以忽略它。问题在这里，但也想包括查询。我正在使用SQL Server 2014.

select distinct 

prc_code AS REGCLASS, 
etp_code AS TYPE, 
evt_title AS TITLE, 
evt_code AS MEETING, 
ea.adr_city AS CITY, 
ea.adr_state AS STATE, 
ea.adr_country AS COUNTRY, 
evt_start_date AS DATE, 

case 
    when net_ivd_amount_cp=0 
    then 'Comp' 
    else 'Paid' 
end as 'Paid/Comp', 

net_ivd_amount_cp as 'Revenue', 
ev_registrant.reg_cst_key AS [COUNT], 
net_ivd_amount_cp, 
chp_name as 'District/National Council', 
c03_region_name as 'DC Region', 
cst_sort_name_dn as 'Name', 
ind_first_name as 'First', 
ind_last_name as 'Last', 
cst_ixo_title_dn as 'Registrant Title', 
cst_org_name_dn as 'Organization', 
ca.adr_line1 as 'Address 1', 
ca.adr_line2 as 'Address 2', 
ca.adr_city as 'Registrant City', 
ca.adr_state as 'Registrant State', 
ca.adr_post_code as 'Registrant Zip', 
ca.adr_country as 'Registrant Country', 
cst_phn_number_complete_dn as 'Phone', 
cst_eml_address_dn as 'Email', 

case 
    when mem_member_type IS null 
    then 'Prospect' 
    else mem_member_type 
    end as 'Member/Prospect', 
mem_join_date as 'Join Date', 

reg_attendance_flag as 'Attended', 

case 
    when ind_int_code='Student' 
    then 'Student' 
    else org_ogt_code 
end as 'Industry', 

cr.rgn_code as 'Region' 

FROM ev_registrant 
    join co_customer on reg_cst_key=cst_key 
    INNER JOIN ev_event on reg_evt_key=evt_key 
    INNER JOIN ev_event_type ON ev_event.evt_etp_key = ev_event_type.etp_key 
    left outer join dbo.ac_invoice with (nolock) ON  dbo.ev_registrant.reg_inv_code=dbo.ac_invoice.inv_code 
    LEFT OUTER JOIN dbo.vw_ac_invoice_detail WITH (nolock) ON dbo.ac_invoice.inv_key = net_inv_key 
    LEFT OUTER JOIN dbo.ac_payment_detail WITH (nolock) ON dbo.ac_payment_detail.pyd_ivd_key = net_ivd_key 
    LEFT OUTER JOIN dbo.oe_product with (nolock) on dbo.oe_product.prd_key=net_ivd_prc_prd_key 
    LEFT OUTER JOIN dbo.oe_product_type WITIH (nolock) on prd_ptp_key = ptp_key 
    LEFT OUTER JOIN dbo.oe_price WITH (nolock) on net_ivd_prc_key = prc_key 
    left outer join ev_event_location on evl_evt_key=evt_key and evl_primary=1 
    left outer join ev_location on evl_loc_key=loc_key 
    left outer join co_customer_x_address ecxa on loc_cxa_key=ecxa.cxa_key 
    left outer join co_address ea on ecxa.cxa_adr_key=ea.adr_key 
    left outer join client_uli_dc_region on LEFT(evt_code, 4)=c03_chp_chapter_number 
    left outer join co_chapter on LEFT(evt_code, 4)=chp_chapter_number 
    left outer join co_customer_x_address ccxa on cst_cxa_key=ccxa.cxa_key 
    left outer join co_address ca on ccxa.cxa_adr_key=ca.adr_key 
    LEFT OUTER JOIN co_country cc on ca.adr_country=cc.cty_code 
    LEFT OUTER JOIN co_region cr on cr.rgn_key=cc.cty_rgn_key 
    left outer join vw_client_uli_member_type on cst_key=mem_cst_key 
    left outer join co_individual_x_organization on ixo_key=cst_ixo_key 
    left outer join co_organization on ixo_org_cst_key=org_cst_key 
    left outer join co_individual on cst_key=ind_cst_key 
where 
    ((net_ivd_void_flag)=0 
     Or net_ivd_void_flag Is Null) 
    AND (reg_delete_flag=0 Or reg_delete_flag Is Null) 
    AND ((ev_registrant.reg_cancel_date) Is Null 
    and evt_start_date>='2017-07-01') 
    and ptp_code='event' 

Answer 1

好吧，现在我们知道evt_code是数据类型NVARCHAR。

您有两个加入该字段的连接，如下所列。

left outer join client_uli_dc_region on LEFT(evt_code, 4)=c03_chp_chapter_number 
left outer join co_chapter on LEFT(evt_code, 4)=chp_chapter_number 

如果其中任何一列是任何数字数据类型，那么您将得到您提到的错误。解决的办法是将CAST加入到它们的字段中，以NVARCHAR