11

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

  • How do the the 2 parts of the UNION differ? They look fairly similar, except for the slightly different WHERE conditions. – ypercubeᵀᴹ May 16 '18 at 12:14
  • That is the key difference. The two different WHERE conditions on the date give today & the same date 12 months ago. This means I can then compare the numbers for that day and and the same day 12 months ago in the presentation layer - but running the single SQL query. – kiltannen May 16 '18 at 12:18
  • 3
    Why not a single SELECT with WHERE a.datecreated = CONVERT(DATE,now()) OR a.datecreated = DATEADD(YEAR,-1,CONVERT(DATE,now())) ? – ypercubeᵀᴹ May 16 '18 at 12:35
  • @ypercubeᵀᴹ The simple answer is when building this at first I was copying a way I did it somewhere else that used UNION. The slightly more complicated is that the date limiter is actually quite a buit more complex than today and the same date 12 months ago. The date range I am selecting for is from the 1st Jul to the current date +from the 1st Jul before that to the date that is exactly 12 months ago. (Financial Year To Date VS Last FY YTD 12 months ago - this gives a comparison of growth or otherwise for the financial year). BUT as AndryM & you suggest, I am going to try minus the UNION – kiltannen May 16 '18 at 22:45
11

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.

  • Nice one Andriy - +1! Inspired by you :-), I've added another approach to my answer - a CTE - I'm not sure which is the best approach! – Vérace May 16 '18 at 10:55
  • Hi Andriy, I like the look of this solution. I did mention that I had a UNION - but I was dumb enough to not include it in my example. I have done so now. I suspect this x from CROSS APPLY is not likely to be available to the second half of the UNION is it? So this would mean I would still be stuck with 2 copies of the CASE is that right? (I will check it out tomorrow when I get back to work) – kiltannen May 16 '18 at 12:04
  • @kiltannen Drop the UNION and just include the datecreated column in your GROUP BY clause (and update the WHERE clause to include both dates that you're interested in). – Scott M May 16 '18 at 12:40
  • @ScottM: I don't think the OP needs to include the datecreated column in the GROUP BY. Other than that, I completely agree, they can just combine the WHERE clauses and ditch the UNION. – Andriy M May 16 '18 at 12:47
  • @scott-m I will have to try this tomorrow BUT I suspect that doesn't work so well. It's not actually one day - it's potentially several months. I think what I ran into was having up to 11 months of daily data - so the where had start & end AND then I had to run an OR for the same period 12 months previously. I think this ended up with a performance hit. I'd have to try again - but I do remember running into problems that I did not have when running the UNION. Of course that does bring problems of it's own. Like the one I'm currently wrestling with.. – kiltannen May 16 '18 at 12:48
22

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.

4

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.

3

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.