Case Statement in Sql Using Like
Hello I Have a Sql Statement Insert into Foundation. Taxliability. Empowersystemcalendarcode Select Systemtax. Systemtaxid, Empowercalendarcode...
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
Even though CalendarName has values like Quarterly (EOM) I still end up getting 0. Any ideas and suggestions!
3 Answers
For one, I would update your SQL to this so you are using a JOIN on your SELECT statement instead of placing this in a WHERE clause.
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 the INSERT INTO?
You can also do like this. I think this will work.
select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %'
- Try ruling-out any null issues with ISNULL():
Try ruling-out any case-sensitivity issues with UPPER():
CASE WHEN upper(isnull(EmpowerCalendarCode.CalendarName, 'none')) LIKE '%MONTHLY%' THEN 3...
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
