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

Pages

» » SQL-NVL

Thursday, 31 March 2016

SQL-NVL

Posted by at Labels:
This will substitutes the specified value in the place of null values.

     Syntax: nvl (null_col, replacement_value) 

     Ex:
          SQL> select * from student;            -- here for 3rd row marks value is null

                         NO NAME      MARKS
                           --- -------      ---------
                          1        a         100
                          2        b          200
                          3        c

SQL> select no, name, nvl(marks,300) from student; 

                        NO NAME  NVL(MARKS,300)
                         ---  -------  ---------------------
                          1           a             100
                          2           b             200
                          3           c             300

SQL> select nvl(1,2), nvl(2,3), nvl(4,3), nvl(5,4) from dual;

                NVL(1,2)   NVL(2,3)   NVL(4,3)   NVL(5,4)
               ----------    ----------    ----------         ----------
                      1               2                    4                   5

SQL> select nvl(0,0), nvl(1,1), nvl(null,null), nvl(4,4) from dual;

            NVL(0,0)   NVL(1,1) NVL(null,null)  NVL(4,4)
             ----------    ---------- -----------------       ----------

                      0              1                                             4



You May Like:                                              
                            SQL Abs
                            SQL Sign
                            SQL Sqrt
                            SQL Mod                           
                            SQL NVL2
                            SQL Power
                            SQL Exp
                            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. Unknown20 April 2018 at 21:24

    NayHoh,


    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.

    I changed following configuration file and restart the server .
    C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting
    Services\ReportServer\bin\ReportingServicesService.exe.cofig
    Add following line

    After restart , log generated ReportServerService__04_16_2018_13_58_15.log
    But I saw there is an error as follows
    +++++++++++++++++++++++++++

    Microsoft SQL Server Reporting Services Version 2015.0130.4466.04 ((SQL16_SP1_QFE-CU).171222-1041)
    English (Australia)
    AUS Eastern Standard Time
    L:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SSRS_TraceDump_log\\ReportServerService__04_16_2018_13_58_15.log
    SQLRST01
    Microsoft Windows NT 6.2.9200
    6.2.9200
    34204
    Hypervisor

    AMD64
    AMD64
    rshost!rshost!5e84!04/16/2018-13:58:15:: i INFO: CLR runtime is initialized.
    rshost!rshost!5e84!04/16/2018-13:58:15:: i INFO: Derived memory configuration based on physical memory as 16776756 KB
    appdomainmanager!DefaultDomain!5e84!04/16/2018-13:58:15:: i INFO: Entered managed ServiceMain in DefaultDomain.


    appdomainmanager!     DefaultDomain!5e84!04/16/2018-13:58:15:: i INFO: Attempting to start WebApp service: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\..\..\RSWebApp\Microsoft.ReportingServices.Portal.WebHost.exe
    library!DefaultDomain!3868!04/16/2018-13:58:16:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.

    Once again thanks for your tutorial.


    Thank you,
    Ajeeh Kapoor

    ReplyDelete
  2. Unknown28 April 2018 at 02:10

    Salve

    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on #topic!

    I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.

    Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.

    Is this even possible?

    Thanks for any help you can give.

    Wayne

    Appreciate your effort for making such useful blogs and helping the community.

    Merci

    ReplyDelete
  3. Unknown14 June 2018 at 23:45

    Hey,

    I learnt so much in such little time about SQL-NVL. Even a toddler could become smart reading of your amazing articles.
    I have requirement like

    select COL1,Col2 from A
    minus
    Select COL1,COL2 from B;

    both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.

    But nice Article Mate! Great Information! Keep up the good work!

    Kind Regards,
    Radhey

    ReplyDelete

Subscribe to: Post Comments (Atom)