How to have more than 100 entries in case statement as a variable
Put the data into a table
CREATE TABLE AccountTranslate (wrong VARCHAR(50), translated(VARCHAR(50));
INSERT INTO AccountTranslate VALUES ('ADDICTION ADVICE%','ADDICTION ADVICE');
INSERT INTO AccountTranslate VALUES ('AIR BP%','AIR BP');
INSERT INTO AccountTranslate VALUES ('AIR NEW Z%', 'AIR NEW ZEALAND');
and join to it.
SELECT ...,COALESCE(AccountTranslate.translated, ac.accountName) AS accountName
FROM
....,
account_code ac left outer join
AccountTranslate at on ac.accountName LIKE AccountTranslate.wrong
That way you can avoid keeping the data up to date in multiple places. Just use the COALESCE
where you need it. You can incorporate this into CTE or VIEW
s as per the other suggestions.
One easy way to eliminate the repetition of the CASE expression is to use CROSS APPLY like this:
SELECT
SUM(c.charge_amount) AS GSTExcl
,dl.FirstDateOfMonth AS MonthBilled
,dl.FirstDateOfWeek AS WeekBilled
,x.accountName
,dl.FinancialYear
,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
CROSS APPLY
(
SELECT
CASE
WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
END AS accountName
) AS x
LEFT Join charge c ON a.accession_id = c.accession_id
LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
GROUP BY
dl.FirstDateOfMonth
,x.AccountName
With the help of CROSS APPLY you assign a name to your CASE expression in such a way that it can be referenced anywhere in your statement. It works because strictly speaking you are defining the computed column in a nested SELECT – the FROM-less SELECT that follows the CROSS APPLY.
This is the same as referencing an aliased column of a derived table – which technically this nested SELECT is. It is both a correlated subquery and a derived table. As a correlated subquery, it is allowed to reference the outer scope's columns, and as a derived table it allows the outer scope to reference the columns it defines.
For a UNION query that uses the same CASE expression, you have to define it in each leg, there is no workaround for that except to use a completely different replacement method instead of the CASE. However, in your specific case it is possible to fetch the results without UNION.
The two legs differ in the WHERE condition only. One has this:
WHERE a.datecreated = CONVERT(DATE,now())
and the other this:
WHERE a.datecreated = DATEADD(YEAR,-1,CONVERT(DATE,now()))
You can combine them like this:
WHERE a.datecreated IN (
CONVERT(DATE,now()),
DATEADD(YEAR,-1,CONVERT(DATE,now()))
)
and apply it to the modified SELECT at the beginning of this answer.
Another option I think if you need to re-use it several places an Inline table valued function will be a good one.
CREATE FUNCTION dbo.itvf_CaseForAccountConsolidation
( @au_lname VARCHAR(8000) )
RETURNS TABLE
RETURN
SELECT
CASE
WHEN UPPER(@au_lname) LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
WHEN UPPER(@au_lname) LIKE 'AIR BP%' THEN 'AIR BP'
WHEN UPPER(@au_lname) LIKE 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
ELSE '****ERROR****' -- you may or may not need this!
-- If converting every record, then yes, if not, then no!
-- Errors should stand out on browsing and it's easy to search for!
END AS wrong
--Copied from verace
Your select will be like this.
SELECT
SUM(c.charge_amount) AS GSTExcl
,dl.FirstDateOfMonth AS MonthBilled
,dl.FirstDateOfWeek AS WeekBilled
,dd.wrong AS accountName
,dl.FinancialYear
,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
LEFT Join charge c ON a.accession_id = c.accession_id
LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
CROSS APPLY dbo.itvf_CaseForAccountConsolidation( ac.accountName)dd
GROUP BY
dl.FirstDateOfMonth
,dl.FirstDateOfWeek
,wrong
,dl.FinancialYear
,CONVERT(Date,c.date_charged)
Also, I have not tested this and the performance of the code also should be determined.
EDIT1: I think andriy already have given one which uses cross apply which redacts the code. Well, this one can be centralized since any changes in the function will reflect in all since you are repeating the same in other parts of the code.