Hi all..

Can we discuss briefly about the sql functions.

Thanks...

Hi all...

What is sql function?

Thanks...

Hi all...


Sql function:

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.

In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter function appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.

Thanks...

Hi all...

Types of function.

Thanks...

Hi all...


Types of functions:

There are several basic types and categories of functions in SQL. The basic types of functions are:

1. Aggregate Functions and

2. Scalar Functions.

Thanks...

Hi all...


What is aggregate function?


Thanks in advance

Hi all...

Aggregate Function:

Aggregate functions are called on certain fields over an entire set of records, and return one value. In a SQL statement, they can appear in the SELECT clause. With VB LINQ, this concept appears a bit differently.

SELECT SUM(Cost) TotalCost, COUNT(*)

FROM OrderTable

WHERE OrderDate > “Sept-29-2007”

Thanks...

Hi all...


What is scalar function?


Thanks in advance...

Hi all...

What is analytic function?


Thanks in advance...

Hi all...


Scalar function:

Scalar functions operate against a single value, and return a single value based on the input value.


Thanks...

Hi all...


Analytic function:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.


Thanks...

Can U please briefly explain me what is analytic function and which situation we need to use this function?