14 Replies Latest reply on Mar 1, 2012 8:59 AM by user12957777

    Printing a blank line??

    636453
      Hello!! :)

      I have a table called employees, it has the following column:

      JOBTITLE
      -----------------
      Accountant
      Accountant
      Accountant
      Accountant
      Clerk
      Clerk
      Salesman
      Salesman
      Salesman
      Salesman
      Salesman

      What i would like to do is print a blank like between the differents groups, i.e:

      Accountant
      Accountant
      Accountant
      Accountant

      Clerk
      Clerk

      Salesman
      Salesman
      Salesman
      Salesman
      Salesman
      *************************

      Any ideas anyone??

      Thanks
        • 1. Re: Printing a blank line??
          442119
          In SQL*Plus:
          if your query returns the data as below:

          select * from
          (select 'Accountant' job from dual union all
          select 'Accountant' job from dual union all
          select 'Accountant' job from dual union all
          select 'Accountant' job from dual union all
          select 'Clerk' job from dual union all
          select 'Clerk' job from dual union all
          select 'Salesman' job from dual union all
          select 'Salesman' job from dual union all
          select 'Salesman' job from dual union all
          select 'Salesman' job from dual union all
          select 'Salesman' job from dual)

          JOB
          ----------
          Accountant
          Accountant
          Accountant
          Accountant
          Clerk
          Clerk
          Salesman
          Salesman
          Salesman
          Salesman
          Salesman


          If you set BREAK clause before running the query as below, you will get an empty line :

          SQL> break on job duplicates skip 1
          SQL> l
          1 select * from
          2 (select 'Accountant' job from dual union all
          3 select 'Accountant' job from dual union all
          4 select 'Accountant' job from dual union all
          5 select 'Accountant' job from dual union all
          6 select 'Clerk' job from dual union all
          7 select 'Clerk' job from dual union all
          8 select 'Salesman' job from dual union all
          9 select 'Salesman' job from dual union all
          10 select 'Salesman' job from dual union all
          11 select 'Salesman' job from dual union all
          12* select 'Salesman' job from dual)
          SQL> /

          JOB
          ----------
          Accountant
          Accountant
          Accountant
          Accountant

          Clerk
          Clerk

          Salesman
          Salesman
          Salesman
          Salesman
          Salesman


          11 rows selected.

          SQL>


          Regards,
          Raj
          • 2. Re: Printing a blank line??
            500057
            Have you tried SQL*Plus BREAK command.

            Shailender Mehta
            • 3. Re: Printing a blank line??
              636453
              Hi guys,

              thanks!!!

              At the moment im using toad - How do i get the break to work?? also i dont want to delete any repeating fields, jus a line break between the change of departments!!!
              • 4. Re: Printing a blank line??
                647253
                Here is a (potentially ugly/non-performant[?]) SQL-only solution....but I think you're answer may lie in the MODEL clause (10g and above) but I haven't used that in anger :-)
                with tmp as (
                select 'Accountant' as d from dual union all
                select 'Accountant' as d from dual union all
                select 'Accountant' as d from dual union all
                select 'Accountant' as d from dual union all
                select 'Clerk' as d from dual union all
                select 'Clerk' as d from dual union all
                select 'Salesman' as d from dual union all
                select 'Salesman' as d from dual union all
                select 'Salesman' as d from dual union all
                select 'Salesman' as d from dual union all
                select 'Salesman' as d from dual)
                SELECT CASE i
                       WHEN 0 THEN NULL
                       ELSE d
                       END d
                FROM (
                      SELECT i,d
                      FROM (
                            SELECT row_number() OVER (PARTITION BY t1.d ORDER BY 1) i,t1.d
                            FROM tmp t1
                            UNION ALL
                            SELECT DISTINCT 0 as ind,d FROM tmp
                            )
                      ORDER BY d,i DESC
                )            
                • 5. Re: Printing a blank line??
                  636453
                  Thanks DUDE!!!!!! :-)
                  • 6. Re: Printing a blank line??
                    450441
                    This is a display requirement and as such needs to be handled after the query has returned a resultset, by the client, not in SQL. SQL*Plus and TOAD are clients.

                    SQL*Plus, as demonstrated, allows you to do this. TOAD does not by default.

                    What are you planning to do with the results of the query?

                    Write it to a file? Have the PL/SQL output a blank line on change of job.
                    Show it in a webpage? The code that assembles the page should be able to handle it.
                    Print it in a report? The reporting software should handle it.

                    Fundamentally, your blank lines are formatting, not data. So they should not be returned by the SQL query.
                    • 7. Re: Printing a blank line??
                      Karthick2003
                      Very true. What they have achived by SQL makes me to feel that there is a Job which is Blank.
                      • 8. Re: Printing a blank line??
                        user643114
                        If my response is not too late try this:

                        dbms_output.put_line(chr(0));

                        Should do the trick!
                        • 9. Re: Printing a blank line??
                          BluShadow
                          If my response is not too late try this:

                          dbms_output.put_line(chr(0));

                          Should do the trick!
                          Only if you're using dbms_output for the wrong reasons such as displaying data in a production system.
                          dbms_output should only really be used for debugging purposes and even then there are more effective ways of debugging that don't rely on the dbms_output buffer being read at the end of the process; a buffer which is limited in size anyway.

                          I seriously would not recommend using dbms_output for displaying data, unless you are just using it for a quick debug.
                          • 10. Re: Printing a blank line??
                            646947
                            As this is a display requirement, i don't think it should be done in SQL. But when has that ever stopped anybody?
                            WITH
                                 Sample_Data
                            AS
                                 (
                                  SELECT 'Accountant'     Job FROM Dual UNION ALL
                                  SELECT 'Accountant'     Job FROM Dual UNION ALL
                                  SELECT 'Accountant'     Job FROM Dual UNION ALL
                                  SELECT 'Accountant'     Job FROM Dual UNION ALL
                                  SELECT 'Clerk'          Job FROM Dual UNION ALL
                                  SELECT 'Clerk'          Job FROM Dual UNION ALL
                                  SELECT 'Salesman'     Job FROM Dual UNION ALL
                                  SELECT 'Salesman'     Job FROM Dual UNION ALL
                                  SELECT 'Salesman'     Job FROM Dual UNION ALL
                                  SELECT 'Salesman'     Job FROM Dual UNION ALL
                                  SELECT 'Salesman'     Job FROM Dual
                                 )
                            SELECT
                                 Job
                            FROM
                                 (
                                  SELECT
                                      Job,
                                      Job          Order_By
                                  FROM
                                      Sample_Data
                                  UNION ALL
                                  SELECT DISTINCT
                                      NULL          Job,
                                      Job || Job     Order_By
                                  FROM
                                      Sample_Data
                                 )
                            ORDER BY
                                 Order_By;
                            • 11. Re: Printing a blank line??
                              Aketi Jyuuzou
                              with tmp as (
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Clerk' as d from dual union all
                              select 'Clerk' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual)
                              select nvl2(RowNum,d,null) as JOBTITLE
                                from tmp
                              group by rollup(d,RowNum)
                              having grouping_ID(d,RowNum) != 3;
                              JOBTITLE
                              ----------
                              Clerk
                              Clerk
                              null
                              Salesman
                              Salesman
                              Salesman
                              Salesman
                              Salesman
                              null
                              Accountant
                              Accountant
                              Accountant
                              Accountant
                              null
                              Other Version
                              with tmp as (
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Accountant' as d from dual union all
                              select 'Clerk' as d from dual union all
                              select 'Clerk' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual union all
                              select 'Salesman' as d from dual)
                              select case grouping(RowNum) when 1 then null
                                                           else d end as JOBTITLE
                                from tmp
                              group by grouping sets(d,(d,RowNum));
                              JOBTITLE
                              ----------
                              Clerk
                              Clerk
                              null
                              Salesman
                              Salesman
                              Salesman
                              Salesman
                              Salesman
                              null
                              Accountant
                              Accountant
                              Accountant
                              Accountant
                              null
                              • 12. Re: Printing a blank line??
                                646947
                                Your second one shows up as cheapest.

                                On my 10g system, user644250's had an estimated cost of 29, mine was 28, your first one was also 28, and your second a much lower 23.

                                I've never actually used GROUPING before. No matter how little i think i know, someone is always able to show me shows that i know even less.
                                • 13. Re: Printing a blank line??
                                  2889
                                  for a structured test output :
                                  set serveroutput on  format wrapped 

                                  begin
                                       dbms_output.enable ;
                                       dbms_output.put_line ('=============') ;
                                       dbms_output.put_line ('=============') ;
                                       dbms_output.put_line ('=============') ;
                                       dbms_output.put_line (null) ;
                                       dbms_output.put_line ('=============') ;
                                       dbms_output.put_line ('=============') ;
                                  end ;
                                  /

                                  =============
                                  =============
                                  =============

                                  =============
                                  =============                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                  • 14. Re: Printing a blank line??
                                    user12957777
                                    How to modify in case of multiple columns as result from query ?

                                    Sanjay