Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Unanswered: DB2 export with column headers - problem

    I am trying to export data in db2 using export utility.
    But the result of this export gives nothing. I just get the column headers but there is no data(for the second select)
    I think the Select statement cannot handle strings more than 255 characters.
    Is there any workaround for this? typecasting, or something else?

    EXPORT TO ABCDUMP.csv OF del
    modified by nochardel
    MESSAGES ABCMSG.txt

    VALUES '"F1"; "F2"; "F3";"F4";"F5";"F6";"F7";"F8";"F9";"F10";"F11" ;"F1 2";"F13";"F14";"F15";"F16";"F17";"F18";"F19";"F 20" '

    UNION ALL

    SELECT
    '"'||TRIM(CHAR(FIELD1))||'";"'||
    TRIM(CHAR(FIELD2))||'";"'||
    Trim(FIELD3)||'";"'||
    Trim(FIELD4)||'";"'||
    Trim(FIELD5)||'";"'||
    Trim(CHAR(FIELD6))||'";"'||
    Trim(CHAR(FIELD7))||'";"'||
    trim(FIELD8)||'";"'||
    Trim(CHAR((int(FIELD9))))||','||Trim(Char((Abs(Int (((FIELD9 - int(FIELD9)) * 100))))))||'";"'||
    Trim(CHAR((int(FIELD10))))||','||Trim(Char((Abs(In t(((FIELD10 - int(FIELD10)) * 100))))))||'";"'||
    Trim(CHAR((int(FIELD11))))||','||Trim(Char((Abs(In t(((FIELD11 - int(FIELD11)) * 100))))))||'";"'||
    Trim(CHAR(FIELD12))||'";"'||
    Trim(CHAR(FIELD13))||'";"'||
    Trim(FIELD14)||'";"'||
    Trim(FIELD15)||'";"'||
    Trim(FIELD16)||'";"'||
    Trim(FIELD17)||'";"'||
    Trim(CHAR(FIELD18))||'";"'||
    Trim(CHAR((int(FIELD19))))||','||Trim(Char((Abs(In t(((FIELD19 - int(FIELD19)) * 100))))))||'";"'||
    Trim(CHAR(FIELD20))||'"'
    FROM
    EMP.TABLE1

    There are 54651 records in EMP.TABLE1.


    Please help!

    Regards,
    Ashish S

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If any of the columns are null, then the entire result is null.

    Andy

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    I dont think thats the problem.
    If I remove the last 2 columns from the 2nd select, I am getting the results.

  4. #4
    Join Date
    Feb 2009
    Posts
    7
    Hi Andy,
    Thanks for your answer, that was indeed the problem.
    I used CASE statement for each field and made it empty when the field in null.

    But there is another problem I am facing with this sql.:

    1st select contains the header and 2nd select contains the data.
    I want the header to be in the first row, and then the data from the second row onwards.
    But the UNION and ORDER BY makes the header somewhere in between and not in the order I want.
    I tried putting a space in the column header, and then used a ORDER BY.
    But the system which is trying to upload this file is not accepting a space as a first character. I dont want the system to be changed.

    Could you please help. I just want the header to be in the first row.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Add another column to both sides of the UNION. For the header row, set it to 1 and for the query, set it to 2 and then ORDER BY on that column.

    Andy

Posting Permissions

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