|
|
-
|
|
UnNest function in PostgreSQL
In PostgreSQL, we can perform UnPivoting using the UnNest function. It accepts an array and expands the array items in set of rows.
Syntax: UNNEST(Some array)
Example
Let us consider the below script
Create Table tblUnPivotExample(ItemNo INT, TotalAmt INT,Item1 ......
|
|
-
|
|
Common Table Expression(CTE) is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.It computes the aggregation once, and allows us to reference it by its name (may be multiple times) in the queries.It materialize subqueries thereby helpin......
|
|
-
|
|
In PostgreSQL we can use the Serial datatype for generating the identity column
CREATE TABLE tblIndentity
(
EmpID Serial NOT NULL,
EmpName Character Varying(20)
);
NOTICE: CREATE TABLE will create implicit sequence "tblindentity_empid_seq" for serial column "tblindentity.empid"
Que......
|
|
-
|
|
We can get the version information in PostgreSQL by using the version function as shown under
select version();
/* Result */
version
-------
"PostgreSQL 9.1.0, compiled by Visual C++ build 1500, 32-bit"
In Sql Server we use @@Version
Hope this helps
Thanks for reading......
|
|
-
|
|
Purpose:To replace substring from a specified position to a specified position.
Syntax:Overlay(string placing string from int [for int])
Example
Select Overlay('www.beyondnational.com' Placing 'relational' From 11 For 8) "Overlay Example"
/* Result */
Overlay Example
---------------
www.b......
|
|
-
|
|
Aim: A query to show which employees works for which department
Option 1: Using String_Agg function
Purpose: This values helps to concatenate the input values into a string, separated by delimiter. We can pass any delimiter in this function
Syntax: String_Agg(String_Values, delimiter)
Select ......
|
|
-
|
|
In PostgreSQL concatenation can be done in the following ways
Using Concatenation Operator ( || )
Using Concat function
Using TextCat function
Option 1: Using Concatenation Operator ( || )
Select 'Mr ' || EmpName "Employee Name" from tblEmployee Where EmpID Not In(14,16)
Un......
|
|
-
|
|
Writing Dynamic SQL Queries in PL/SQL using Native Dynamic SQL(NDS)
If we need to build and execute SQL statements at runtime , we need to go for Dymanic SQL. In this article we will look into how to do so using NDS which has two constructs
Execute Immediate
Open For
1.Execute Immediate......
|
|
-
|
|
In this article, we will look into creating our own Aggregate Function in Oracle.
User-defined aggregates are a feature of the Extensibility Framework.It is possbile to make user define Aggregate function because of Oracle's Data Cartridge model which takes advantage of object types and other exten......
|
|
-
|
|
Select
2 EMPNO,ENAME,JOB,SAL,DEPTNO
3 ,Row_Number() Over(Order By JOB) "RowNo"
4 ,Rank() Over(Order By JOB) "Rank"
5 ,Dense_Rank() Over(Order By JOB) "Dense Rank"
6 ,NTile(4) Over(Order By JOB) "NTile"
7 From Emp;
EMPNO ENAME JOB SAL DEPT......
|
|