Hi Ami
Well there are some who would argue that pretty formatting isn't the job of
a relational SQL engine. You'd have to get the DB2 QMF add-on or some
visualisation tool like DBVis; or you could capture the output to file and
edit the format through awk or similar. SQL is a declarative language. A
result set is just that - a set of tuples. It's er, impure of ORACLE to go
down this path.

If you're determined to do this in standard SQL alone, you're reduced to
really ugly hacks involving, but not limited to, a SELECT of the character
literal for the carriage return or line feed on your operating system.
For example in UNIX:
select deptno,ename,sal, chr(10)
from emp
where sal < 2500
order by deptno

Now that will place a linefeed after each row - but you only want a linefeed
on a change of deptno; so you have to group by deptno, which forces you to
select a scalar or ranking or windowing function and then mask the output.
This will work but look at the extra processing and the self-join:

select deptno,ename,sal,
case when ename||digits(sal) = (select max(ename||digits(sal)) from
emp temp where deptno = temp.deptno)
then chr(10) else ' ' end
from emp
where sal < 2500
order by deptno,ename,sal

But no one should write code like that.
Regards
Alex Levy
Sustainable Software Ltd.


-----Original Message-----
From: Tsao Ami
[mailto:db2udbdba-ezmlmshield-x72546250.[Email address protected]
Sent: 15 May 2008 22:49
To: LazyDBA Discussion
Subject: FW: DB2 equivalent for Oracle SQL*Plus BREAK ON function?


Does anyone have a DB2 solution equivalent to this Oracle feature? In a
nutshell, we want to group the returned result set, separated by a blank
line.

thanks,
Ami

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 ON??





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page