Oracle - String Aggregate Functions

Hi Everyone,

If there is a requirement to list values from multiple rows to a single row related with a specific value, Oracle string aggregation function can be used to achieve this. Below are the different methods available in different versions of Oracle. 

LISTAGG Analystic Function in 11g Release 2

SELECT DEPTNO, LISTAGG(ENAME, ',') 

WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEES

FROM   EMP

GROUP BY DEPTNO;

WM_CONCAT Function (Works with 11g Release 1)

SELECT DEPTNO, WM_CONCAT(ENAME) AS EMPLOYEES

FROM   EMP

GROUP BY DEPTNO;

Table Content

Result of the query

Thanks for visiting my blog and reading this article !

Read More

Oracle - Basic SELECT Statements

Hi Everyone,

Here are the basic Oracle queries.

Simple select statement

SELECT col1 [,col2, col3, ...] FROM tab1 [tab2, tab3, ...] WHERE condition1 [, condition2, condition3, ...]

selects all the record

SELECT * FROM emp;

selects records where empname is equal to shibin

SELECT * FROM emp WHERE empname='shibin';

selects the fields empname, salary from emp table

SELECT empname, salary FROM emp;

DISTINCT in SELECT Statement

SELECT DISTINCT empname FROM emp;

SELECT in Multiple Tables

SELECT e.empid, e.empname, d.deptname, e.salary FROM emp e, dept d WHERE  e.deptid=d.deptid;

IN clause in select

SELECT * FROM emp WHERE empname IN ('shibin','shine');

BETWEEN clause in slect

SELECT * FROM emp WHERE salary BETWEEN 500 AND 2500;

LIKE clause in select

SELECT * FROM emp WHERE empname LIKE 's%';

ORDER BY caluse in select

SELECT * FROM emp ORDER BY empname DESC

HAVING clause in select

SELECT deptid, SUM(salary) FROM emp GROUP BY deptid HAVING SUM(salary)>1000

Thanks for reading my Blog !

Read More

Microsoft SQL Server - Query to find the record counts of tables

Hi Everyone,

Use the below query to find the record count of tables.

SELECT

SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,

T.NAME AS TABLE_NAME,

SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME AS FULL_TABLE_NAME,

I.ROWS

FROM

SYS.TABLES AS T INNER JOIN SYS.SYSINDEXES AS I

ON T.OBJECT_ID = I.ID AND I.INDID < 2

WHERE

SCHEMA_NAME(T.SCHEMA_ID)='DBO'

AND T.NAME IN ('Table1','Table2','Table3','TableN')

Thanks for reading my Blog !

Read More

Microsoft SQL Server - Query to fetch Table Meta Data

Use the below query to fetch table metadata in Microsoft SQL Server.

SELECT

TABLE_SCHEMA,

TABLE_NAME,

ORDINAL_POSITION,

COLUMN_NAME,

CASE DATA_TYPE

WHEN 'DATETIME' THEN UPPER(DATA_TYPE)

ELSE

UPPER(DATA_TYPE)+'('+LTRIM(RTRIM(DATA_LENGTH))+')'

END AS DATA_TYPE,

NULLABLE

FROM

(

SELECT

COLS.TABLE_SCHEMA,

COLS.TABLE_NAME,

COLS.COLUMN_NAME,

COLS.ORDINAL_POSITION,

COLS.DATA_TYPE,

CASE COLS.DATA_TYPE

  WHEN 'CHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'VARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NVARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'SMALLINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'INT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'BIGINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'DECIMAL' THEN RTRIM(CONVERT(CHAR,COLS.NUMERIC_PRECISION)) + ',' +RTRIM(CONVERT(CHAR,COLS.NUMERIC_SCALE))

ELSE

NULL

END AS DATA_LENGTH,

CASE COLS.IS_NULLABLE

WHEN 'NO' THEN 'NOT NULL'

WHEN 'YES' THEN 'NULL'

END AS NULLABLE

FROM

INFORMATION_SCHEMA.COLUMNS COLS

WHERE COLS.TABLE_SCHEMA = 'DBO'

AND COLS.TABLE_NAME = 'EMPLOYEE'

)A

ORDER BY  TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME

Can also use IN Clause for COLS.TABLE_NAME to include multiple tables.

 Thanks for reading my Blog !

Read More