SQL statement using Like

Question

Hello, I have a SQL statement

INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode

SELECT SystemTax.SystemTaxID,
       EmpowerCalendarCode.CalendarCodeID
      ,CASE WHEN  EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
            WHEN  EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
            WHEN  EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
       ELSE 0
       END
       FROM Foundation.Common.SystemTax SystemTax, Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
WHERE SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode and SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType

Although CalendarName has values ​​like Quarterly (EOM), I still get 0. Any ideas or suggestions!

Answer 1

First, I would update your SQL to use it JOINin yours SELECTinstead of putting it in a sentence WHERE.

INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode

SELECT SystemTax.SystemTaxID,
       EmpowerCalendarCode.CalendarCodeID
      ,CASE WHEN  EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
            WHEN  EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
            WHEN  EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
       ELSE 0
       END
FROM Foundation.Common.SystemTax SystemTax
INNER JOIN Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
    ON SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode 
    AND SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType

two, what happens if you remove INSERT INTO?

Answer 2

• Try to eliminate any errors with ISNULL ():

• Try eliminating any case sensitivity issues with UPPER ():

  CASE (isnull (EmpowerCalendarCode.CalendarName, 'none')) LIKE '% MONTHLY%' THEN 3...