Oracle Content: June 2013

To fetch ALTERNATE records from a table. (EVEN NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.select * from emp where rownum <= &n;
Select LAST n records from a tableselect * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);
How to delete duplicate rows in a table?delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
Count of number of employees in department wise.select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;
Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;
Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;
Select all record from emp where job not in SALESMAN or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');
Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';
Select all records where ename may be any no of character but it should end with ‘R’.

select * from emp where ename like'%R';
Count MGR and their salary in emp table.

select count(MGR),count(sal) from emp;
In emp table add comm+sal as total sal .

select ename,(sal+nvl(comm,0)) as totalsal from emp;
Select any salary <3000 from emp table.

select * from emp where sal> any(select sal from emp where sal<3000);
Select all salary <3000 from emp table.

select * from emp where sal> all(select sal from emp where sal<3000);
Select all the employee group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;
How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;
How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

Creating Matrix Reports

When you choose the Matrix report style, the wizard displays three new tab/wizard pages.

Ø Rows :-The field to be displayed vertically down the left side of the matrix; you can

choose multiple levels of rows to create a vertically nested matrix

Ø Columns :-The field values to be displayed horizontally across the top of the matrix;

you can choose multiple levels of columns to create a horizontally nested matrix.

Ø Cell:-The field value that becomes the cell, or cross-product, of the matrix.

Creating Matrix Summaries

ü When you choose a summary in the Summary page, the wizard creates three

summaries in the matrix.

Creating a Matrix for Each Group Record

ü Choose the Matrix with Group report style. This provides a similar group structure to

the Group Above report.

ü In your Regional report, you can select REG_NAME in the Group page so that the

wizard structures the Department/Title matrix for each record.

The differences from a nested matrix are:

§ Only the relevant Departments and Titles are displayed for each region.

§ If you create summaries for a Matrix with Group style, the wizard calculates and displays summaries for each group as well as the report total. The report total is displayed at the end of the report, in the bottom left corner.

Enhancing Matrix Reports

The Matrix Data Model

1) Cross product group.

2) Row and Column groups

3) Cell group.

4) Cell column is the source of a cross product summary that becomes the cell content.

5) Row and Column summaries.

ü To maintain and enhance the structure of a matrix report, you must understand the

Data Model objects and their interdependency.

Simple Matrix Style: The wizard creates three data groups:

• Department records (the rows)

• Job Title records (the columns)

• Salary records (the values used to calculate the matrix cells)

ü There is an extra group that surrounds the row and column groups. It is called the cross product group and contains the summary column that forms each cell value,

SumSalary, as well as the summaries to total the row and column values.

ü To create a cross product group, use the Cross Product tool in the vertical toolbar.

Matrix with Group:

ü The data model is identical, except for the addition of a break group, similar to a master/detail hierarchy.

ü The break group exists one level above, and outside, the matrix group.

ü The entire cross product group, with its contents, repeats for each record in the break group.

The Matrix Layout:

1 Repeating frame for rows (Down Direction).

2 Repeating frame for columns. (Across)

3 Matrix object, the intersection of the two repeating frames.

4 Matrix with Group style: Outer repeating frame for break group.

The layout for a Matrix report (The number of objects and their interdependency) is more complex than other styles because of the special relationship between the row and column groups and the cell values.

Simple Matrix Style : The wizard creates the following objects:

• One repeating frame with Print Direction of Down to hold the row values

• One repeating frame with Print Direction of Across to hold the column values

• A field for the summary of the cell values; the source is SumSalary, which

resides in the cross product group

• Fields for the row and column values, as well as for the row, column, and report

summaries if applicable

• A matrix object that corresponds to the cross product group; this object

intersects the row and column repeating frames, and contains the cell field.

Matrix with Group: The Matrix with Group style is identical except for the addition

of a repeating frame that surrounds the entire matrix layout and corresponds to the

break group in the Data Model.

Matrix Structure:

The Data Model and Layout Model of a matrix report contain special features, and

relationships between objects, to reflect the cross product structure of the report.

Matrix-Specific Objects and Properties

Matrix reports include special objects, and properties:

• Data Model: Cross Product group; Product Order property

• Layout Model: Matrix object.

Creating Matrix Summaries

1 Product Order = G_DEPT

2 Product Order = G_TITLE

3 Product Order =G_DEPT G_TITLE

When creating row or column summaries, or the summary for the cell values, use the

summary tool in the vertical toolbar, but ensure that you create the summaries inside

the cross product group (but outside the row and column groups).

PL/SQL TRIGGERS IN REPORTS

The Three Trigger Types

v Report level:

ü A set of five report-level triggers. Each trigger fires at a different stage of the report execution.

ü Access report triggers from the Report Triggers node in the Object Navigator.

v Data Model level:

ü Formula Column fires each time the column is processed.

ü Group filter fires for each record in the group.

ü Parameter validation fires when the run-time parameter form is displayed and when the user leaves the parameter field.

ü Access data model triggers in the property palette of a data model object—column, group, or parameter—or the corresponding layout object—field, repeating frame, or parameter field.

v Layout level: (format and action triggers are layout triggers).

ü Format triggers on most layout objects (excluding anchors). Each trigger fires as the layout object is processed.

ü Access format triggers in the property palette of a layout object.

Note: Report Builder does not allow data manipulation language (DML)

Commands—INSERT, DELETE, UPDATE—in layout format triggers.

Ø Different Triggers in Report Builder : -

1) Formula Trigger

2) Format Trigger

3) Validation Trigger

4) Action Trigger

5) Group Filter

6) Report Trigger

VALIDATION TRIGGER: - (validating a parameter value)

ü Validation triggers are PL/SQL functions used to validate the value of the parameter in a report.

ü The function that you specify as a validation trigger must return a Boolean value (T/F) based on whether the validation is successful or not.

ü If the function returns TRUE, the report is run. If the function returns FALSE, an error message is displayed and the report is not run.

In case of FALSE, the cursor remains in the parameter and the user has

The following two options.

1. Enter a different value.

2. Cancel the report.

Note: - you can test the value of a parameter, but you can’t change the value.

ACTION TRIGGER: -

ü These are pl/sql procedures executed when a button is select into the runtime previewer. These triggers can be used to call another report dynamically.

Types of Report Triggers in Report Builder and the sequence is ,

1) Before Parameter Form Trigger

2) After Parameter Form Trigger

3) Before Report Trigger

4) Between Pages Trigger

5) After Report Trigger

Write the code in following ways:

Ø Write validation trigger code for parameter (user or system) values validation under data model in obj.nav. --------> Return type is Boolean.

(Dbl click on parameter under user parameter in obj nav, go to properties click on validation trigger and write the code.)

Ø Write Group Filter Trigger code for Group under data model in obj.nav. --------> Return type is Boolean.

ü Dbl click on Group (Ex:- G_VOUCHER) in data model (or) in obj.nav , goto properties, in group node,

§ if filter type is PL/SQL, then click on PL/SQL Filter and write code.

§ If filter type is None, it displays all the records.

§ If filter type is First/Last, you must provide No. of records in property palette.

Note:- Filter types are ------>None, First, Last, PL/SQL.

ü Except PL/SQL type, in other three cases, PL/SQL Filter is not enabled.

Ø Write format trigger in formula cols.or database -----> Return type is Boolean .

Ø Write formula trigger for formula cols -----------> Return type is any.

Note :- For database cols, dbl click on col in paper layout model, in properties,

Advanced Layout node-------> click on Format Trigger.

For Formula cols, dbl click on col in paper layout model, in properties,

Placeholder/Formula node------> click on PL/SQL formula.

Ø Write in report triggers in obj.nav. -----> Return type is Boolean.

Note :- Don’t write the code in summary cols and place holder cols.

FORMULA TRIGGER CODE FOR NAME COLUMN IN FORMULA COLUMNS

Function NAMEFormula return Char is

A VARCHAR2(200);

B VARCHAR2(200);

begin

B:=:CNAME;

IF B='NEPL' THEN

A:='NARNE ESTATES PRIVATE LIMITED';

ELSIF B='NCPL' THEN

A:='NARNE CONSTRUCTIONS PRIVATE LIMITED';

ELSIF B='NAME' THEN

A:='NARNE AQUA MARINE ESTATES LIMITED';

ELSIF B='MSPL' THEN

A:='MARKET SPACE SYSTEMS PRIVATE LIMITED';

END IF;

RETURN(A);

end;

FORMULA TRIGGER CODE FOR TITLE COLUMN

Function TITLEFormula return Char is

A VARCHAR2(20);

B VARCHAR2(20);

C DATE;

D DATE;

F VARCHAR2(20);

E VARCHAR2(200);

begin

A:=:CNAME;

C:=:STDT;

D:=:EDDT;

F:=:BNAME;

E:=A||' '||F||' BANK BOOK'||' PAYMENT DETAILS FROM '||TO_CHAR(C,'DD/MM/YYYY')||' TO '||TO_CHAR(D,'DD/MM/YYYY');

RETURN(E);

end;

FORMULA TRIGGER CODE FOR CF_3 COLUMN

function CF_3Formula return Number is

x number(20);

begin

if :debit>:credit then

x:=:debit-:credit;

else

x:=null;

end if;

return x;

end;

Formula Trigger for Formula column BAL

function BALFormula return Char is

A DATE;

B VARCHAR2(50);

begin

A:=:TDT;

B:='Balances as on'||' '||TO_CHAR(A,'DD/MM/YYYY');

RETURN(B);

end;

Example of Foramt trigger to ENAME(database) column.

Function F_4 Format Trigger return Boolean is

Begin

If length(:ename)>4 then

Return(TRUE);

Else

Return(FALSE);

End if;

End;

Example of Format Trigger on Ename col ( hide the 3 emps based on condition).

Steps : -

ü In obj,nav create 3 user parameters (hide 3 enames).

ü Goto properties of each parameter.

ü Name---Pname1, Datatype---char, list of values----select ename from emp;

ü In layout editor, dbl click on ename col write format trigger code,

Begin

If :ename in(:pname1,:pname2,:pname3) then

Return (false);

Else

Return (true);

End if;

End;

Example of validation trigger to p_dno parameter.

SQL QUERY : - Select empno, ename, sal, deptno from emp where deptno=:p_dno;

function DNOValidTrigger return boolean is

begin

If :p_dno in(10,20,30) then

Return(TRUE);

Else

Srw.message(10,’please enter valid number’);

Return(FALSE);

End if;

End;

IMP NOTE :- SRW.message is accepts the error number and error message.

VALIDATION TRIGGER EXAMPLE

Is
begin

  IF UPPER(:DESTYPE)
= 'PRINTER'

  AND
:LAST_NAME_PARAM = 'SMITH' THEN

 
RETURN(TRUE);

ELSE

 
RETURN(FALSE);

  END
IF;

end;

Group Filter Trigger for group G_VOUCHER

If :comm. Is null then

Return (TRUE);

Else

Return (FALSE);

End if;

End;

Setting the border colors to the cols based on the condition.(psal is a parameter)

Begin

If :sal=:psal then

Srw.set_foregroun_border_color(‘red’);

Srw.set_border_pattern(‘solid’);

Srw.set_foreground_fill_color(‘magenta’);

Srw.set_fill_pattern(‘solid’);

End if;

Return(false);

End;

Oracle Content

Tuesday 18 June 2013

Oracle Interview Questions Part 8

How to display multiple column values in a Row

What is string Aggregation?

select

deptno,

listagg (ename, ',')

WITHIN GROUP

(ORDER BY ename) empnames

FROM

emp

GROUP BY

deptno

Monday 17 June 2013

Oracle Reports Material Part 6

Translate

Translate

Pages

About Me