Hi,
You have written this :
Select val from UDF_Split
(
Select vch_Remarks from T_Caf_Receive C
where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(C.bit_DeletedFlag,0)=0
)
What about the where clause here. Shouldnt it be :
Select val from UDF_Split where columnName in (
Select vch_Remarks from T_Caf_Receive C
where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(C.bit_DeletedFlag,0)=0
)
Try this :
select A.int_ApplicantId,A.vch_UniqueRefNo,A.vch_ApplicantName,case int_Gender
when 1 then 'Male' when 2 then 'Female' end as Gender,vch_CorHouseNo,
(select vch_DistrictName from M_District where int_DistrictID=A.int_CorDistID
and int_StateID=A.int_CorStateID)District,vch_CorPinCode,dtm_DOB, (case int_CategoryId
when 1 then 'UR' when 2 then 'SC' when 3 then 'ST' when 4 then 'SEBC' end)Category,
(case isnull(bit_PHOH,0) when 1 then 'Yes' else 'No' end ) PWD,(case isnull(bit_SportsPerson,0)
when 1 then 'Yes' else 'No' end ) SportsPerson,(case isnull(bit_ESM,0) when 1
then 'Yes' else 'No' end ) ESM, (case isnull(bit_Odia,0) when 1 then 'Yes' else 'No' end ) Odia,
(select vch_DDNO from T_Finance_Details F where isnull(F.bit_DeletedFlag,0)=0 and
F.vch_uniquerefno=A.vch_uniquerefno)vch_DDNO,A.vch_CorMobileNo
, case vch_Remarks when null then
(select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo
and ISNULL(c.bit_DeletedFlag,0)=0)else
coalesce(
(select vch_remarks From M_Remark_Detail where int_Remark_id in
(Select val from UDF_Split where val in (
Select vch_Remarks from T_Caf_Receive C
where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(C.bit_DeletedFlag,0)=0
)
)),',','')
end As vch_Remarks
,(case isnull(A.bit_ReceiveStatus,0) when 1 then '1' else '0' end ) ReceiveStatus
from T_ApplicantDetails_Temp A where isnull(A.bit_DeletedFlag,0)=0 and ISNULL(A.bit_ReceiveStatus,0)=1 and
ISNULL(A.bit_Rejected,0)=1 and isnull(A.bit_DuplicateStatus,0)=0 and A.vch_AdvertisementNo='2395'
and A.vch_Post_Code='35' order by A.vch_UniqueRefNo
Hope this helps !!
Regards,
Praneet