Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Advanced Oracle SQL Book ? (was: Reading column into a string variable)
hasta_l3_at_hotmail.com wrote:
> Sometimes, I am amazed by the results one can achieve
> with Oracle SQL extensions, such as analytic functions
> and sys_connect_by_path below...
>
> Would you recommand a *good* book on advanced Oracle SQL
> (not PL/SQL) illustrating the use of these extensions to solve
> actual problems ?
>
> The examples in the SQL reference manual are not bad,
> but - understandably - a bit too focused on the statement
> or function being specified.
>
> Thanks
>
> --- Raoul
The SQL Reference Manual, at least for the 10g R2 release is a good reference for the analytical functions, and includes a sample for each function. See chapter 5 (and possibly 6): http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf
I had previously read a handful of SQL reference manuals, including
"Special Edition Using SQL", "Oracle SQL High-Performance Tuning", and
"Joe Celko's SQL for Smarties", none of which discussed the Oracle
analytical functions. After seeing some of the short and very
efficient solutions posted by Michel Cadot, compared to some of the
less efficient non-analytical solutions that I generated for the same
problems, I took a couple days vacation and went through each function
described in chapters 5 and 6 and then created an example using the
functions that could be applied to the tables and columns in my
databases. It also helped to actively look for problems were the
analytical functions can be applied. For instance, after my post
yesterday to this thread, I decided to do the same concatenation of
rows, but for each employee department. This is what I came up with
(the WHERE clause has since been moved) is as follows:
SELECT
DEPARTMENT_ID,
EMPLOYEE_LIST
FROM
(SELECT
DEPARTMENT_ID,
SUBSTR(SYS_CONNECT_BY_PATH(ID,','),2) EMPLOYEE_LIST,
LEVEL CURRENT_EMP_COUNTER,
EMPLOYEE_DEPT_COUNT
FROM
(SELECT
ID, DEPARTMENT_ID, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY ID) ROW_POSITION, COUNT(ID) OVER (PARTITION BY DEPARTMENT_ID) EMPLOYEE_DEPT_COUNT FROM EMPLOYEE WHERE ACTIVE='Y' ORDER BY DEPARTMENT_ID, ID)
The rows to be included in the analytical functions can also be
specified, as in the following:
SELECT
FW.FISCAL_YEAR_WEEK, FW.FISCAL_YEAR, FW.FISCAL_MONTH,
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Tue Nov 14 2006 - 05:12:41 CST