Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    India
    Posts
    40

    Unanswered: Other case statement how to use Decode

    Hi All,

    I have a table with 5 fields in that...
    Ex : a b c d sec
    Table name TimeTest

    In Sec , seconds are stored....

    I want to find each record to which hour it belongs to
    So i am using this

    Select
    A,
    B,
    c,
    D,
    (CASE
    WHEN SEC < 3600 THEN '1'
    WHEN sec> 3600 AND sec< 7200 THEN '2'
    WHEN sec> 7200 AND sec< 10800 THEN '3'
    WHEN sec> 10800 AND sec< 14400 THEN '4'
    WHEN sec> 14400 AND sec< 18000 THEN '5'
    WHEN sec> 18000 AND sec< 21600 THEN '6'
    WHEN sec> 21600 AND sec< 25200 THEN '7'
    WHEN sec> 25200 AND sec< 28800 THEN '8'
    WHEN sec> 28800 AND sec< 32400 THEN '9'
    WHEN sec> 32400 AND sec< 36000 THEN '10'
    WHEN sec> 36000 AND sec< 39600 THEN '11'
    WHEN sec> 39600 AND sec< 43200 THEN '12'
    WHEN sec> 43200 AND sec< 46800 THEN '13'
    WHEN sec> 46800 AND sec< 50400 THEN '14'
    WHEN sec> 50400 AND sec< 54000 THEN '15'
    WHEN sec> 54000 AND sec< 57600 THEN '16'
    WHEN sec> 57600 AND sec< 61200 THEN '17'
    WHEN sec> 61200 AND sec< 64800 THEN '18'
    WHEN sec> 64800 AND sec< 68400 THEN '19'
    WHEN sec> 68400 AND sec< 72000 THEN '20'
    WHEN sec> 72000 AND sec< 75600 THEN '21'
    WHEN sec> 75600 AND sec< 79200 THEN '22'
    WHEN sec> 79200 AND sec< 82800 THEN '23'
    WHEN sec> 82800 AND sec< 86400 THEN '24'
    ELSE 'No Hour'
    END) Sales Hour
    From TimeTest

    It is working fine...can any let me know other then this is there any other way
    We can use between ..if possible please let me know can we use DECODE
    and get the hour ....

    Please give u r suggestions on this.

    Thanks
    Suryadevara

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could use BETWEEN operator ...
    Code:
    SELECT a, b, c, d, 
    (CASE 
     WHEN sec < 3600 THEN '1'  
     WHEN sec BETWEEN 3601 AND 7199 THEN '2'
     WHEN sec BETWEEN 7200 AND 10799 THEN '3'
     ...
     END
    ) sales_hour
    FROM timetest;

  3. #3
    Join Date
    May 2004
    Location
    Germany
    Posts
    25
    You could just caluculate the hour using trunc:

    SELECT a, b, c, d, trunc(sec/3600)+1 as sales_hour from timetest;

  4. #4
    Join Date
    Jun 2002
    Location
    India
    Posts
    40
    Thanks for u r suggestion...S we can use that ...BETWEEN....
    But what i want to do is that..i want to reduce that code to some extant..
    that is y i want some suggestions on that...
    Suryadevara

Posting Permissions

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