c# - Get the Count of a SubQuery -
i need count result of sql query have no control over. idea wrap inner sql wrapper count query. outer sql quite simple, here got:
select count(*) ( x ) countquery
where x whatever inner sql goes. problem query crash, namely if else end.
how craft wrapper sql wrap around sql?
here example of 1 of inner sql need count:
if null <> 'pipeline_stage' begin cnts ( select o.[opportunity_id] ,[opportunity_name] ,[opportunity_details] ,[image_url] ,opportunity_value ,[probability] ,[bid_currency] ,[bid_amount] ,[bid_type] ,[bid_duration] ,[forecast_close_date] ,o.[category_id] ,c.category_name ,c.background_color ,o.[pipeline_id] ,o.[stage_id] ,[opportunity_state] ,[responsible_user_id] ,u.[first_name] ,u.[last_name] ,o.[visible_to] ,o.visible_team_id ,o.[date_created_utc] ,o.[date_updated_utc] ,o.owner_user_id ,o.import_id ,follow_id ,row_number() over( order case when @sortorder = 'opportunity_name' opportunity_name end, case when @sortorder = 'responsible_user' isnull(u.[first_name], 'zz') end, case when @sortorder = 'forecast_close_date' forecast_close_date end, case when @sortorder = 'date_created' o.[date_created_utc] end, case when @sortorder = 'forecast_close_date_desc' forecast_close_date end desc, case when @sortorder = 'date_created_desc' o.[date_created_utc] end desc ) introw, count(o.instance_id) over() count [insightly].[dbo].[opportunity] o (nolock) left join [insightly].[dbo].[reference.category] c (nolock) on o.category_id = c.category_id , c.instance_id = @instanceid left join [insightly].[dbo].[user] u (nolock) on u.user_id = o.responsible_user_id , u.instance_id = @instanceid left join [insightly].[dbo].[user.follow] uf (nolock) on (o.opportunity_id = uf.opportunity_id , uf.user_id = @currentuserid , uf.instance_id = @instanceid) o.instance_id = @instanceid , temporary = 0 , (@userid null or [responsible_user_id] =@userid) , (@categoryid null or c.category_id = @categoryid) , (@importid null or o.import_id = @importid) , opportunity_state in @opportunitystate) select * cnts introw between @skip , @take end else begin cnts ( select o.[opportunity_id] ,[opportunity_name] ,[opportunity_details] ,[image_url] ,opportunity_value ,[probability] ,[bid_currency] ,[bid_amount] ,[bid_type] ,[bid_duration] ,[forecast_close_date] ,o.[category_id] ,c.category_name ,c.background_color ,o.[pipeline_id] ,o.[stage_id] ,[opportunity_state] ,[responsible_user_id] ,u.[first_name] ,u.[last_name] ,o.[visible_to] ,o.visible_team_id ,o.[date_created_utc] ,o.[date_updated_utc] ,o.owner_user_id ,o.import_id ,follow_id ,row_number() over( order isnull(p.pipeline_name, 'zz'), isnull([pipeline.stage].stage_order, 999) ) introw, count(o.instance_id) over() count [insightly].[dbo].[opportunity] o (nolock) left join [insightly].[dbo].[reference.category] c (nolock) on o.category_id = c.category_id , c.instance_id = @instanceid left join [insightly].[dbo].[user] u (nolock) on u.user_id = o.responsible_user_id , u.instance_id = @instanceid left join [insightly].[dbo].[user.follow] uf (nolock) on (o.opportunity_id = uf.opportunity_id , uf.user_id = @currentuserid , uf.instance_id = @instanceid) left outer join [pipeline.stage] with(nolock) on o.pipeline_id = [pipeline.stage].pipeline_id , o.stage_id = [pipeline.stage].stage_id left outer join [pipeline] p with(nolock) on o.pipeline_id = p.pipeline_id , p.instance_id = @instanceid o.instance_id = @instanceid , temporary = 0 , (@userid null or [responsible_user_id] =@userid) , (@categoryid null or c.category_id = @categoryid) , (@importid null or o.import_id = @importid) select * cnts introw between @skip , @take end
you can create temp table based on query , count rows in it.
create temporary table my_results <whatever query have>; select count(*) my_results;
Comments
Post a Comment