CODEWITHSUNDEEP
sqlcase
Jason
Jason

Reputation: 87

How to use Case statement in SQL correctly

The below Select statement does NOT return a record for me:

Select 1:

    SELECT 
        Case When rcdr.PolicyNumber like '' + bmpd.PaymentReferenceNumber + '%'  
        Then 'Adc' else 'Exceed' end as System
     FROM RcDetailRecords rcdr
       join Bil_ManualPaymentDetails bmpd
       on rcdr.PolicyNumber like '' + bmpd.PaymentReferenceNumber + '%' + ''

Output:

    System =

The below Select statement DOES return a record for me:

Select 2:

     SELECT 
        Case When rcdr.PolicyNumber like '1234567890%'
        Then 'Adc' else 'Exceed' end as System
     FROM RcDetailRecords rcdr
       join BilManualPaymentDetails bmpd
       on rcdr.PolicyNumber like '1234567890%'

Output:

    System = Adc

Am I missing a tick '' mark somewhere in the first Select ?

BilManualPaymentDetails Table:

PaymentReferenceNumber 
123456789020161013025120

RcDetailRecords Table:

PolicyNumber
1234567890

Upvotes: 0

Views: 42

Answers (1)

Hart CO
Hart CO

Reputation: 34774

1234567890 is not like 123456789020161013025120%, but 123456789020161013025120 is like 1234567890%

So it appears you have the fields switched, try:

  SELECT 
        CASE WHEN bmpd.PaymentReferenceNumber LIKE '' + rcdr.PolicyNumber + '%'  
             THEN 'Adc' 
             ELSE 'Exceed' 
        END as System
     FROM RcDetailRecords rcdr
     JOIN Bil_ManualPaymentDetails bmpd
       ON bmpd.PaymentReferenceNumber like '' + rcdr.PolicyNumber + '%' + ''

Upvotes: 2

Related Questions