OraFAQ Forum: Client Tools » Report output format

Home » Other » Client Tools » Report output format (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Report output format [message #683811]

Mon, 22 February 2021 23:28

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

Report message to a moderator

Re: Report output format [message #683812 is a reply to message #683811]

Tue, 23 February 2021 00:04

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Report output format [message #683821 is a reply to message #683812]

Tue, 23 February 2021 02:54

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

Report message to a moderator

Re: Report output format [message #683830 is a reply to message #683821]

Tue, 23 February 2021 08:20

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Report output format [message #683838 is a reply to message #683830]

Tue, 23 February 2021 08:50

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.

Report message to a moderator

Previous Topic:	Toad debug strange behavior
Next Topic:	Access Toad remotely

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 19:28:44 CST 2024