sql server - Subquery returned more than 1 value couldnt able to rectify -
create procedure [dbo].[usp_viewrejectedreport24102014] ( @pvch_advertisementno varchar(200) ,@pvch_post_code varchar(100) ,@pint_districtid int ,@pinttype int ) declare @remarks varchar(max) set @remarks = ( select distinct c.vch_remarks t_caf_receive c,t_applicantdetails_temp c.vch_uniquerefno =a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) begin if (@pinttype = 1) begin if (@pint_districtid = 0) begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code order a.vch_uniquerefno end else begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code , a.int_examdistrictid = @pint_districtid order a.vch_uniquerefno end end else if (@pinttype = 2) begin if (@pint_districtid = 0) begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code order a.vch_uniquerefno end else begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code , a.int_examdistrictid = @pint_districtid order a.vch_uniquerefno end end else if (@pinttype = 3) begin if (@pint_districtid = 0) begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code order a.vch_uniquerefno end else begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code , a.int_examdistrictid = @pint_districtid order a.vch_uniquerefno end end else if (@pinttype = 4) begin if (@pint_districtid = 0) begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code order a.vch_uniquerefno end else begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code , a.int_examdistrictid = @pint_districtid order a.vch_uniquerefno end end else begin if (@pint_districtid = 0) begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code order a.vch_uniquerefno end else begin select a.int_applicantid ,a.vch_uniquerefno ,a.vch_applicantname ,case int_gender when 1 'male' when 2 'female' end gender ,vch_corhouseno ,( select vch_districtname m_district int_districtid = a.int_cordistid , int_stateid = a.int_corstateid ) district ,vch_corpincode ,dtm_dob ,( case int_categoryid when 1 'ur' when 2 'sc' when 3 'st' when 4 'sebc' end ) category ,( case isnull(bit_phoh, 0) when 1 'yes' else 'no' end ) pwd ,( case isnull(bit_sportsperson, 0) when 1 'yes' else 'no' end ) sportsperson ,( case isnull(bit_esm, 0) when 1 'yes' else 'no' end ) esm ,( case isnull(bit_odia, 0) when 1 'yes' else 'no' end ) odia ,( select vch_ddno t_finance_details f isnull(f.bit_deletedflag, 0) = 0 , f.vch_uniquerefno = a.vch_uniquerefno ) vch_ddno ,a.vch_cormobileno ,( case @remarks when null ( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) else ( select stuff(( select vch_remarks m_remark_detail int_remark_id in ( select val udf_split(( select vch_remarks t_caf_receive c c.vch_uniquerefno = a.vch_uniquerefno ), ',') ) xml path('') ), 1, 1, '') ) end ) remarks ,( case isnull(a.bit_receivestatus, 0) when 1 '1' else '0' end ) receivestatus t_applicantdetails_temp isnull(a.bit_deletedflag, 0) = 0 , isnull(a.bit_receivestatus, 0) = 1 , isnull(a.bit_rejected, 0) = 1 , isnull(a.bit_duplicatestatus, 0) = 0 , a.vch_advertisementno = @pvch_advertisementno , a.vch_post_code = @pvch_post_code , a.int_examdistrictid = @pint_districtid order a.vch_uniquerefno end end end
try replacing population of remarks variable either
declare @remarks varchar(max) = '' select @remarks = @remarks + ',' + t.remarks ( select distinct c.vch_remarks remarks t_caf_receive c ,t_applicantdetails_temp c.vch_uniquerefno = a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) t or
declare @remarks varchar(max) set @remarks = ( select top 1 distinct c.vch_remarks t_caf_receive c,t_applicantdetails_temp c.vch_uniquerefno =a.vch_uniquerefno , isnull(c.bit_deletedflag, 0) = 0 ) depending on want do.
sql-server sql-server-2008
No comments:
Post a Comment