Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Export to positional textfile

    Hello,
    i need to provide an export to a positional (not delimited) file. I will use this file with a LOAD DATA .. POSITION load, so i can not use a delimited.

    The normal EXPORT DEL
    1, 2, 3
    must be:
    001002003

    But i can not find how to do this. Exists a db2 utility or a speciale export option?
    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select char(col1) || char(col2) || char(col3) from mytable
    Redirect output to a file.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    10
    :-) and what happends for decialm fields?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure why you find it funny. You should apply the formatting that suits your purposes.
    Code:
    $db2 "select d, char(d) from (select cast(10.1 as decimal(8,2)) d from sysibm.sysdummy>
    
    D          2
    ---------- ----------
         10.10 000010.10
    
      1 record(s) selected.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2009
    Posts
    10
    I found funny just because it is easier than I thought...
    Thanks for your suggestion.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow ...right(digits(colx), 3)...

    Quote Originally Posted by n_i View Post
    Code:
    select char(col1) || char(col2) || char(col3) from mytable
    Redirect output to a file.
    In my opinion (IMHO)
    Code:
    select 
    right(digits(col1), 3)  || right(digits(col2), 3) || right(digits(col3), 3) 
    from mytable
    is closer to the SPEC.

    Lenny

  7. #7
    Join Date
    Nov 2009
    Posts
    10
    Thanks very much, just another question... what about null values? I tried to use CHAR(..) but i get some strange string if the value is null.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use COALESCE() to convert null values to whatever should represent nulls in your file.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2009
    Posts
    10
    Thanks, i used "COALESCE", but what about set "Y" if null and "N" if not null in a char(1) independently from the field value?
    I can not do it with COALESCE, i think i must use the CASE statement do you agree, or there is other useful function?

    Thank you.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There are some (tricky) ways, like in the following example.

    But, I want to recommend to use CASE expression.
    Because it describes naturally the meaning of the column.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT col1
         , COALESCE(LEFT('N', 1 + col1 - col1), 'Y')        AS funcs
         , DECODE(col1, col1 + 1, 'Y', 'N')                 AS decode
         , CASE WHEN col1 IS NULL THEN 'Y' ELSE 'N' END     AS case
      FROM (VALUES -5, 0, 3, CAST(NULL AS INT) ) t(col1);
    ------------------------------------------------------------------------------
    
    COL1        FUNCS DECODE CASE
    ----------- ----- ------ ----
             -5 N     N      N   
              0 N     N      N   
              3 N     N      N   
              - Y     Y      Y   
    
      4 record(s) selected.

Posting Permissions

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