Skip navigation
Up to Discussions in TwinFin Talk
2514 Views 2 Replies Latest reply: Feb 8, 2012 12:01 AM by ulab RSS
ulab New Enzee
ulab
62 posts since
Jul 1, 2011
Currently Being Moderated

Feb 7, 2012 7:26 AM

Option to increase line size

HI All,

 

We have an external table and from that we are inserting into a table,one of the column value's length is greater than 2000 but in the select query it is retrieving only upto 2000 characters and rest is getting ignored, Could you please let me know what could be done to resolve this.

 

Many thanks.

  • Shawn Fox Enzee Exraordinaire
    Shawn Fox
    1,475 posts since
    Aug 15, 2006
    Currently Being Moderated
    1. Feb 7, 2012 3:41 PM (in response to ulab)
    Re: Option to increase line size

    I'm not really clear on what your problem is here.  Did you define the external table to have a column larger than varchar(2000)?  Is the target column larger than a varchar(2000)?  Are you perhaps using nvarchar instead of varchar somewhere?  Are there any custom functions involved in the SQL statement (maybe one of them is trimming the value to 2000 characters)?  There really isn't enough information in your post to know what the problem is.  If you post your external table definition, the target table definition, and your SQL statement someone might be able to help.  If you can't do that publicly, then I suggest you log an issue with Netezza support.

  • ulab New Enzee
    ulab
    62 posts since
    Jul 1, 2011
    Currently Being Moderated
    2. Feb 8, 2012 12:01 AM (in response to Shawn Fox)
    Re: Option to increase line size

    HI Shawn,

     

    below are the details

     

    external table definition.

     

     

    CREATE EXTERNAL TABLE ET_ETL_ERROR_INFO

    (

    ERROR_VALUE CHARACTER VARYING(2000),

    PID CHARACTER VARYING(30),

    TID CHARACTER VARYING(30),

    BATCH_ID NUMERIC(38,0),

    AUDIT_ID NUMERIC(38,0),

    ETL_JOB_NAME CHARACTER VARYING(254),

    ERROR_DETAILS CHARACTER VARYING(4000),

    ERROR_DATE TIMESTAMP

    )

    USING

    (

    DATAOBJECT( '/data/audit/etl_err_info.dat')

    DELIMITER '|'

    NULLVALUE ''

    TRUNCSTRING TRUE

    DATEDELIM ''

    TIMESTYLE '24HOUR'

    TIMEDELIM ''

    CTRLCHARS TRUE

    LOGDIR 'data/audit/log'

    FILLRECORD TRUE

    ENCODING 'INTERNAL'

    );

     

     

    Initially ERROR_DETAILS column length was 2000 later i used ALTER TABLE to modify that column to 4000 and still the problem was there...i did DROP and CREATE...now its workin fine....not sure why ALTER dint work..

     

    Many thanks

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points