子查询返回多个值时发生错误
我使用了以下查询,但错误发生如下:
i used the below query, but the error occurs as following:
错误:
子查询返回的值超过1。当子查询遵循=,!=,<,< =,>,> =或子查询用作表达式时,不允许这样做。
Subquery returned more than 1 value. this is not permitted when the subquery follows =, !=,<,<=,>,>= or when the subquery is used as an expression.
我该如何解决这个问题?
How can i solve this?
选择
cast(spl。[results_associations_contactIds]为int)
,cast(s。[ results_engagement_id] as bigint)
,DATEADD(S,CONVERT(int,LEFT(s。[results_engagement_createdAt],10)), '1970-01-01 00:00:00:000')
,cast(s。[results_engagement_ownerId ] as bigint)
,s.results_engagement_type
,concat(s。[results_metadata_from_firstName],'',s。[results_metadata_from_lastName])as Engagement_from_name
,s。[results_metadata_from_email]
,s。[results_metadata_fromNumber]
,concat(s。[results_metadata_to_firstName],'',s。[results_metadata_to_lastName])为Engagement_to_name
,s。[results_metadata_to_email]
,s。[results_metadata_toNumber]
,s。[results_metadata_status]
,s。[results_engagement_activityType]
,案例 当ltrim(results_metadata_disposition)='73a0d17f-1163-4015-bdd5-ec830791da20'然后'无应答'
当ltrim(results_metadata_disposition)='9d9162e7-6cf3-4944-bf63-4dff82258764'然后'忙碌'
-d246-4b32-a13b-75a56a4cd0ff'然后'左边留言'
当ltrim(results_metadata_disposition)='17b47fee-58de-441e-a44c-c6300d46f273'然后'错误号码'
当ltrim(results_metadata_disposition)='b2cf5968-551e-4856-9783-52b3da59a7d0'然后'Left VoiceMail'
当ltrim(results_metadata_disposition)='f240bbac- 87c9-4f6e-bf70-924b57d47db7'然后'已连接'
当ltrim(results_metadata_disposition)=''然后''结束为Call_outcome
,s。[results_metadata_recordingUrl]
,cast((s.results_metadata_durationMilliseconds / 1000)%60 as int)
,DATEADD(S,CONVERT(int,LEFT(s。[ results_engagement_lastUpdated],10)),'1970-01-01 00:00:00:000')
来自HubspotSTG。[dbo]。[Engagements] as s
交叉申请(选择值[ results_associations_contactIds]来自string_split
((从HubspotSTG中选择[results_associations_contactIds]。[dbo]。[Engagements] s1
其中s1。[results_associations_contactIds] = s。[results_associations_contactIds]) ,',')
其中CHARINDEX(value,s。[results_associations_contactIds],1)> 0)spl
cast(spl.[results_associations_contactIds] as int)
,cast(s.[results_engagement_id] as bigint)
,DATEADD(S, CONVERT(int,LEFT(s.[results_engagement_createdAt], 10)), '1970-01-01 00:00:00:000')
,cast(s.[results_engagement_ownerId] as bigint)
,s.results_engagement_type
,concat(s.[results_metadata_from_firstName],' ',s.[results_metadata_from_lastName]) as Engagement_from_name
,s.[results_metadata_from_email]
,s.[results_metadata_fromNumber]
,concat(s.[results_metadata_to_firstName],' ',s.[results_metadata_to_lastName]) as Engagement_to_name
,s.[results_metadata_to_email]
,s.[results_metadata_toNumber]
,s.[results_metadata_status]
,s.[results_engagement_activityType]
,case when ltrim(results_metadata_disposition) = '73a0d17f-1163-4015-bdd5-ec830791da20' then 'No Answer'
when ltrim(results_metadata_disposition) = '9d9162e7-6cf3-4944-bf63-4dff82258764' then 'Busy'
when ltrim(results_metadata_disposition) = 'a4c4c377-d246-4b32-a13b-75a56a4cd0ff' then 'Left Live Message'
when ltrim(results_metadata_disposition) = '17b47fee-58de-441e-a44c-c6300d46f273' then 'Wrong Number'
when ltrim(results_metadata_disposition) = 'b2cf5968-551e-4856-9783-52b3da59a7d0' then 'Left VoiceMail'
when ltrim(results_metadata_disposition) = 'f240bbac-87c9-4f6e-bf70-924b57d47db7' then 'Connected'
when ltrim(results_metadata_disposition) = '' then '' end as Call_outcome
,s.[results_metadata_recordingUrl]
,cast((s.results_metadata_durationMilliseconds/1000)%60 as int)
,DATEADD(S, CONVERT(int,LEFT(s.[results_engagement_lastUpdated], 10)), '1970-01-01 00:00:00:000')
from HubspotSTG.[dbo].[Engagements] as s
cross apply(select value [results_associations_contactIds] from string_split
((select [results_associations_contactIds] from HubspotSTG.[dbo].[Engagements] s1
where s1.[results_associations_contactIds] = s.[results_associations_contactIds]),',')
where CHARINDEX(value,s.[results_associations_contactIds],1)> 0) spl
使用 在下面的陈述中。
use in in following statement.
从HubspotSTG中选择[results_associations_contactIds]。[dbo]。[Engagements] s1
其中s1。[results_associations_contactIds] = s。[results_associations_contactIds]),',')
其中CHARINDEX(值,s。[results_associations_contactIds],1)