sql - select command inside then in case statement -
i got error following query in sql server 2008
subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.
i want use select command inside case statement after then
below query
declare @starttime datetime ,@endtime datetime ,@personid varchar(max) ,@supplierid uniqueidentifier = null set @starttime = '2011-1-22' set @endtime = '2012-1-27' set @personid = '2dd3cd60-4acc-4ff1-9956-2938099c08af,69186022-78b5-4bc6-9878-55b14a44a5aa,e64f0bf8-51cc-4c85-a4bd-2615d3ba7a52,53091d8b-2891-4c46-babd-1f0036ffe003,ea21226c-8be6-48de-a707-fe0edd0b62a3,f5ce7a19-a8da-4c0c-a233-861f9330361b' declare @table table (personid uniqueidentifier) insert @table select deviceid [dbo].[split](@personid, ',') create table #temptable ( person_id uniqueidentifier ,asset_id uniqueidentifier ,event_type_id int ,event_start_date datetime ,event_date datetime ,amend_time int ,grp int ,seq int identity(1, 1) ,activity_time int ) --adding raw data temp table insert #temptable select asset_event.person_id ,asset_id ,event_type_id ,event_start_date ,event_date ,isnull(datediff(ss, event_start_date, event_date), 0) interval ,0 ,0 asset_event inner join person on asset_event.person_id = person.person_id event_type_id < 3 , event_date >= @starttime , event_date <= @endtime , asset_event.person_id in ( case (len(@personid)) when 0 asset_event.person_id else ( select deviceid [dbo].[split](@personid, ',') ) end ) , isnull(convert(varchar(40), person.supplier_id), '') = case when @supplierid not null convert(varchar(36), @supplierid) else '' end order person_id ,event_date select * #temptable drop table #temptable any alternative query.
try changing select query this.
select asset_event.person_id, asset_id, event_type_id, event_start_date, event_date, isnull(datediff(ss, event_start_date, event_date), 0) interval, 0, 0 asset_event inner join person on asset_event.person_id = person.person_id event_type_id < 3 , event_date >= @starttime , event_date <= @endtime , ( len(@personid) = 0 or asset_event.person_id in (select deviceid [dbo].[split](@personid, ',')) ) , isnull(convert(varchar(40), person.supplier_id), '') = case when @supplierid not null convert(varchar(36), @supplierid) else '' end
Comments
Post a Comment