BusinessObjects-How to find which report is running from database session

Sometimes you see very large SQL expressions which are killing your database systems. With audit reports you can find which reports have run after they finished. But with this little code you can directly find the user, the report and the universe from your database directly on demand.

http://www.dallasmarks.org/blog/2011/10/tips-and-tricks-identifying-business-objects-queries-using-end_sql/

Just put this lines to your universe parameters "END_SQL" line:
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

Thats it. Your query will look like this:

Your dba will appreciate you.

BusinessObjects-How to use Oracle hints in Universe

If you use an Oracle connection in your universe and put the tables from the table browser, you can't use hints with your table. You can use derived tables for use of Oracle hints.

But there is another way put hints to your queries. Just go to your universe. Create an object with your rule. For example:

There is a "1" at the end. Because BO puts a comma sign after each object by generating the sql. Therefore we put a dummy value.

Ok. Lets get a look after we export our universe and use the hint object. (Don't forget to use this object as the FIRST object in Webi)

ORACLE-Transpose Column Value to Row in Oracle

You have a table with various columns. One of your column is a text column where the values are seperated with semicolon (";"). You want to transpose this column values into rows.

Generally we use instr function for this kind of purposes. But there is a another way to do this.

Let's suppose that your select statement is like this:

SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL

the output:

and our new statement:

WITH T AS(
SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL)
SELECT TXT FROM T
MODEL
RETURN UPDATED ROWS
PARTITION BY(ROWNUM RN)
DIMENSION BY (0 POSITION)
MEASURES (TXT,NVL(LENGTH(REGEXP_REPLACE(TXT,'[^;]+','')),0) NB_MOT)
RULES
(TXT[FOR POSITION FROM 1 TO NB_MOT[0] INCREMENT 1] = REGEXP_SUBSTR(TXT[0],'[^;]+',1,CV(POSITION)))

and the output:
 

Tweet

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

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-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