CODEWITHSUNDEEP
oracle-databaseformattingsqlplus
misteralexander
misteralexander

Reputation: 488

PL/SQL: Center query RESULTS

I've googled all I know how to Google. I need some new eyes to look at this.

I am trying to center the RESULTS of a query. Here is what I have so far ...

    set lines 2000 pages 2000
    COLUMN total HEADING "Total" FORMAT A20 JUSTIFY CENTER
    COLUMN sending_fi HEADING "Sending M" FORMAT A20 JUSTIFY CENTER
    COLUMN receiving_fi HEADING "Receiving M" FORMAT A20 JUSTIFY CENTER
    COLUMN status HEADING "Payment Status" FORMAT A20 JUSTIFY CENTER
    SET UNDERLINE =
    select
        count(*) as "Total",
        p.sending_fi,
        p.receiving_fi,
        p.status
    from
        my_schema.my_table p
    where
        p.match_date
            between
                to_date
                    (
                        '2017/10/26:00:00:00',
                        'yyyy/mm/dd:hh24:mi:ss'
                    )
            and
                to_date
                    (
                        '2017/10/28:23:59:59',
                        'yyyy/mm/dd:hh24:mi:ss'
                    )
    and
        p.expedited='1'
    and
        p.status='DELIVERED'
    group by
        p.sending_fi,
        p.receiving_fi,
        p.status
    order by
        1
        DESC
    ;

Which produces this output ...

Total Sending M Receiving M Payment Status
==================== ==================== ==================== ================= 266759 BAC BAC DELIVERED
49954 JPM BAC DELIVERED
45194 BAC JPM DELIVERED
25990 WFC BAC DELIVERED
25676 JPM WFC DELIVERED
24120 WFC JPM DELIVERED
23565 BAC WFC DELIVERED

The COLUMN HEADERS (names) are centered, but for the life of me, I can't figure out how to center the results.

I'm a non-privileged user (all i ever really do are select update and insert), with very limited SQL skills. Please be gentle in your suggestions.

Upvotes: 1

Views: 12007

Answers (3)

Niall Litchfield
Niall Litchfield

Reputation: 391

Bobby's answer will work for text output. You can also make use of the HTML markup feature of sqlplus and produce formatted html output. An example which extends https://docs.oracle.com/database/121/SQPUG/ch_seven.htm#SQPUG531 would be 

create table t1 (
id number
, dt date 
, vc varchar2(20) 
, constraint pk_t1 primary key (id) );

begin     
for i in 1..10 loop
 insert into t1 values (i,sysdate + i , 'Row '||i);
end loop;
commit;
end;
/

SET MARKUP HTML ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Department Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
<!-- TD { text-align: center} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"

Then run 

spool t.html

followed by your query and then 

host firefox t.html

Upvotes: 0

user5683823
user5683823

Reputation:

Originally I wrote this as a Comment. Then I thought of closing the question as a duplicate; but the only duplicate I found has no answers: just a Comment saying the same thing. (I should know, it was my Comment then, too!)

Column Formatting JUSTIFY not working

ANSWER:

It looks like you want an answer in SQL*Plus. There is no answer. Strings and dates are always aligned to the left side of the column, and numbers to the right. Period. You can't change that (other than by adding to the strings themselves, in the query - by padding with spaces; a complete waste of time!) If you need to generate "good looking" reports, and what is available in SQL*Plus is not good enough, you must use a reporting tool; can't be done in SQL*Plus.

There seems to be some confusion about the SQL command COLUMN ... FORMAT ... JUSTIFY. Here is what the manual says:

JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}

Aligns the heading. If you do not use a JUSTIFY clause, headings for NUMBER columns default to RIGHT and headings for other column types default to LEFT.

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12013.htm#BACHCABF

Upvotes: 1

Bobby Durrett
Bobby Durrett

Reputation: 1293

You might be able to use LPAD.

Here is a quick example:

COLUMN blks HEADING "Num Blocks" FORMAT A10 JUSTIFY CENTER

select 
lpad(to_char(blocks),(10-length(to_char(blocks)))/2+length(to_char(blocks)),' ') blks 
from user_segments
where rownum < 11;

The idea is to pad the column on the left with half of the difference between the format size and the size of the column value in spaces.

Output:

Num Blocks
----------
    16
    16
    16
    16
    16
    16
    16
    16
    16
    16

10 rows selected.

Bobby

Upvotes: 1

Related Questions