Navicat carriage return in results

Question

I am running a query on my database with Navicat and I am using the ORDER BY CASE to print out the results in a specific order. I would like to insert a carriage return between each result, but I am not finding any information on how to do that. Ideally, I would like to have a record returned, a blank row of cells, then the next record printed below that.

Any help would be greatly appreciated.

Answer 1

Probably as many other community members, I don't use Navicat. I Googled for it - looks like GUI tools I've seen (SQL Developer, TOAD) with nice features. I doubt it is capable of doing what you want out of the box.

You didn't say what you plan to do with such an output - maybe spool it into a (CSV?) file? If so, there's a workaround: use SQL*Plus whose SET RECSEP setting does exactly that, with no effort at all.

This is default:

SQL> select * from dept order by dname, loc;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        40 OPERATIONS     BOSTON
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO

Setting I mentioned:

SQL> set recsep each
SQL> select * from dept order by dname, loc;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

        40 OPERATIONS     BOSTON

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO


SQL>

Cool, isn't it?

---

Otherwise, a workaround that doesn't exactly add carriage return, but adds empty rows. To do that, I used several CTEs:

• your_query is your (my) current query with two additional columns:
  • rn which returns row number, sorted by column(s) you want
  • cnt which returns total number of rows returned by your query
• counter is a row generator, generating as many rows as your query returns (in my example, my query returned 4 rows; counter creates 4 rows as well)
• temp unions these two queries:
  • your query is trivial
  • it is unioned with query that returns rn from counter, while other columns are null. Visually, it'll return result you wanted (empty rows)
• finally, select only significant columns sorted by rn

---

SQL> with
  2  your_query as
  3    (select deptno, dname, loc,
  4       --
  5       row_number() over (order by dname, loc) rn,
  6       count(*) over () cnt
  7     from dept
  8    ),
  9  counter as
 10    (select level rn
 11     from dual
 12     connect by level <= (select cnt
 13                          from your_query
 14                          where rownum = 1
 15                         )
 16    ),
 17  temp as
 18    (select a.rn, a.deptno, a.dname, a.loc
 19     from your_query a
 20     union all
 21     select b.rn, null, null, null
 22     from counter b
 23    )
 24  select deptno, dname, loc
 25  from temp
 26  order by rn, deptno;

Result:

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

        40 OPERATIONS     BOSTON

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO


8 rows selected.

SQL>

If you wonder how it looks in GUI, here it is (from SQL Developer):