Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: understanding a query...

    Hi guys

    Trying to understand the following:

    Code:
    SELECT 
    
    to_char( max(decode(TRUNC(DATETIME),Trunc(SYSDATE-1),NVL
    
    (UTILIZATION_RATE,0)))) as day1
    
    FROM Schema.Table
    The result is the value of 'UTILIZATION_RATE' for the previous day.

    What is decode doing here? How is it comparing 'TRUNC(DATETIME)' with

    Trunc(SYSDATE-1)? and then what does

    NVL(UTILIZATION_RATE,0)' mean here?

    Will appreciate any help. Thanks.

    Regards
    Shajju
    Last edited by shajju; 11-09-09 at 12:12.

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    I'd like to add that the data is being queried from a table that contains just one hour for a day like the busiest hour of the day.
    Last edited by shajju; 11-09-09 at 12:24.

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Do you ever read the manual? For that matter, do you know the definition?
    manual involving or using human effort, skill, power, energy, etc.;

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thank you for the criticism. Yes I do know the definition but was just wanting to confirm with my more experienced peers.

    Is:
    Code:
    to_char( max(decode(TRUNC(DATETIME),Trunc(SYSDATE-1),NVL
    
    (UTILIZATION_RATE,0)))) as day1
    saying: Use the max value of UTILIZATION_RATE for datetime=trunc(sysdate-1) and show as DAY1?

    If the above is true, I was trying to change this query to show the value of UTILIZATION_RATE at 1AM by substituting
    Code:
    Trunc(sydate-1)
    with
    Code:
    Trunc(sysdate-1)+1/24
    but it is not working.

    More than likely, I've interpreted the above SQL incorrectly. That's why I was asking for help. Sorry.
    Last edited by shajju; 11-10-09 at 04:21.

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    can you give me a simple ddl of the table and one insert . .

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Is:
    Code:
    to_char( max(decode(TRUNC(DATETIME),Trunc(SYSDATE-1),NVL
    
    (UTILIZATION_RATE,0)))) as day1
    saying: Use the max value of UTILIZATION_RATE for datetime=trunc(sysdate-1) and show as DAY1?
    Do you see the difference (in bold)? It also seems you are not aware what TRUNC function really does. I have nothing to add to my post here; maybe just some examples:
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    10.11.2009 11:07:12
    
    SQL> select trunc( sysdate ) trunc_common,
      2         trunc( sysdate, 'dd' ) trunc_day,
      3         trunc( sysdate, 'hh' ) trunc_hour,
      4         trunc( sysdate ) + 1/24 trunc_1am
      5  from dual;
    
    TRUNC_COMMON        TRUNC_DAY           TRUNC_HOUR          TRUNC_1AM
    ------------------- ------------------- ------------------- -------------------
    10.11.2009 00:00:00 10.11.2009 00:00:00 10.11.2009 11:00:00 10.11.2009 01:00:00

  7. #7
    Join Date
    Aug 2009
    Posts
    262
    good example flyboy

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Hey, thanks. Really Appreciated.

    Note: I knew what trunc does

Posting Permissions

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