This discussion is archived
7 Replies Latest reply: May 4, 2012 12:12 AM by AlbertoFaenza RSS

Help With A Case Statement With Multiple Variables

908328 Newbie
Currently Being Moderated
I apologize if this is the incorrect Forum for this type of question, but it was the closest one that I could find. I'm pretty new with SQL and am stuck on this issue. I have roughly 26 dates that I need to compare to one another. Each date is tied to a step code. I also have a Stop value that is tied directly to the "max date" of the step codes. So, I need to compare 30 dates against one another to 1st - ID the max date; 2nd - ID if the Stop value is correct; 3rd - if the stop value is incorrect, identify what the correct value would be.

At first, this seemed like it wouldn't be that hard. I wrote a query that found the max date for each step code. Then I realized that multiple step codes could have the same date. So, I tried using this case statement, but I did not get the expected results. Is there a more efficient way of getting what I need? This code seems like it's not necessary and probably the source of my issue.

CASE
WHEN FS25.ACTUAL_COMPLETION_DATE > FS.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS1.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS2.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS3.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS4.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS5.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS6.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS7.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS8.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS9.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS10.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS11.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS12.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS13.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS14.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS15.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS16.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS17.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS18.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS19.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS20.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS21.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS22.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS23.ACTUAL_COMPLETION_DATE AND FS25.ACTUAL_COMPLETION_DATE > FS24.ACTUAL_COMPLETION_DATE AND L.FORECLOSURE_STOP_CODE <= '8' THEN '9'
ELSE 'UH OH'
END AS "CHANGE FC STOP TO"

Any assistance is appreciated!
  • 1. Re: Help With A Case Statement With Multiple Variables
    indra budiantho Expert
    Currently Being Moderated
    Give the data and the result u want.
  • 2. Re: Help With A Case Statement With Multiple Variables
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    Are FS to FS25 all aliases for different tables?

    Provide more info and scenario, please.

    Regards.
    Al
  • 3. Re: Help With A Case Statement With Multiple Variables
    918949 Pro
    Currently Being Moderated
    with tab as
    (select sysdate+4 date1, sysdate+3 date2, sysdate+2 date3, sysdate date4 from dual)
    select case date1 when greatest(date1,date2,date3,date4) then 'yeap' else 'uh oh' end test
    from tab;
  • 4. Re: Help With A Case Statement With Multiple Variables
    908328 Newbie
    Currently Being Moderated
    FS thru FS25 are all from the same table. They're laid out that way so that the information is displayed as column headers instead of in rows.

    Here's a scenario:
    CASE
    WHEN FS25 > FS24 AND FS25 > FS23 AND STOP_CODE <> '9' THEN '9'
    ELSE 'UH OH'
    END AS 'CHANGE STOP CODE TO'

    I ran the query on some smaller sets and it appears that I will need to add something to the effect of:
    CASE
    WHEN FS25 > FS24 OR FS25 IS NOT NULL AND FS24 IS NULL AND FS25 > FS23 OR FS25 IS NOT NULL AND FS23 IS NULL AND STOP_CODE <> '9' THEN '9'
    ELSE 'UH OH'
    END AS 'CHANGE STOP CODE TO'

    Going through all of the different combinations doesn't seem like the most efficient way to achieve the desired result.
  • 5. Re: Help With A Case Statement With Multiple Variables
    AlbertoFaenza Expert
    Currently Being Moderated
    I think Igor pointed out a working solution before.

    Applying it at your examples (you missed the operator after STOP_CODE, I assume it =):
    CASE
    WHEN FS25 = GREATEST(FS25, FS24, FS23) AND STOP_CODE = '9' THEN '9'
    ELSE 'UH OH'
    END AS 'CHANGE STOP CODE TO'
    {code}
    
    Be careful at the second example. You are checking:
     
    {code:sql}
    FS25 > FS24 OR FS25 IS NOT NULL AND FS24 IS NULL AND FS25 > FS23
    OR
    FS25 IS NOT NULL AND FS23 IS NULL AND STOP_CODE = '9' 
    {code}
    
    Remember that AND has higher priority among operators than OR so if FS25 is greater than FS24 and FS23 the condition will be true even if STOP_CODE is not equal 9.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 6. Re: Help With A Case Statement With Multiple Variables
    908328 Newbie
    Currently Being Moderated
    Al, That was helpful, thanks. However, I'm still not getting the desired result. I believe the Null values are still throwing the query off. How can I incoporate those into the query?
  • 7. Re: Help With A Case Statement With Multiple Variables
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    If you see that your output is not the desired one, try to understand what you have to do.
    Translating the requirements into SQL code is the second step IMO.

    Anyway, the NVL function might be a way to overcome your problem with NULL values.

    Regards.
    Al

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points