Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Friday, 1 April 2016

SQL-Exp




This will raise e value to the give power.

     Syntax: exp (value)     

     Ex:
          SQL> select exp(1), exp(2), exp(0), exp(null), exp(-2) from dual;

                        EXP(1)           EXP(2)           EXP(0)  EXP(NULL)    EXP(-2)
   --------            ---------           --------    -------------      ----------

                     2.71828183  7.3890561          1                               .135335283


You May Like                                             
                            SQL Abs
                            SQL Sign
                            SQL Sqrt
                            SQL Mod
                            SQL NVL
                            SQL NVL2
                            SQL Power                           
                            SQL Ln
                            SQL Log
                            SQL CEIL
                            SQL Floor
                            SQL Round
                            SQL Trunc
                            SQL Bitand
                            SQL Greatest
                            SQL Least
                            SQL Coalesce

Share this article :

3 comments:

  1. NayHoh,


    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

    I am using SQL server 2014 standard edition.
    I am using Drill Through action to display a detail report for my cube data, which is working fine. Only issue I see is when I drill through year and quarter it displays data for months in a haphazard manner instead of sorted by month. Ho do I set it to sort by month, and Item Number (which is a fact dimension for this measure) within month






    Once again thanks for your tutorial.


    Cheers,

    ReplyDelete
  2. Hi There,

    In debt to you for making my learning on the #topic area so hassle-free! I lay my faith on your writings.

    I have a field called ACTUALFINISH in format DD/MM/YY and I want to add the hours and minutes.

    For example, I have this date: 29/08/17

    What I do is the next query:

    select to_char(actualfinish, 'DD/MM/YY HH24:MI') from table;

    This query works and I get: 29/08/17 17:39

    But when I try to return to Date type using this query:

    select TO_DATE( (TO_CHAR(actualfinish, 'DD/MM/YY HH24:MI') ), 'DD/MM/YY HH24:MI') from table;

    I get again the initial date: 29/08/17 and the hours and minutes are ignored.

    What I'm doing wrong?

    Awesome! Thanks for putting this all in one place. Very useful!

    Many Thanks,
    Preethi

    ReplyDelete
  3. Salve


    Hot! That was HOT! Glued to the SQL-Exp your proficiency and style!

    This is the table I want to transpose into rows:

    target_name tgl round(avg(round ((AVERAGE/10),3)),3)
    health 30-AUG-17 0.816
    health 31-AUG-17 0.814
    health 01-SEP-17 0.633
    health 02-SEP-17 0.662
    health 03-SEP-17 1.119
    health 05-SEP-17 13.939

    and select query is,

    select * from
    (
    select target_name, trunc(rollup_timestamp) tgl, round(avg(round ((AVERAGE/10),3)),3) from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = 'cpuUtil' and target_type='host'
    and METRIC_NAME = 'Load'
    group by target_name,trunc(rollup_timestamp)
    union
    select target_name,trunc(collection_timestamp) tgl, (round(value,3))
    from MGMT$METRIC_current where
    METRIC_COLUMN = 'cpuUtil' and target_type='host' and METRIC_NAME = 'Load' ) where target_name = 'ehgexnp2db01.ehealth.gov.au'
    order by 1,2;

    Result needed as:

    target_name 30-AUG-17 31-AUG-17 01-SEP-17 02-SEP-17 03-SEP-17 05-SEP-17
    health 0.816 0.814 0.633 0.662 1.119 13.939

    Great effort, I wish I saw it earlier. Would have saved my day :)


    Gracias
    Radhey

    ReplyDelete