Formatting Sql Output
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Formatting Sql Output

  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Formatting Sql Output

    I need to extract some data but in a specific format. The data that I am extracting is supposed to be matched up to certain specifications. For example if I query the name field the output must be 80 characters in length and even if the name is not that long it has to be padded with spaces. For numberical fields it has to be padded with zeros to the desired length.

    Secondly I am restricted my the linesize for my output. The total number of fields I want to extract is 75 and whenever I go beyond a certain linesize the output wraps over to the next line. How can I stop this I need a single record in a line.

    I would be greatful to see a PL/Sql code that formats output.

    Thank you.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    read about SQL funtions

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Formatting Sql Output

    Originally posted by Yus

    I would be greatful to see a PL/Sql code that formats output.
    PL/SQL would be WAY overkill for this requirement.
    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."

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    You can create a SQL file that has all of the formmating commands that you want and use it as a template for any new SQL files that you create.

    take a look at the following:

    SET LINESIZE 300
    COL mycol FORMAT A80
    COL mycolo FORMAT 00009
    this space intentionally left blank

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
    SQLdesc t1
     Name                                Null
    ?    Type
     
    ----------------------------------- -------- ------------------------
     
    NAME                                         VARCHAR2(32)
     
    ID                                           NUMBER
     CITY                                         VARCHAR2
    (30)
     
    STATE                                        VARCHAR2(30)

    SQLset lines 70 pages 40 wrap on

    before prompting

    SQL
    select from t1;

    NAME                                     ID
    -------------------------------- ----------
    CITY                           STATE
    ------------------------------ ------------------------------
    TAMIL                                   100
    NEWYORK                        NEWYORK

    SCOTT                                   200
    ATLANTA                        GEORGIA


    after formatting

    SQL
    get x
      1  select rpad
    (name,32-length(name),'.')||' '||
      
    2         to_char(id,900000)||' '||
      
    3         rpad(city,30-length(30),'.')||' '||
      
    4         rpad(state,30-length(state),'.'newrow
      5
    from t1
    SQL
    > /

    NEWROW
    ----------------------------------------------------------------------
    TAMIL......................   00100 NEWYORK..................... NEWYO
    RK
    ................

    SCOTT......................   00200 ATLANTA..................... GEORG
    IA
    ................ 
    Hope this helps

    Tamil

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