please help me to solve the errors.

Question

1.00/5 (2 votes)

See more:

SQL

This is the case i am using within a procedure while executing my query i am getting following error please help me to solve them.

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 (select vch_remarks From M_Remark_Detail where int_Remark_id in(Select val from UDF_Split(Select vch_Remarks from T_Caf_Receive where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0))) end As vch_Remarks

errors are Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'case'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'else'. Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.

my complete procedure is

CREATE PROCEDURE [dbo].[USP_ViewRejectedReport]
(
	
	@Pvch_AdvertisementNo varchar(200),
	@Pvch_Post_Code varchar(100),
	@Pint_DistrictId int,
	@PintType int	
)

as
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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by A.vch_UniqueRefNo
                End 
            End
         
 end

Posted 17-Oct-14 1:32am

ank0657

Updated 17-Oct-14 1:37am

v2

Add a Solution

Comments

MT_ 17-Oct-14 8:50am

posting solution by yourself may not be a good idea. It was an error in your code which you fixed which is just fine.

2 solutions

Solution 1

Try this, I think last parenthesis is missing
sq

SQL

(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 
(select vch_remarks From M_Remark_Detail where int_Remark_id in(Select val from UDF_Split(Select vch_Remarks from T_Caf_Receive where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0)) 
end ) As vch_Remarks

Posted 17-Oct-14 1:42am

MT_

Comments

ank0657 17-Oct-14 7:45am

thanks but still getting same errors

MT_ 17-Oct-14 8:11am

Which line is giving issue. I think in SSMS, if you double click, it should show the line having trouble.
When I searched for word "case vch_Remarks" in the complete SP you posted, didn't find it !!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ank0657 · Accepted Answer · 2014-10-17T02:25:00

SQL

CREATE PROCEDURE [dbo].[USP_ViewRejectedReport]
(

    @Pvch_AdvertisementNo varchar(200),
    @Pvch_Post_Code varchar(100),
    @Pint_DistrictId int,
    @PintType int
)

as
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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                    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 (select  vch_remarks From M_Remark_Detail where int_Remark_id in(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))) 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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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 (select  vch_remarks From M_Remark_Detail where int_Remark_id in(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))) 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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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 (select  vch_remarks From M_Remark_Detail where int_Remark_id in(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))) 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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                   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 (select  vch_remarks From M_Remark_Detail where int_Remark_id in(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))) 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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by 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 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,   ( select vch_Remarks from T_CAF_Receive C where C.vch_UniqueRefNo=A.vch_UniqueRefNo and ISNULL(c.bit_DeletedFlag,0)=0 )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= @Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code order by A.vch_UniqueRefNo
                End
                Else
                Begin
                    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 (select  vch_remarks From M_Remark_Detail where int_Remark_id in(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))) 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=@Pvch_AdvertisementNo and A.vch_Post_Code=@Pvch_Post_Code and A.int_examDistrictId=@Pint_DistrictId order by A.vch_UniqueRefNo
                End
            End

 end