Home » SQL & PL/SQL » Client Tools » Report output format (Oracle 11g)
Report output format [message #683811] Mon, 22 February 2021 23:28 Go to next message
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 messageGo to next message
Michel Cadot
Messages: 67885
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 #683821 is a reply to message #683812] Tue, 23 February 2021 02:54 Go to previous messageGo to next message
varman
Messages: 2
Registered: February 2021
Junior Member
Thank you..!!The first output is what I expected, the only thing is I need to put a visible line instead of blank space after break on dept id. Is that possible with break statement?



Regards
Sakthi
Re: Report output format [message #683830 is a reply to message #683821] Tue, 23 February 2021 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67885
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
EdStevens
Messages: 1321
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.
Previous Topic: Toad debug strange behavior
Next Topic: Access Toad remotely
Goto Forum:
  


Current Time: Mon Jun 21 11:36:46 CDT 2021