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

I have a query below that I have been struggled with. I know that my evt_code column is integer but includes nvarchar value also (for example FALL2015,JP19200202 and many others including letters).

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

I need to include those nvarchar codes in my result set. Can you help? I believe I need to add a condition into my join in order to include those codes with nvarchar datatype but don't know how.

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

I apologize for such a long script. You may ignore it. The question is up here but wanted to include the query also. I am using SQL Server 2014.

我为这么长的剧本道歉。你可以忽略它。问题在这里，但也希望包括查询。我正在使用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'

1 个解决方案

#1

Ok, so now we know that the evt_code is of data type NVARCHAR.

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

You have two joins which are joining on that field, listed below.

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

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

If either of those columns are of any numerical data type, you'll get the error which you have mentioned. The solution would be to CAST the fields which you join to them, to NVARCHAR

如果这些列中的任何一个都是任何数值数据类型，您将得到您提到的错误。解决方案是将您加入的字段CAST到NVARCHAR

#1