Thursday 30 May 2013

General Functions in SQL

These functions work with any data type and pertain to using nulls.

  • NVL (expr1,expr2): Converts a null value to an actual value
  • NVL2 (expr1,expr2,expr3): If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
  • NULLIF (expr1,expr2): Compares two expressions and returns null if they are equal, or the first expression if they are not equal.
  • COALESCE ( expr1,expr2,....., expr n): Returns the first non-null expression in the expression list.

Using NVL Function

  • Data types that can be used are date, character, and number.
  • Data types must match:
    • NVL ( comm,0 )
    • NVL (hiredate,'01-JAN-13')
    • NVL(job,'No Job Yet')
Example:


Using the NVL2 function

  • The NVL2 function examines the first expression. 
  • If the first expression is not null, then the NVL2 function returns the second expression. 
  • If the first expression is null, then the third expression is returned.

Syntax:
NVL2(expr1,expr2,expr3)

In the syntax:

expr1 : is the source value or expression that may contain null.
expr2: is the value returned if exp1 is null.
expr3: is the value returned if expr2 is null.

Example:


Using the NULLIF Function

  • The NULLIF function compares two expressions. 
  • If they are equal, the function returns null. 
  • If they are not equal the function returns the first expression. 
  • You cannot specify the literal NULL for first expression.
Syntax: 
 NULLIF(expr1,expr2)

In the syntax:

expr1  is the source value compared to expr2.
expr2  is the source value compared with expr1. (if it is not equal to expr1, expr1 is returned.)

Example:


In the above example, no employee having salary=their annual commision. 
So everytime in the result tab, first value is returned. 
If in the case any employee having sal= his annual commission (12*comm) then we should have noticed null in RESULT tab.

Using the COALESCE Function

  • The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.
  • If the first expression is not null, it returns that expression; Otherwise, it does a COALESCE of the remaining expressions.
  • This function finds the first non null expression and returns. Thats it
Syntax:
              COALESCE(expr1,expr2,......exprn)

In the Syntax:

expr1  returns this expression if it is not null.
expr2  returns this expression if the first expression is null and this expression is not null.
exprn  returns this expression if the preceding expression are null.

Example:



Conditional Expression  covers in Next post.
  • CASE expression
  • DECODE function



 


Conversion Function in SQL



In some cases, Oracle server uses data of one data type where it expects data of a different data type. When this happens, Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by Oracle server, or explicitly by the user.

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

                    From                                           To

        VARCHAR2 OR CHAR                   NUMBER
        VARCHAR2 OR CHAR                   DATE
        NUMBER                                         VARCHAR2
        DATE                                                VARCHAR2

The assignment succeed if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

Explicit data type conversion covers in Next post.

Explicit Data Type Conversion

SQL Provides 3 functions to convert a value from one data type to another:

TO_CHAR (number | date, [, fmt] ) 

This function converts a number or date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER (char, [fmt] )

This function converts a character string containing digits to a number in the format specified by the optional format model fmt.

TO_DATE (char, [fmt] )

This function converts a character string representing a date to a date according to the fmt specified. If fmt is omitted, the format is DD-MON-YY.

Using the TO_CHAR Function with Dates


TO_CHAR(date,'format_model')

The format model:
  • Must be enclosed in single quotation marks and is case sensitive.
  • Can include any valid date format element.
Example: 


TO_CHAR ( number,'format_model')

Example: here you can notice the both sal and salary printed with formatted result.



Note:
TO_NUMBER()
and TO_DATE()  

I'll upload the example if any one ask for me. In the comment section ask for me, and I'll upload it. 

Date Function - Working with Dates

SYSDATE is a function that returns the current database server date and time.

Example:

SELECT SYSDATE                                  * DUAL is a dummy table.
FROM DUL;

Result
----------------
30-MAY-13

Arithmetic with Dates

  • Add or subtract a number to or from a date for a resultant date values.
  • Subtract two dates to find the number of days between those dates.
  • Add hours to a date by dividing the number of hours by 24.
Example:

The example on the slide displays the last name and the number of weeks worked by the employee department number 10.

Date Functions

  • MONTHS_BETWEEN(date1,date2): Finds the number of months between date1 and date2.
  • ADD_MONTHS(date,n): Adds n number of calender months to date.
  • NEXT_DAY(date,'char'): Finds the date of the next specified day of the week.
  • LAST_DAY(date): Finds the date of the last day of the month that contains date.
  • ROUND(date [, 'fmt']): Returns date rounded to the unit specified by the format model fmt.
  • TRUNC(date [, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Using Date Functions

MONTHS_BETWEEN



 ADD_MONTHS

NEXT_DAY


LAST_DAY


ROUND


TRUNC