Home » SQL & PL/SQL » SQL & PL/SQL » Multiple values in CASE statement
Multiple values in CASE statement [message #233017] Tue, 24 April 2007 07:03 Go to next message
bigove
Messages: 4
Registered: April 2007
Junior Member
Hello.

This is my first post, so I apologise for anything I've done incorrectly!

I am trying to write an UPDATE statement that changes values based on a condition, so I am using a CASE statement. However, my statement keeps failing and it's driving me mad!

When I take the basic CASE statment on its own it works fine. I then need another CASE statment around that as the values will depend on what the outcome is and it is here that it fails. Here is my code to try and make more sense of what I am saying:

 
select 
    CASE 
       
            (select --to_number(substr(CONTACTTIME, 1, 2)) original_value, 
                case 
                    when (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) < 0 )
                        then (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) + 24)
                    else (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)))
                end new_value
            from scho.wcontactlogging)
        
       when 0 then 1
       when 1 then 1
       when 2 then 2
       when 3 then 2
       when 4 then 3
       when 5 then 3
       when 6 then 4
       when 7 then 4
       when 8 then 5
       when 9 then 5
       when 10 then 6
       when 11 then 6
       when 12 then 7
       when 13 then 7
       when 14 then 8
       when 15 then 8
       when 16 then 9
       when 17 then 9
       when 18 then 10
       when 19 then 10
       when 20 then 11
       when 21 then 11
       ELSE 12
    END)
from scho.wcontactlogging;


I am wondering if the problem is because I am using mutliple values in my CASE expression - is this possible? If not how would I then go about writing what I want to achieve?

If any more information is needed please let me know and I will post it below.

Thanks in advance
Re: Multiple values in CASE statement [message #233022 is a reply to message #233017] Tue, 24 April 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I clearly understand:
select 
    CASE 
              ( case 
                    when (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) < 0 )
                        then (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)) + 24)
                    else (to_number(to_char(sysdate, 'HH24')) - to_number(substr(CONTACTTIME, 1, 2)))
                end )
       when 0 then 1
...
       when 21 then 11
       ELSE 12
    END value
from scho.wcontactlogging;

Regards
Michel
Re: Multiple values in CASE statement [message #233072 is a reply to message #233022] Tue, 24 April 2007 09:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, you could replace the outer case with a one line expression using FLOOR and LEAST:
  1  select lvl
  2        ,least(floor(lvl/2)+1,12)
  3* from  (select level-1 lvl from dual connect by level <= 23)
SQL> /

       LVL LEAST(FLOOR(LVL/2)+1,12)
---------- ------------------------
         0                        1
         1                        1
         2                        2
         3                        2
         4                        3
         5                        3
         6                        4
         7                        4
         8                        5
         9                        5
        10                        6
        11                        6
        12                        7
        13                        7
        14                        8
        15                        8
        16                        9
        17                        9
        18                       10
        19                       10
        20                       11
        21                       11
        22                       12
Re: Multiple values in CASE statement [message #233079 is a reply to message #233017] Tue, 24 April 2007 10:00 Go to previous messageGo to next message
bigove
Messages: 4
Registered: April 2007
Junior Member
Thanks very much for your help here; trying Michel's answer (purely because it was the first one!) looks to have cured the problem! I still need to work away to get the update statement to work correctly, i.e. so that it takes the right values from the case statement, but I'm confident I can get that working. I'll also try the second query as it may be neater, once I've read up on LEAST.

Once again, thank you very much.
Re: Multiple values in CASE statement [message #234175 is a reply to message #233022] Mon, 30 April 2007 05:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Weird - got a double post.
Ignore this.

[Updated on: Mon, 30 April 2007 05:32]

Report message to a moderator

Previous Topic: Doubt regarding trigger and other database objects
Next Topic: Interesting Query
Goto Forum:
  


Current Time: Sun Dec 04 00:49:47 CST 2016

Total time taken to generate the page: 0.23844 seconds