Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2015
    Posts
    5

    Smile Unanswered: Alternative to Select Case

    Hello,
    I have a field [change type] that includes such values such as New Order, Addition, Reduction, Cancellation, Consolidation, and Swap. For different reports, they are displayed slightly differently, and it sometimes depends on other field values. For example, in one report 'Addition' is displayed as 'Adds to Existing', if [change type] = 'Swap' and [annualized charge] > $0 then 'Adds to Existing', if [change type] = 'Swap' and [annualized charge] < $0 then 'Reduction', if [change type] is 'New Order' and [type] = 'Internal' then 'Adds to Existing' (else New Order).

    The way I'm handling it now is in a query:
    Code:
    SELECT [metrics change type] = CASE 
    WHEN [change type] = 'addition' THEN 'Adds to Existing'
    WHEN [change type] = 'swap' AND [annualized charge] > 0 THEN 'Adds to Existing'
    WHEN [change type] = 'swap' AND [annualized charge] < 0 THEN 'Reduction'
    WHEN [change type] = 'new order' AND [type] = 'internal' THEN 'Adds to Existing' ELSE
    [change type] END
    I just want to know if anyone has any suggestions for a better way.

    Thanks in advance!
    Jeff

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    You could make it table driven I suppose. You could have a table that contains the fields that make up your case logic and then you can just join on that table to get this derived value which would also be in said new table. That way if the logic changes, you do not have deply any new code. Just add some data to your new table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Greetings Jeff!

    There isn't a simple, one size fits all answer to this kind of question. The problem seems to be a display issue more than a logic issue, so I'd probably solve it at the client/app server or browser level. If this actually impacts the data, then the code based solution is probably the most flexible. Since you have at least columns ([change type], [annualized charge], and [type]) all contributing to the final value, trying to create a JOIN to a lookup table is going to be both inefficient and probably hard to read.

    As always, better is subjective and depends on what you're trying to improve. The answer that you have now appears to be a good one.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    It may perform poorly depending on the scale of the data and the available indices. However the lookup table is more maintainable. I am becoming more of the opinion that the appearance of string literals in stored procedures are built in maintenance issues most of the time, and the appearance of too many strings in your sql is a sign of bad code.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2015
    Posts
    5
    Thank you both for your response!

    Thrasymachus - Since the display is sometimes based on varying multiple fields, sometimes just one field, how would I make an efficient lookup table? So for [change type] = 'Adds to Existing', what would I put in the [type] field? Leave it NULL or would I need a record for each value of [type]? I assume if it's the latter, I wouldn't need a record for every possibility, just every existing combination, based on a query.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jsic1210 View Post
    Thank you both for your response!

    Thrasymachus - Since the display is sometimes based on varying multiple fields, sometimes just one field, how would I make an efficient lookup table? So for [change type] = 'Adds to Existing', what would I put in the [type] field? Leave it NULL or would I need a record for each value of [type]? I assume if it's the latter, I wouldn't need a record for every possibility, just every existing combination, based on a query.
    hat way you woi
    it could be an outer join to the lookup table (and yes I know this creates another step in the query processing). That way you would not need a value forevery combination. Just on the ones you are concerned with.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jul 2015
    Posts
    5
    Thrasymachus,

    Sorry for the late response, I've been tied up. I just tried it with a LEFT OUTER JOIN, but every record has a NULL value. To simplify, I created a view of every combination of fields that could affect the output field (Metrics Change). I joined that to my lookup table, using the following code:
    Code:
    select v.*, r.[Metrics Change]
    from tvwPendingCategories as v left outer join
    	tblReportCategories as r on 
    	v.[type] like r.[type] and
    	v.[product fees] like r.[Product Fees] and
    	v.category like r.category and
    	v.[change type] like r.[change type] and
    	v.[positive charge] like r.[Positive Charge]
    Again, sometimes only one or two fields affect [Metrics Change] and sometimes multiple fields affect it. I'm using 'And' in the join. Is that the problem? Using 'Or' produced too many results.

  8. #8
    Join Date
    Jul 2015
    Posts
    5
    Okay, I found a solution. In my lookup table, I added a column: priority. I ranked the records because records could potentially join to multiple records in the lookup table. So I added a max(priority) field to the view. I also changed the joins. Here is my new code:
    Code:
    select v.*, condition = min(r.[priority])
    from tvwPendingCategories as v left outer join
    	tblReportCategories as r on 
    	v.[type] like isnull(r.[type],v.[type]) and
    	v.[product fees] like isnull(r.[Product Fees],v.[product fees]) and
    	v.category like isnull(r.category,v.category) and
    	v.[change type] like isnull(r.[change type],v.[change type]) and
    	v.[positive charge] like isnull(r.[Positive Charge],v.[positive charge])
    group by v.type, v.[product fees], v.category, v.[change type], v.[positive charge]

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    All tied up? that could be fun with the right people.

    I was a little busy myself yesterday.

    So did you find your solution then? I was going to say that if you are doing a left JOIN and the value is null in the "right table" and the "left table" you might have to do something like...

    SELECT *
    FROM tbRight r
    LEFT JOIN tbLeft l
    on r.somenonnullvalue = l.somenonnullvalue
    AND (r.somenullvalue = l.somenullvalue or (r.somenullvalue is null and l.somenullvalue is null))

    ...because nothing equals null, right?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2015
    Posts
    5
    You know, when I typed "tied up," I thought "Nobody will misread that, I'm the only one with my mind in the gutter." Thanks for proving me wrong!
    Anyway, yes, I solved it. I used LEFT OUTER JOIN that was this logic:
    Code:
    FROM a LEFT OUTER JOIN b ON a.fieldname = ISNULL(b.fieldname,a.fieldname)
    I was a little surprised it was right, because I was basically joining a table with itself for NULL values. But basically, what it did was "If b.fieldname is null, treat b.fieldname (the lookup table) as it being equal to a.fieldname." So that in conjunction with using the Priority field (to handle situations where a record met criteria of multiple records in the lookup table).

    Thanks everyone for the help!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jsic1210 View Post
    You know, when I typed "tied up," I thought "Nobody will misread that, I'm the only one with my mind in the gutter." Thanks for proving me wrong!
    You haven't been around DBForums very long, so I can understand how you'd make that type of mistake. Many people do things like that until they get to know us better.

    Quote Originally Posted by jsic1210 View Post
    Code:
    FROM a LEFT OUTER JOIN b ON a.fieldname = ISNULL(b.fieldname,a.fieldname)
    Ah, the elusive OPEN JOIN which treats a NULL value on the left side as a wildcard (matching everything except another NULL) on the right side. I just stumbled over that in an old ACM article a couple of months ago... I'd forgotten all about it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •