The GROUPING_ID Function in Font

Creating DataMatrix in Font The GROUPING_ID Function

The GROUPING_ID Function
Encode Data Matrix 2d Barcode In None
Using Barcode encoder for Font Control to generate, create DataMatrix image in Font applications.
www.OnBarcode.com
UCC.EAN - 128 Creation In None
Using Barcode drawer for Font Control to generate, create GS1 128 image in Font applications.
www.OnBarcode.com
The GROUPING_ID function is more flexible that the GROUPING function, because it can return several different results, as you can see in Listing 8-40. Listing 8-40. GROUPING_ID Function Example with ROLLUP SQL> 2 3 4 5 6 7 8 9 select , deptno case GROUPING_ID(deptno, job) when 0 then job when 1 then '**dept **' when 3 then '**total**' end job , count(empno) headcount from employees group by rollup(deptno, job);
Data Matrix Generator In None
Using Barcode generation for Font Control to generate, create Data Matrix ECC200 image in Font applications.
www.OnBarcode.com
Paint Barcode In None
Using Barcode creation for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
CHAPTER 8 RETRIEVAL: MULTIPLE TABLES AND AGGREGATION
QR-Code Creation In None
Using Barcode creator for Font Control to generate, create Denso QR Bar Code image in Font applications.
www.OnBarcode.com
Barcode Printer In None
Using Barcode encoder for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
DEPTNO -------10 10 10 10 20 20 20 30 30 30 30
Code 39 Extended Creator In None
Using Barcode maker for Font Control to generate, create Code 39 Full ASCII image in Font applications.
www.OnBarcode.com
Paint ISSN - 13 In None
Using Barcode generator for Font Control to generate, create ISSN - 10 image in Font applications.
www.OnBarcode.com
JOB HEADCOUNT --------- --------ADMIN 1 MANAGER 1 DIRECTOR 1 **dept ** 3 MANAGER 1 TRAINER 4 **dept ** 5 ADMIN 1 MANAGER 1 SALESREP 4 **dept ** 6 **total** 14
Drawing Data Matrix 2d Barcode In Visual C#.NET
Using Barcode generation for Visual Studio .NET Control to generate, create Data Matrix ECC200 image in Visual Studio .NET applications.
www.OnBarcode.com
Data Matrix 2d Barcode Recognizer In C#.NET
Using Barcode decoder for VS .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
12 rows selected. SQL> You may be puzzled by the value 3 being used on the fifth line in Listing 8-40. Things become clear when you convert 3 to a binary representation, which results in the binary number 11. The two ones in this number act as a flag to trap the situation in which both columns contain a null value. GROUP BY ROLLUP can produce only 1 (binary 01) and 3 (binary 11), but GROUP BY CUBE can also generate 2 (binary 10). Look at the results in Listing 8-41. Obviously, GROUPING_ID produces a 0 (zero) for all regular rows in the result. Listing 8-41. GROUPING_ID Function Example with CUBE SQL> 2 3 4 select , from group by deptno, job GROUPING_ID(deptno, job) gid employees cube(deptno, job);
Create Code 128 Code Set B In Visual Studio .NET
Using Barcode generator for ASP.NET Control to generate, create Code 128A image in ASP.NET applications.
www.OnBarcode.com
Paint GS1-128 In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create UCC.EAN - 128 image in ASP.NET applications.
www.OnBarcode.com
DEPTNO JOB GID -------- -------- -------3 ADMIN 2 MANAGER 2 TRAINER 2 DIRECTOR 2 SALESREP 2 10 1 10 ADMIN 0 10 MANAGER 0 10 DIRECTOR 0 20 1
Painting Barcode In Java
Using Barcode printer for BIRT reports Control to generate, create Barcode image in Eclipse BIRT applications.
www.OnBarcode.com
Decode UCC - 12 In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
CHAPTER 8 RETRIEVAL: MULTIPLE TABLES AND AGGREGATION
UCC.EAN - 128 Decoder In Visual Basic .NET
Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
Barcode Creator In Visual Studio .NET
Using Barcode generator for ASP.NET Control to generate, create Barcode image in ASP.NET applications.
www.OnBarcode.com
20 20 30 30 30 30
European Article Number 13 Generation In Java
Using Barcode drawer for BIRT Control to generate, create GS1 - 13 image in BIRT reports applications.
www.OnBarcode.com
Print 1D Barcode In VB.NET
Using Barcode creation for .NET Control to generate, create 1D image in .NET applications.
www.OnBarcode.com
MANAGER TRAINER ADMIN MANAGER SALESREP
Code 3 Of 9 Scanner In .NET Framework
Using Barcode scanner for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Code 3 Of 9 Creation In Java
Using Barcode creation for Android Control to generate, create ANSI/AIM Code 39 image in Android applications.
www.OnBarcode.com
0 0 1 0 0 0
17 rows selected. SQL>
8.9 Partitioned Outer Joins
We discussed outer joins in Section 8.4. This section introduces partitioned outer joins. To explain what partitioned outer joins are, let s start with a regular (right) outer join in Listing 8-42. Listing 8-42. Regular Right Outer Join Example SQL> break on department skip 1 on job SQL> 2 3 4 5 6 7 8 select , , from d.dname as department e.job as job e.ename as employee employees e right outer join departments d using (deptno) by department, job; EMPLOYEE -------MILLER KING CLARK <<< ADMIN JONES MANAGER BLAKE SALESREP ALLEN WARD TURNER MARTIN
order
DEPARTMENT JOB ---------- -------ACCOUNTING ADMIN DIRECTOR MANAGER HR SALES
CHAPTER 8 RETRIEVAL: MULTIPLE TABLES AND AGGREGATION
TRAINING
MANAGER TRAINER
JONES SMITH FORD ADAMS SCOTT
15 rows selected. SQL> The SQL*Plus BREAK command allows you to enhance the readability of query results. In Listing 8-42, we use the BREAK command to suppress repeating values in the DEPARTMENT and JOB columns, and to insert an empty line between the departments. (See 11 for details about BREAK.) The result shows 15 rows, as expected. We have 14 employees, and the additional row (marked with <<<) is added by the outer join for the HR department without employees. Look at Listing 8-43 to see what happens if we add one extra clause, just before the RIGHT OUTER JOIN operator. Listing 8-43. Partitioned Outer Join Example SQL> 2 3 4 5 6 7 8 9 select , , from d.dname as department e.job as job e.ename as employee employees e PARTITION BY (JOB) right outer join departments d using (deptno) by department, job; EMPLOYEE -------MILLER KING CLARK <<< <<< <<< <<< <<< <<< <<<
order
DEPARTMENT JOB ---------- -------ACCOUNTING ADMIN DIRECTOR MANAGER SALESREP TRAINER HR ADMIN DIRECTOR MANAGER SALESREP TRAINER
CHAPTER 8 RETRIEVAL: MULTIPLE TABLES AND AGGREGATION
SALES
ADMIN JONES DIRECTOR MANAGER BLAKE SALESREP ALLEN WARD TURNER MARTIN TRAINER ADMIN DIRECTOR MANAGER JONES SALESREP TRAINER SMITH FORD ADAMS SCOTT
<<< <<< <<< <<<
TRAINING
26 rows selected. SQL> Listing 8-43 shows at least one row for each combination of a department and a job. Compared with Listing 8-42, the single row for the HR department is replaced with 12 additional rows, highlighting all nonexisting department/job combinations. A regular outer join considers full tables when searching for matching rows in the other table. The partitioned outer join works as follows: 1. Split the driving table in partitions based on a column expression (in Listing 8-43, this column expression is JOB). 2. Produce separate outer join results for each partition with the other table. 3. Merge the results of the previous step into a single result. Partitioned outer joins are especially useful when you want to aggregate information over the time dimension, a typical requirement for data warehouse reporting. See Oracle SQL Reference for more details and examples.
Copyright © OnBarcode.com . All rights reserved.