ORACLE-Find the Time Difference Between Two Steps

Suppose that you have a table which has only one time column. This table is used for collect the datetime for each steps of your transactions. (Eg. step started, step in progress, step closed for each transaction) In this example below the Pkey column is the unique key for each transaction and the second column is the start time for it.

pkey	datecreated
10	06/02/2007 10:28:25
10	06/02/2007 11:28:25
10	06/02/2007 12:28:25
20	06/02/2007 10:28:25
20	06/02/2007 09:28:25
20	06/02/2007 10:20:25
30	06/02/2007 10:28:25
30	07/02/2007 10:28:25
30	07/02/2007 01:28:25

solution 1 :


SELECT u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) u,
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1


solution 2 :


WITH std AS
(SELECT e.*,row_number() over (PARTITION BY pkey ORDER BY e.datecreated) orderno FROM table e)
(SELECT
u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM std u , std g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1
)


solution 3 :


SELECT pkey, datecreated,
TRUNC(((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)/7) weeks,
TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24) days,
TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)-24*(TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)) hours,
TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)-60*(TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)) minutes,
TRUNC(86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))-60*(TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)) seconds
FROM table

ORACLE-Cumulative Sum at the Same Line

You can sum the total of a measure in the same line for each section in your row data. For example:

select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal,ename) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept,
sum(sal) over (order by deptno, sal) CumTot,
sum(sal) over () TotSal
from emp
order by deptno, sal

and the result looks like this:

deptno	ename	sal	CumDeptTot	SalByDept	CumTot	TotSal
10	John	1300	1300	3400	1300	11300
10	Michael	1100	2400	3400	2400	11300
10	Robert	1000	3400	3400	3400	11300
20	Vicky	1200	1200	3550	4600	11300
20	Sally	1250	2450	3550	5850	11300
20	Harry	1100	3550	3550	6950	11300
30	Pavel	1400	1400	4350	8350	11300
30	Dennis	1400	2800	4350	9750	11300
30	Barbie	1550	4350	4350	11300	11300

BusinessObjects-Dont' Ask for Username and Password by Opening Olap Reports

After creating an Olap Report the deafult setting is ask for username and password for each logon. To prevent this and run the report directly is simple.

Go to the Central Management Console. Find your Olap Report from "Objects". Click on to it and go to "Data Source Logon" tab. Select "Logon using specific credentials"

ORACLE-Merge Command

You can use the Merge command to insert and update at the same time with one query. So you don't need to write a procedure which includes insert, exception and update.

Use of the command;

Merge Into Table1 T1

Using (select * from Table2 where ...) T2

On (T1.Field1 = T2.Field1 And ...)

When Matched Then

Update

Set T1.Field2 = T2.Field2

When Not Matched Then

Insert (T1.Field2) Values (T2.Field2)

Table2 is our source table. Table1 is our destination table.

The fields in the "on" clause are the unique fields for T1 and works like a where clause.

ORACLE-Delete Same Multiple Rows Except One

To delete all the rows which have the same data except one.

(In this example we suppose that your table has same data in ID1 and ID2 columns.)

delete from t1 A

where rowid != ( select min(rowid) from t1 B

where B.ID1 = A.ID1

and B.ID2 = A.ID2)

ORACLE-Get the Values from a Long Data Type Column

DECLARE
long_var LONG;
var_var VARCHAR2(2000);
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
var_var := substr(long_var,1,2000);
INSERT INTO table_b
VALUES (var_var);
END;

ORACLE-Find Non-Numeric Rows in a Table

Sometimes you want to alter your table. For example you want to change the data type from varchar to numeric. But many times some rows prevents you to doing this.

Find the non-numeric rows;

select nvl(length(translate(trim(column_name),' +-.0123456789',' ')),0) from table_name

where nvl(length(translate(trim(column_name),' +-.0123456789',' ')),0) > 0

tip: by equal the where clause to 0 (zero) you can find the numeric columns.

ORACLE-Create a Calendar Table

Create a table named X which should include numbers from 0 to 9. Let say vals as the column name.

so;

create table calendar as

select (a.vals + b. vals*10 + c. vals*100 + d. vals*1000 + e. vals*10000 )+

to_date('01.01.1900','dd.mm.yyyy') date from x a, x b, x c, x d, x e

this will create a table from 01.01.1900 to 10^5 days after.

ORACLE-Find Number of Character in a String

For example, find how many "|" is in "abc|123|wer|123|bnm".

select length('abc|123|wer|123|bnm')-length(replace('abc|123|wer|123|bnm','|','')) from dual