Results 1 to 5 of 5

Thread: spool file

  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: spool file

    Hi all

    i am using spool file option like below
    spool c:\tmp_vehdetl
    set heading off
    set pages 0
    select VDPOLN||'|'||VDRENN||'|'||VDSEQN||'|'||VDVEHN||'|' ||VDVRSQ||'|'||VDVHYR||'|'||VDMAKC||'|'||VDMODC||' |'||VDSMDC||'|'||VDBDCD||'|'||VDTYPE||'|'||VDVINN| |'|'||VDSYMB||'|'||VDPSYM||'|'||VDRTER||'|'||VDISC D||'|'||VDSYDM||'|'||VDDRVN||'|'||VDWTCL||'|'||VDV USE||'|'||VDVUBC||'|'||VDRDUS||'|'||DUMMYDRV||'|'| |VDAMIL FROM TMP_VEHDETL;
    SPOOL OFF;

    for that i am getting file like below


    8054897|0|0|5|5|1981|PORSCH|928 |----- |ACOUPE|V|WP0JA0928BS820582 |
    99|0|999||N|0||P||0||500

    8054898|0|0|1|1|1975|CHEVRO|IMPALA |----- |A4DRSD|V|1L69H5J206 |
    99|0|0||T|0||P||0||500

    8054899|0|0|1|1|2008|HYUNDA|VERACRUZG |LS/SE/LIMI|M4DR4W|V|KM8NU73C08 |
    99|0|0||T|0||P||0||40000

    8054899|0|0|2|2|2005|CHRYSL|PT CRUISER|----- |CNVT |V|3C3EY55E25T290644 |
    99|0|0||N|0||P||0||45000

    8054900|0|0|1|1|2008|MITSUB|OUTLANDER |XLS |M4DR4W|V|JA4MT41X78Z001871 |
    99|0|0||N|0||P||0||50000

    8054900|0|0|2|2|2005|TOYOTA|COROLLA |CE/LE |A4DRSD|V|JTDBR32E05 |
    99|0|0||N|0||P||0||60000

    8054901|0|0|1|1|2002|INFINI|QX4 |----- |M4DR2W|V|JNRDR09X02 |
    99|0|0||N|0||P||0||5000

    8054902|0|0|1|1|1998|HONDA |CIVIC |EX |ACOUPE|V|1HGEJ8247WL |
    99|0|0||N|0||P||0||7000



    i am getting gaps between one record to other and also i am getting some gaps between columns
    how to get data with out any gaps ,i mean no gap between each record and column and also i wanted to get the data one record with 24 columns in one line like that


    please advise me...

    Thanks in advance!!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try "set linesize 1024", or whatever number is suitable in your situation.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool User gaps

    You do have extra spaces in your delimiters, try this to avoid gaps:
    Code:
    spool c:\tmp_vehdetl
    set heading off pages 0 lin 1024 trims on
    SELECT VDPOLN||'|'||VDRENN||'|'||VDSEQN||'|'||VDVEHN||'|'||VDVRSQ||'|'
         ||VDVHYR||'|'||VDMAKC||'|'||VDMODC||'|'||VDSMDC||'|'||VDBDCD||'|'
         ||VDTYPE||'|'||VDVINN||'|'||VDSYMB||'|'||VDPSYM||'|'||VDRTER||'|'
         ||VDISCD||'|'||VDSYDM||'|'||VDDRVN||'|'||VDWTCL||'|'||VDVUSE||'|'
         ||VDVUBC||'|'||VDRDUS||'|'||DUMMYDRV||'|'||VDAMIL
      FROM TMP_VEHDETL;
    SPOOL OFF;

    PS: apart from N_I's suggestion.
    Last edited by LKBrwn_DBA; 01-28-10 at 10:50. Reason: Adding n_i's suggestion
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    rtrim() on each selected character column may also be in order.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2010
    Posts
    29
    Thanks it woeked!!!

Posting Permissions

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