SQL WM_CONCAT FUNCTION

SQL WM_CONCAT FUNCTION

This function will return the select column values concatenated in single line

Example 1: 

SELECT    WM_CONCAT (DISTINCT EMPLID)
FROM    PS_JOB WHERE DEPTID=’XXX’;

WM_CONCAT(DISTINCTENAME)
---------- -----------------------
CLARK,KING,MILLER

Example 2: 

SELECT    DEPTID, COUNT (*),   WM_CONCAT (DISTINCT EMPLID)
FROM    PS_JOB GROUP BY    DEPTID;

DEPTNO  COUNT   WM_CONCAT(DISTINCTENAME)
---------- --------   --------------------------------
10                3        CLARK,KING,MILLER
20                5        ADAMS,FORD,JONES,SCOTT,SMITH
30                6        ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

TRUNC - Truncate Datetime - Oracle

TRUNC - Truncate Datetime - Oracle 

In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e)

Syntax	TRUNC(datetime [, unit])
Default Unit	'DD' truncates to day (sets zero time)

TRUNC function supports the following truncation units (full list...):

Oracle TRUNC Unit		Truncation	Result
'DD'	'DDD'	Day	YYYY-MM-DD 00:00:00
'MM'	'MONTH'	Month	YYYY-MM-01 00:00:00
'YY'	'YEAR'	Year	YYYY-01-01 00:00:00
'HH'	'HH24'	Hour	YYYY-MM-DD HH:00:00
'MI'		Minute	YYYY-MM-DD HH:MI:00

Example: In PeopleSoft below statement will all employee record where effective date is same as last update datetime, TRUNC method without any parameter will result the date value.

SELECT * FROM PS_JOB WHERE EFFDT = TRUNC(LASTUPDDTTM);