SQLPLUS formatting
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQLPLUS formatting

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Posts
    59
    Hello,

    I have the results of a query which appears as follows -

    POR_ID POR_TOP_ID_SENDING POR_TOP_ID_RECEIVING POR_ORDER_N P P P P POR_CREATED_TIMESTAM POR_USERID
    ---------- ------------------ -------------------- ----------- - - - - -------------------- ----------
    1 1 2 26/01/582 P S N C 13-AUG-1999 22:17:11 PICKELLS

    I want to find out if there is any way that I can format the first three columns (all number datatypes) and leave the rest alone "without" having to write each column out within the select statement??

    I mean can can you use an sqlplus formating commands? - column...FORMAT... etc. I have tried RPAD for these 1st three columns but do not want to have to write the others as I do not want to format their output.

    If you need clearification please let me know. Maybe what I want can only ne achivede via a procedure or something?????

  2. #2
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

    Is it some thing like this.

    column sal noprint
    column comm noprint
    select ename,job,sal,comm
    from bonus
    Nagesh

  3. #3
    Join Date
    Oct 2001
    Posts
    59
    Thanks for that nagesh - that command actually gets rid of the output; but that is not what I want.

    I want to be able to say justify the first three columns output say justify the value to center or LEFT and not do naything to the rest.

    I do not want to have to do this -

    select (my first three columns with RPAD's), the rest of the columns
    from table

    as too many columns to type in .

    Is there such a command(s) or way in which I can get the first three columns formatted (values centered) and get all the rest of the columns selected "without" having to type them into the select statement???

    [Edited by marist89 on 07-05-2002 at 09:36 AM]

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    No..

    you can only justify heading using col.

    you will have to use a function in your script.

    SQL> HELP COL

    COLUMN
    ------

    Specifies display attributes for a given column, such as:
    - column heading text
    - column heading alignment
    - NUMBER data format
    - column data wrapping

    Also lists the current display attributes for a single column
    or all columns.

    COL[UMN] [{column | expr} [option...] ]

    where option is one of the following clauses:
    ALI[AS] alias
    CLE[AR]
    ENTMAP {ON|OFF}
    FOLD_A[FTER]
    FOLD_B[EFORE]
    FOR[MAT] format
    HEA[DING] text
    JUS[TIFY] {L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]}
    LIKE {expr | alias}
    NEWL[INE]
    NEW_V[ALUE] variable
    NOPRI[NT] | PRI[NT]
    NUL[L] text
    OLD_V[ALUE] variable
    ON|OFF
    WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]


    SQL>

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Oct 2001
    Posts
    59
    Here I am formatting the output of the first column
    but not doing the same for the other 15 columns as I do not want their output to be formatted

    select rpad(
    lpad(
    to_char(POR_TOP_ID_SENDING),
    (length(POR_TOP_ID_SENDING)+10)/2
    )
    ,10) col 1, col2....col15
    from my table
    where rownum < 20

    Is there a way of getting the first columns output to be formatted within the select statement and leave the rest of the columns untouched "and" not having to type those columns in within the select statement??

    Thanks in advance.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Write a function, center(), that does the centering for you.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width