How to have more than 100 entries in case statement as a variable

Question

I wrote a case statement with > 100 choices where I am using the same statement in 4 places in a simple query.

The same query twice with a union between them but also is doing a count and therefore the group by also contains the case statement.

This is to relabel some company names where different records for the same company are spelled differently.

I tried to declare a variable as a VarChar(MAX)

declare @CaseForAccountConsolidation varchar(max)

SET @CaseForAccountConsolidation = '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''
       ...

When I went to use it in my select statement - the query just returned the case statement as text and didn't evaluate it.

I also was unable to use it in the group by - I got this error message:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Ideally I would like to have the CASE in just a single place - so that there is no chance of me updating one line and not replicating that elsewhere.

Is there some way of doing this?

I am open to other ways (Like maybe a function - but I am not sure how to use them like this)

Here is a sample of the SELECT I am currently using

SELECT 
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,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'
       ELSE ac.accountName
   END 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
WHERE a.datecreated = CONVERT(DATE,now())
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,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'
       ELSE ac.accountName
   END

UNION

SELECT 
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,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'
       ELSE ac.accountName
   END 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
WHERE a.datecreated = DATEADD(YEAR,-1,CONVERT(DATE,now()))
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,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'
       ELSE ac.accountName
   END

The purpose for this UNION is to return all data for a timeperiod, and ALSO to return data for the same timeperiod for 12 months previously

EDIT: Added a missing "CATCH-ALL"
EDIT2: Added a second ½ of the UNION statement
EDIT3: Corrected the GROUP BY to include some other necessary elements

Answer 1

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.

Answer 2

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 VIEWs as per the other suggestions.

Answer 3

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.

Answer 4

I would use a VIEW to do what you are trying to do. You could, of course, correct the underlying data, but frequently on this site, those asking questions (consultants/dbas/) don't have the authority to do this. Using a VIEW can solve this problem! I also made use of the UPPER function - a cheap way of resolving errors in cases like this.

Now, you only declare the VIEW once and can use it anywhere! This way, you only have one place in which your data conversion algorithm is stored and run, thereby increasing the reliability and robustness of your system.

You can also use a CTE (Common Table Expression) - see bottom of answer!

To answer your question, I did the following:

Create a sample table:

CREATE TABLE my_error (wrong VARCHAR(50));

Insert a couple of sample records:

INSERT INTO my_error VALUES ('Addiction Advice Services Ltd.');
INSERT INTO my_error VALUES ('AIR BP_and-mistake');
INSERT INTO my_error VALUES ('AIR New Zealand Airlines');

Then create a VIEW as suggested:

CREATE VIEW my_error_view AS 
SELECT 
  CASE
    WHEN UPPER(wrong) LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
    WHEN UPPER(wrong) LIKE 'AIR BP%'  THEN 'AIR BP'
    WHEN UPPER(wrong) LIKE 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
    ELSE '***ERROR****' -- You may or may not need this.
                        -- It's attention grabbing (report) and easy to search for (SQL)!
  END AS wrong
FROM my_error;

Then, SELECT from your VIEW:

SELECT * FROM my_error_view
ORDER BY wrong;

Result:

ADDICTION ADVICE
AIR BP
AIR NEW ZEALAND

Et voilà!

You can find all of this on the fiddle here.

The CTE approach:

Same as above, except for the CTE is substituted for the VIEW as follows:

WITH my_cte AS
(
  SELECT 
  CASE
    WHEN UPPER(wrong) LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
    WHEN UPPER(wrong) LIKE 'AIR BP%'  THEN 'AIR BP'
    WHEN UPPER(wrong) 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
  FROM my_error
)
SELECT * FROM my_cte;

The result is the same. You can then treat the CTE as you would any other table - for SELECTs only! Fiddle available here.

Overall, I think that the VIEW approach is better in this case!

Answer 5

Embedded table

select id, tag, trans.val 
  from [consecutive] c
  join ( values ('AIR NEW Z%', 'AIR NEW ZEALAND'),
                ('AIR BP%',    'AIR BP')
       ) trans (lk, val)
    on c.description like trans.lk 

Skip the union and use an OR in the where as suggested by others.