Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Red face Unanswered: Generate csv file with headers...using db2 export ?

    I want to add headers to a csv file I am generating from db2 export utility ...
    I am using the following command line

    db2 -x "EXPORT TO /home/runTest.csv of DEL MODIFIED BY coldel,
    select col1,col2,col3
    from <some table>
    union all values '"Header1","Header2","Header3"';"

    I am getting error "SQL0421N The operands of a set operator or a VALUES clause do not have the
    same number of columns. SQLSTATE=42826"

    Kindly help

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    UNION just concatenates two selects of Corresponding columns.
    since the returned columns from sysdummy1 (your headers) are all char,
    your select of col1,col2,col3 also have to be all char.

    Select "Header1","Header2","Header3"
    from sysibm.sysdummy1
    union all
    select col1,col2,col3
    from <some table>;

    you may have to do two exports, and then concatenate the two files.
    Last edited by dbzTHEdinosaur; 08-05-12 at 05:32. Reason: added rules for UNION
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    db2 -x "EXPORT TO /home/runTest.csv of DEL MODIFIED BY coldel,
    select
    substr(coalesce(char(t1.DOB),' '),1,4),
    substr(coalesce(char(t1.DOB),' '),6,2),
    substr(coalesce(char(t1.DOB),' '),9,2),
    substr(coalesce(t1.PAT_SEX,' '),1,1)
    from member_demo t2,member_auth t1
    where conditions......
    union all values '"year","Month","Day","sex"';"

    Let me know anything worng in above query....I have tried to convert all columns to char...

  4. #4
    Join Date
    Aug 2012
    Posts
    4
    Quote Originally Posted by dbzTHEdinosaur View Post
    UNION just concatenates two selects of Corresponding columns.
    since the returned columns from sysdummy1 (your headers) are all char,
    your select of col1,col2,col3 also have to be all char.

    Select "Header1","Header2","Header3"
    from sysibm.sysdummy1
    union all
    select col1,col2,col3
    from <some table>;

    you may have to do two exports, and then concatenate the two files.
    working if I just give one header column Header1 but not if I include headers for all columns ( Header2,Header3)

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Select "Header1","Header2","Header3"
    from sysibm.sysdummy1
    Use single quotation mark, like...
    Code:
    SELECT 'Header1' , 'Header2' , 'Header3'
      FROM sysibm.sysdummy1
    
    or
    
    VALUES('Header1' , 'Header2' , 'Header3')

  6. #6
    Join Date
    Aug 2012
    Posts
    4
    Quote Originally Posted by tonkuma View Post
    Use single quotation mark, like...
    Code:
    SELECT 'Header1' , 'Header2' , 'Header3'
      FROM sysibm.sysdummy1
    
    or
    
    VALUES('Header1' , 'Header2' , 'Header3')

    Thanks Buddy .... This works ...you are a champion

Posting Permissions

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