views:

306

answers:

6

Is there a way to rewrite a Transact SQL statement that uses a CASE WHEN structure to do the same without using the CASE WHEN?

I'm using a product that has a built-in query designer and its own pseudo-SQL. It has limitations on what I can use with SQL Server and Oracle. So I have this column that, when the underlying database is Oracle, uses DECODE (which is supported). However, I need to make it work with SQL Server and CASE WHEN is not supported.

The statement I'm trying to convert is something like

Decode (StatusColumn,  'Value 1',
Decode(Sign(Now()-TargetDateColumn)),1,'Past
Due', 'Outstanding'),  'Value 2',
Decode(Sign(Now()-TargetDateColumn)),1,'Past
Due', 'Outstanding'),  'Value 3',
Decode(Sign(Now()-TargetDateColumn)),1,'Past
Due', 'Outstanding'),  'Value 4')

I have a limited set of T-SQL options to use and CASE WHEN is not an option. I do have IsNull and Coalesce, but I'm not sure if they will help me with this one.

Don't bother with the date calculations, those are solved.

I searched the CASE WHEN questions here, to no avail.

Thanks!

Update:

I realize that I should have given more details on the reason for the limitations, as this is a developer's resource and it would be assumed that this is a development product. It is not.

I'm using an enterprise software product that has a built-in query designer and its own pseudo-SQL. It has limitations on what I can use with SQL Server and Oracle. Basically, everything that doesn't break the parsing of the built-in query engine is game. That means all the sanctioned functions and expressions, plus all the data abstractions (internal objects that correspond to a physical table in a database and other queries created with the product), plus everything from Oracle SQL or Transact SQL that does not explicitly break the parsing.

The reason why CASE WHEN doesn't work for me is that it breaks the parsing of the pseudo-SQL by the query engine.

Ultimately, I would like to try to:

  1. Use only the product's query designer the SQL that passes the parsing OR
  2. Use a few extra resources from the SQL Server database and the query designer to get it done.

Based on the several good answers that I got, here's the approach that worked out for me, so far.

Jason DeFontes suggested that I could use a database view to perform the CASE WHEN rules and that falls into #2 above. It works for me because a view is dynamic enough that I don't have to do maintenance on it (as opposed to richartallent's truth tables approach, which I believe are close to Jason's approach). Pascal's suggestion of creating a function would go along the same lines, but probably break the parse.

So I created a database view that does all the transformation with CASE WHEN and I added it to my query's SQL, joined it with the existing SQL and it worked just fine. I realize that I'm probably adding an overhead to the database engine, as it will have to retrieve the same data set twice (one for the view and one for the query), but it's one of those cases where it's hardly an issue.

Given that this "use a view to obfuscate it" design works for me, I wonder what would be the more efficient approach:

  • Using a select with CASE WHEN;
  • Using CTE (again, richardtallent);
  • Using Union All (HLGEM);
  • Using Subqueries (MisterZimbu);

I will still check Aramis wyler's suggestion, as it could probably fall into #1 above.

For now, Jason's answer was accepted. Considering that I used CASE WHEN in the view, perhaps the title for the question ended up being is ill-chosen. I upped everybody that suggested something that helped in the process. I don't know if that makes a difference in your reputation or not, but I thought it was the nice thing to do.

Again, I want to thank you all for your help and ask you kindly to edit anything on the question that you fell is not appropriate (it's my first question and English is my second language).

+1  A: 

Write a function which performs the computation using CASE WHEN.

pascal
I can't, for now. I would like to try to solve this, if possible, by using what I have available at the query designer/engine. I gave it a lot of thought before asking the question, because I knew that it would probably attract more attention to the limitations than to the question itself.Ultimately, I thought that it could be of value to somebody else on the same situation. Or at least some food for thought on efficiency of likewise evaluations.
ezingano
+4  A: 

Do you have union all available? Perhaps you could write a query for each of the conditions with the condition of the case inthe where clause and union them together.

HLGEM
+1 Excellent idea (well, besides burning the pseudo-SQL tool)
Andomar
I'll give it a try. I guess your answer is what I was aiming for: some way of handling this by using basic SQL instead of a function.I'll leave the question unanswered for a bit more, to see if something else may be suggested. If not, then I'll accept your answer.Thanks.
ezingano
I upvoted this, but UNION does have two disadvantages: (1) multiple trips to the well (MSSQL is not going to optimize this well), and (2) duplicated code. Some of this could be mitigated by moving the common parts to a CTE, but if you're trying to avoid CASE, CTE is even more platform-specific.
richardtallent
Richard, I agree but sometimes you are stuck with the limits of the tool you are required to use. Union all would be better for performance than union (since we know the differnt quieries will not have common values), but it may or may not be available.
HLGEM
@richardtallent: I don't know if I can use it straight into our query designer/engine, but I'm going to give it a try with a different approach, as inspired by Jason DeFontes' suggestion.
ezingano
+3  A: 

Can you write custom subqueries? Probably not if you don't even have access to CASE WHEN, but this would probably work too:

select
    ...,
    coalesce(c1.value, c2.value, c3.value, ..., <default value>)
from MyTable
left join (select <result 1> as value) c1 on <first condition>
left join (select <result 2> as value) c2 on <second condition>
left join (select <result 3> as value) c3 on <third condition>
MisterZimbu
+1 neat approach
Andomar
I will also try this, as it may be a stretch for the query engine limitations, but it's very simple to understand and closer to what I'm trying to do. Thanks!
ezingano
+1  A: 

It's ugly and depending on the number of values you have it may not be viable. But strictly speaking, I think something like this would work as a translation from the above query segment:

select 'PastDue' from tablename where Now() > TargetDateColumn and (StatusColumn = 'Value 1' or StatusColumn = 'Value 2' or StatusColumn = 'Value 3') union select 'Outstanding' where Now() < TargetDateColumn and (StatusColumn = 'Value 1' or StatusColumn = 'Value 2' or StatusColumn = 'Value 3') union select 'Value 4' where NOT (StatusColumn = 'Value 1' or StatusColumn = 'Value 2' or StatusColumn = 'Value 3')

Aramis wyler
+1  A: 

I'm not exactly sure I understand your code, but this should give you an idea for a different approach.

First, create a table:

CREATE TABLE StatusLookup(
   value nvarchar(255),
   datesign shortint,
   result varchar(255));

Now, populate it with a truth table (lots of repeated logic in here apparently, maybe this should be two truth tables with a CROSS JOIN between them):

INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 1', -1, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 1', 0, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 1', 1, 'Past Due')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 2', -1, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 2', 0, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 2', 1, 'Past Due')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 3', -1, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 3', 0, 'Outstanding')
INSERT INTO StatusLookup(value, datesign, result) VALUES ('Value 3', 1, 'Past Due')

Finally, join and provide a default answer:

SELECT mytable.*, COALESCE(statuslookup.result, 'Value 4')
FROM
    mytable LEFT JOIN statuslookup ON
        statuslookup.value = StatusColumn
        AND statuslookup.datesign = Sign(Now()-TargetDateColumn)

One key advantage to this approach is it puts the business logic in data tables, not code, which is often more maintainable and extensible.

richardtallent
Your suggestion would probably work, as I can use COALESCE from the query engine. I'm partial to the truth table, as I would not like to manage that (not that it is not a good idea, for I have used that before, but it adds an extra layer of management that would not work for me in this particular scenario), but it could be done. Thanks.
ezingano
+3  A: 

Can you move the CASE/WHEN logic into a view, then have the tool query the view?

Jason DeFontes
You know what? Maybe I can!I'm finishing with some other tasks, but will start trying the suggestions as soon as possible. So far, this one seems to be the one.The query designer is limited, but I can pass-through a few things (not CASE-WHEN, though, it breaks the parsing). I can try this approach, indeed.
ezingano