ORACLE First / last of Month / quarter

select
trunc(sysdate,'MM') as FIRST_OF_MONTH
from dual;

select
trunc(LAST_DAY(sysdate)) as LAST_OF_MONTH
from dual;

select
trunc(sysdate,'Q') as FIRST_OF_QUARTER
from dual;

select
add_months( trunc(sysdate,'Q'),3) - 1 as LAST_OF_QUARTER
from dual;

Till Next Time

ORACLE right function

A good alternative:

substr(COLUMN_NAME, - integer)

Till Next Time

ORACLE Model clause

Played around with the model clause to normalize the EMP hiarchy:
Normal connect by prior:

SELECT 'org_name' AS organisation,
LEVEL AS org_level,
ename FROM emp
CONNECT BY PRIOR empno = mgr
START WITH ename = 'KING'

ORGANISA ORG_LEVEL ENAME
-------- ---------- ----------
org_name 1 KING
org_name 2 JONES
org_name 3 SCOTT
org_name 4 ADAMS
org_name 3 FORD
org_name 4 SMITH
org_name 2 BLAKE
org_name 3 ALLEN
org_name 3 WARD
org_name 3 MARTIN
org_name 3 TURNER
org_name 3 JAMES
org_name 2 CLARK
org_name 3 MILLER

with model clause:

select organisation,
level1, level2, level3, level4 from
( SELECT 'org_name' AS organisation, LEVEL AS org_level, ename
FROM emp CONNECT BY PRIOR empno = mgr
START WITH ename = 'KING')
model
return updated rows
partition by
(organisation)
dimension by (rownum rn)
measures (lpad(' ',10) level1,
lpad(' ',10) level2, lpad(' ',10) level3,
lpad(' ',10) level4, org_level, ename)
rules update
( level1[ANY] = case when org_level[CV()] = 1 then ename [cv()] end,
level2[any] = case when org_level[CV()] = 2 then ename [cv()] end,
level3[any] = case when org_level[cv()] = 3 then ename [cv()] end,
level4[any] = case when org_level[cv()] = 4 then ename [cv()] end )

ORGANISA LEVEL1 LEVEL2 LEVEL3 LEVEL4
-------- ---------- ---------- ---------- ----------
org_name KING
org_name JONES
org_name SCOTT
org_name ADAMS
org_name FORD
org_name SMITH
org_name BLAKE
org_name ALLEN
org_name WARD
org_name MARTIN
org_name TURNER
org_name JAMES
org_name CLARK
org_name MILLER

Let's fill the blanks:

select organisation,
level1, nvl(level2,level1) as level2, nvl(level3,nvl(level2,level1)) as level3,
nvl(level4,nvl(level3,nvl(level2,level1))) as level4 from (
select organisation, last_value(level1 ignore nulls) over (order by rownum) as
level1 , last_value(level2 ignore nulls) over (order by rownum) as level2 ,
decode(level4,null,level3,last_value(level3 ignore nulls) over (order by
rownum)) as level3, level4 from ( select organisation, level1 ,level2,level3,level4 from ( SELECT 'org_name' AS organisation, LEVEL AS
org_level, ename FROM emp CONNECT BY PRIOR empno = mgr START WITH ename = 'KING')
model
return updated rows
partition by (organisation)
dimension by (rownum rn)
measures (lpad(' ',10) level1, lpad(' ',10)
level2, lpad(' ',10) level3,lpad(' ',10) level4, org_level, ename)
rules
update
( level1[ANY] = case when org_level[CV()] = 1 then ename [cv()] end,
level2[any] = case when org_level[CV()] = 2 then ename [cv()] end,
level3[any] = case when org_level[cv()] = 3 then ename [cv()] end,
level4[any] = case when org_level[cv()] = 4 then ename [cv()] end
)))
;

ORGANISA LEVEL1 LEVEL2 LEVEL3 LEVEL4
-------- ---------- ---------- ---------- ----------
org_name KING KING KING KING
org_name KING JONES JONES JONES
org_name KING JONES SCOTT SCOTT
org_name KING JONES SCOTT ADAMS
org_name KING JONES FORD FORD
org_name KING JONES FORD SMITH
org_name KING BLAKE BLAKE BLAKE
org_name KING BLAKE ALLEN ALLEN
org_name KING BLAKE WARD WARD
org_name KING BLAKE MARTIN MARTIN
org_name KING BLAKE TURNER TURNER
org_name KING BLAKE JAMES JAMES
org_name KING CLARK CLARK CLARK
org_name KING CLARK MILLER MILLER

Till Next Time

ORACLE to_number error 9I

Ran into a strange problem today. Somehow the exectution plan on a 9I box was different then before and it started to trow ORA-01722: invalid number errors.... This was caused by the fact that a to_number conversion was done after a the where instead of before. The workaround is to force the database to do the conversion first. This can be done by using a DECODE statement:

DECODE ((REPLACE (TRANSLATE (TRIM (number_as_string), '0123456789', '0000000000'
), '0', NULL ) ), NULL, TO_NUMBER (TRIM (number_as_string)) ) string_as_number.

Till Next Time

ORACLE Collect Function

Up to 9i you had to use Tom Kyte's Stragg function to get the children of the level to a single string. From 10GR1 you can use the collection function. Based on this article: http://www.oracle-developer.net/display.php?id=306 I played around with this function.

First make our own table type:

CREATE OR REPLACE TYPE collect_table AS TABLE OF VARCHAR2 (4000);

SELECT deptno, CAST (COLLECT (distinct ename) AS collect_table) AS emps
FROM emp
GROUP BY deptno;

Now make a collection to string conversion function:

CREATE OR REPLACE FUNCTION collect_to_string (
nt_in IN collect_table,
delimiter_in IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2
IS
v_idx PLS_INTEGER;
v_str VARCHAR2 (32767);
v_dlm VARCHAR2 (10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL
LOOP
v_str := v_str v_dlm nt_in (v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT (v_idx);
END LOOP;
RETURN v_str;
END collect_to_string;

SELECT deptno,
collect_to_string (CAST (COLLECT (distinct ename order by ename desc)
AS collect_table) ,',')AS emps
FROM emp
GROUP BY deptno;

Till Next Time

Welcome to Oracle101

Hi,

My name is John Minkjan and I'm a senior BI-consultant at CIBER in the Netherlands. I reserverd a couple of ...101 blogspots. At the moment I'm mostly active on OBIEE101. I will start posting here as soon as my focus in back on Oracle databases.

Till Next Time

John