Report output format [message #683811] |
Mon, 22 February 2021 23:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7b3b348e2499db847376d9b44e0a4be3?s=64&d=mm&r=g) |
varman
Messages: 2 Registered: February 2021
|
Junior Member |
|
|
hi, I need to insert a new line after every break column/group by column in oracle query, this has to handled in the query itself.
I tried setrecsep, but it is applying for all columns,my need is only when the break column changes.
Thanks
Sakthi
|
|
|
Re: Report output format [message #683812 is a reply to message #683811] |
Tue, 23 February 2021 00:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Something like that?
SQL> break on deptno skip 1
SQL> select deptno, ename from emp order by deptno, ename;
DEPTNO ENAME
---------- ----------
10 CLARK
KING
MILLER
20 ADAMS
FORD
JONES
SCOTT
SMITH
30 ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD
14 rows selected.
SQL> break on deptno dup skip 1
SQL> /
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
|
|
|
|
Re: Report output format [message #683830 is a reply to message #683821] |
Tue, 23 February 2021 08:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Afaik, you can't do it with a SQL*Plus feature but you can do something in SQL:
SQL> col deptn format a10
SQL> select decode(grouping(ename), 1,'----------', lpad(deptno,10)) deptn,
2 decode(grouping(ename), 1,'----------', ename) enam
3 from emp
4 group by rollup(deptno,ename)
5 having grouping(deptno) = 0
6 order by deptno, ename;
DEPTN ENAM
---------- ----------
10 CLARK
10 KING
10 MILLER
---------- ----------
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
---------- ----------
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
---------- ----------
17 rows selected.
|
|
|
Re: Report output format [message #683838 is a reply to message #683830] |
Tue, 23 February 2021 08:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/d29f577f753cb4b873212fd5ed0da4cd?s=64&d=mm&r=g) |
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
" this has to handled in the query itself."
The query itself only returns data. Period. Full stop. Formatting is handled by the client. The examples you've been shown use the sqlplus BREAK directive to tell sqlplus (your client) how to format. The query itself knows nothing about this.
|
|
|