WITH Clause in SELECT statement

I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITH clause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Generate list of dates and times

I want to create query returns list of dates and time for example

1-Jan-2012

1-Jan-2012 12:30:00 AM

1-Jan-2012 13:00:00 AM

1-Jan-2012 13:30:00 AM

1-Jan-2012 14:00:00 AM

.................

.................

1-Jan-2012 11:00:00 PM

1-Jan-2012 11:30:00 PM

To execute the previous requirement I can use the below query

   SELECT TO_DATE ('1-1-2012', 'DD-MM-RRRR') + (LEVEL - 1) / 48 DATE_TIME  
    FROM DUAL  
 CONNECT BY LEVEL <= 48;  

Footer in Ireport

I always print in report footer  some data about displaying date and pages counter like below image

I will explain how to execute this in Ireport

Cumulative Summary in Hierarchical Query using CONNECT_BY_ROOT

Today I will explain how to write hierarchical query, then modify it for getting cumulative summary function of every child using CONNECT_BY_ROOT which is supported within hierarchical queries.

For example I will create hierarchical query for employees and their manager, then display for every manager  how many  employees whose he manages cumulatively.

Ugly count(*)

I noticed at a lot of application that developers used count(*) in their code repeatably.

I don't encourage any developer to use count(*) as I called it "Ugly count(*)" as If you want to retrieve count of all result set regardless null values then use count(1)

I will explain why not using count(*)

Let's run below query against HR schema

select count(*) from employees;

It returns 108 and takes 73 msec to execute.

Then run below query against HR schema also

select count(1) from employees;

It returns 108 and takes 24 msec to execute.

I will try again to query count by primary key (EMPLOYEE_ID)

select count(EMPLOYEE_ID) from employees;

It return 108 and takes 25 msec to execute.

I will try again to query count by non primary key which have null value

select count(COMMISSION_PCT) from employees;

It return 36 and takes 17 msec to execute.

Note : Time of execution may differ in your machine.

Someone may ask question : Why count(*) take time more than count(1)?
The Answer : When I use * in select statement, Oracle internal treat it as Record Type. So it count against composite type and takes more time . While count(1) he counts against scalar type.

Conclusion

Always use count(1) if you need to count in whole result set.
Take care that count against column that has null values, He doesn't count null values.

Thanks
Mahmoud A. El-Sayed

Get Rows count in Whole Schema

Today I will produce function which returns count of rows in whole schema.

I can do this task using two solutions

Average on Dates Columns

SA,

As we know that is is impossible to calculate average on non numeric column.

SQL> select avg(hire_date) from scott.emp;

It will raise error

ORA-00932: inconsistent datatypes: expected NUMBER got DATE 

There is workaround to calculate avg on date columns.

Workaround is to convert date to julian date and then calculate average.

So the following is solution to make average on date column

SELECT TO_DATE(TRUNC(AVG( TO_CHAR(hire_date,'J'))),'J') FROM EMP;

Thanks

Oracle analytical functions - Part 2

That is part 2 that I will explain purpose of every analytic function I mentioned it in part 1

AVG

Returns a running average.

COUNT 

Returns a running count of all records or by partition.

FIRST

Returns the row ranked first using DENSE_RANK.

Syntax :-

aggregate_function(column_name) KEEP
(DENSE_RANK FIRST ORDER BY <column_name> [<ASC|DESC> NULLS <FIRST|LAST> ])

Oracle analytical functions - Part 1

Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Summary Functions in ADF Business Component

SA,
I will present how to create summary function (sum, count, min, max and avg)   in ADF BC and clarify how these can be used at Entity level and View level. That is done using Groovy syntax.
I will use scott schema (EMP and DEPT Tables)

Entity Level

We will create example to get employees count in every departments.
First, we need to have association between entities representing master-detail relationship and the destination accessor name is what we are going to use in our groovy

Syntax: <Accessor>.aggregate_function(Groovyexpression)