DB2 equivalent for ORACLE SQL*Plus BREAK???

[kg6ypx]

Greetings!

This is an excerpt from the Oracle documentation:"...ORACLE SQL*Plus
BREAK command creates a subset of records and add space and/or summary
lines after each subset. The column you specify in a BREAK command is
called a break column which suppresses duplicate values. For example

SQL> BREAK ON DEPTNO SKIP 1 // To insert a blank line
SQL> SELECT DEPTNO, ENAME, SAL
2 FROM EMP
3 WHERE SAL < 2500
4 ORDER BY DEPTNO;

..."
Is there a DB2 equivalent for BREAK?

Thanks for your help.

Al.

[jefftyzzer]

Al,

I'm afraid there is no such DB2 CLP command--the CLP is not the report-
writing tool that SQL*Plus began life as. You'll have to apply a
technique such as the one described on page 381 in Anthony Molinaro's
_SQL Cookbook_ (O'Reilly).

Regards,

--Jeff

[Dave Hughes]

kg6ypx wrote:

If summary lines after groups of rows is what you're interested in,
have a lookup at the grouping-sets and super-group functionality in
DB2. You can find it in the 9.5 InfoCenter here (search for the
"group-by-clause" header and read from there):

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.ref.doc/doc/r0000875.html

Not to worry if you're on an earlier version than 9.5 - it's old
functionality that's been around for a long time (pre v7 I think).

Here's one of the examples illustrating GROUP BY ROLLUP:

Example C3: If you use the 3 distinct columns involved in the grouping
sets of Example C2 and perform a ROLLUP, you can see grouping sets for
(WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) and grand total.

SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON, SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY ROLLUP (WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON)
ORDER BY WEEK, DAY_WEEK, SALES_PERSON

This results in:

WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 6 GOUNOT 11
13 6 LEE 12
13 6 LUCCHESSI 4
13 6 - 27
13 7 GOUNOT 21
13 7 LEE 21
13 7 LUCCHESSI 4
13 7 - 46
13 - - 73
- - - 73

You can use the GROUPING function to determine whether a particular row
is the result of a grouping-set. This is documented here:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.ref.doc/doc/r0000761.html

There's no built-in method I know of to add blank lines to the output
of a CLP query (as Jeff points out, it's not a reporting tool).
However, given the above it wouldn't be too tricky to write a little
app that inserted a line break before each row where GROUPING() returns
1.

Cheers,

Dave.

[kg6ypx]

Jeff/Dave-

Thanks for your help. I am going to go buy Anthony Molinaro's "SQL
Cookbook" (O'Reilly).

Thanks again.

Al.

[Pegaz]

> Not to worry if you're on an earlier version than 9.5 - it's old
> functionality that's been around for a long time (pre v7 I think).
>
> Here's one of the examples illustrating GROUP BY ROLLUP:
>
> Example C3: If you use the 3 distinct columns involved in the grouping
> sets of Example C2 and perform a ROLLUP, you can see grouping sets for
> (WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) and grand total.
>
> SELECT WEEK(SALES_DATE) AS WEEK,
> DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
> SALES_PERSON, SUM(SALES) AS UNITS_SOLD
> FROM SALES
> WHERE WEEK(SALES_DATE) = 13
> GROUP BY ROLLUP (WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON)
> ORDER BY WEEK, DAY_WEEK, SALES_PERSON
>

> [...]
>
> Cheers,
>
> Dave.

What a pity! ROLLUP doesn't work on DB2 for iSeries. Do you know some
replacement clause or tips for that ?

best regards
Pegaz.

[jefftyzzer]

Probably an ungainly series of UNIONS, e.g.,

SELECT fine-grained SUM for group A
UNION
SELECT next-highest level SUM for group A
UNION
SELECT highest level SUM for group A
UNION
SELECT fine-grained SUM for group B
UNION
SELECT next-highest level SUM for group B
UNION
SELECT highest level SUM for group B
.
.
.

-- Jeff

[Tonkuma]

Download a Summary of the DB2 for i 6.1 (i5/OS V6R1) Enhancements (109
KB):
http://www-03.ibm.com/systems/i/software/db2/pdf/db2_v6r1_enhancements.pdf

On January 29, 2008, IBM announced a new version of i5/OS. Version 6
Release 1 (V6R1) of i5/OS includes an updated version of DB2 for i5/
OS, which is built into the operating system. In addition, several new
products are available for DB2 for i5/OS in a similar timeframe as
V6R1.

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

DB2 for i5/OS SQL Syntax support for relational OLAP implementations
is significantly enhanced with the Grouping Sets and Super Groups
support. Grouping Sets and Super Groups (via CUBE and ROLLUP) allow a
user to group and aggregate data in multiple ways in a single
query. .......

Additional Information on DB2 for i5/OS:
DB2 for i5/OS Home Page:
http://www.ibm.com/systems/i/db2
DB2 Web Query for System i:
http://www.ibm.com/systems/i/db2/webquery