Record rejected to error table if date feild is enclosed with double quotes

Tools & Utilities
Enthusiast

Record rejected to error table if date feild is enclosed with double quotes

Hi,

I am trying to load below two records using TPT( version 15.10.00.00) and able to load succusfully second record but first record is going to error table. The same records gets loaded in TPT version 14.00.00.08 .

File :

"1194","N",,"20150422","","20150422"

"1195","N",,"20150423","","20150423"

This is comma delimitted file and 4th,5th,6th places are date field

i am already usind attribute

Quaoteddata='Optional"

OpenQuoteMark="""

CloseQuoteMark="""

Nullcolumns='Y'

I suscept this problem may be because of version change as same file and same code is able to load the file to table correctely when TPT 14.00.00.08 is used and same has issue when using TPT 15.10.00.00

Any input on this woul be of great help

Thanks

Muzammil

20 REPLIES
Teradata Employee

Re: Record rejected to error table if date feild is enclosed with double quotes

On which platform are you running?

Have you looked at the error table to see which column is being flagged as the error column?

Obviously, it does not make sense (to me) because the data is pretty much identical.

Also, I am hoping that your attributes were hand-written and not copied-and-pasted, because "QuotedData" is misspelled in your post.

It appears in the post as "Quaoteddata". A misspelled attribute name would not enable the feature (just checking).

-- SteveF
Enthusiast

Re: Record rejected to error table if date feild is enclosed with double quotes

Thanks Streve for responsding

Platform :TD 15.10

Yes error table show the column which is date

Error table :EXX_ATTR_e1

ErrorCode   ErrorFieldName  

2665           BGLR_DATE

Yes that quotes and all are typo errors

Thanks

Muzammil

Enthusiast

Re: Record rejected to error table if date feild is enclosed with double quotes

Yes that quotes and all are type errors but i have them correctly in script. Concern here is same data is loaded correctly in 14.10 where as it is rejecting in 15.10

Thanks

Muzammil

Teradata Employee

Re: Record rejected to error table if date feild is enclosed with double quotes

Can you provide me with the table definition and script you are using?

-- SteveF
Teradata Employee

Re: Record rejected to error table if date feild is enclosed with double quotes

And at first you said it worked in 14.00.00.08, and then you say it works in 14.10.

Does it work in both 14.00 and 14.10, but not in 15.10?

-- SteveF
Enthusiast

Re: Record rejected to error table if date feild is enclosed with double quotes

++++++++++++++++++++++++++++++++++DDL++++++++++++++++

CREATE MULTISET TABLE DEV.EXX_ATTR ,NO FALLBACK ,
      NO BEFORE JOURNAL,
      NO AFTER JOURNAL,
      CHECKSUM = DEFAULT,
      DEFAULT MERGEBLOCKRATIO
      (
       Col1 INTEGER,
       Col2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
       Col3 VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
       Col4_DATE DATE FORMAT 'YYYY-MM-DD',
       Col5_DATE DATE FORMAT 'YYYY-MM-DD',
       Col6_DATE DATE FORMAT 'YYYY-MM-DD')
 PRIMARY INDEX EXT_STORE_SECURITY_PI ( Col1 );

++++++++++++++++++++++++++++++++script+++++++++++++++++++++

DEFINE JOB load_EXX_ATTR(
  DEFINE OPERATOR W_1_op_loadEXX_ATTR
  TYPE LOAD
  SCHEMA *
  ATTRIBUTES
  (
   VARCHAR UserName, VARCHAR UserPassword, VARCHAR LogTable, VARCHAR TargetTable,
   INTEGER BufferSize, INTEGER ErrorLimit, INTEGER MaxSessions, INTEGER MinSessions,
   INTEGER TenacityHours, INTEGER TenacitySleep, VARCHAR AccountID, VARCHAR DateForm,
   VARCHAR ErrorTable1, VARCHAR ErrorTable2, VARCHAR WorkTable ,VARCHAR NotifyExit, VARCHAR NotifyExitIsDLL,
   VARCHAR NotifyLevel, VARCHAR NotifyMethod, VARCHAR NotifyString, VARCHAR PauseAcq,
   VARCHAR PrivateLogName, VARCHAR TdpId, VARCHAR TraceLevel, VARCHAR WorkingDatabase,
  VARCHAR QueryBandSessInfo );
  DEFINE SCHEMA W_0_sc_loadEXX_ATTR
  (
       "Col1" VARCHAR(25),
       "Col2" VARCHAR(5),
       "Col3" VARCHAR(250),
       "Col4_DATE" VARCHAR(15),
       "Col5_DATE" VARCHAR(15),
       "Col6_DATE" VARCHAR(15)
  );
  DEFINE OPERATOR W_0_op_loadEXX_ATTR
  TYPE DATACONNECTOR PRODUCER
  SCHEMA W_0_sc_loadEXX_ATTR
  ATTRIBUTES
  (
   VARCHAR FileName, VARCHAR Format, VARCHAR OpenMode, INTEGER BlockSize,
   INTEGER BufferSize, INTEGER RetentionPeriod, INTEGER RowsPerInstance, INTEGER SecondarySpace,
   INTEGER UnitCount, INTEGER VigilElapsedTime, INTEGER VigilWaitTime, INTEGER VolumeCount,
   VARCHAR AccessModuleName, VARCHAR AccessModuleInitStr,  VARCHAR AcceptExcessColumns,
   VARCHAR FileList, VARCHAR ExpirationDate, VARCHAR RowErrFileName,VARCHAR EscapeTextDelimiter,
   VARCHAR IndicatorMode, VARCHAR PrimarySpace, VARCHAR PrivateLogName, VARCHAR RecordFormat,
   VARCHAR RecordLength, VARCHAR SpaceUnit, VARCHAR TextDelimiter, VARCHAR VigilNoticeFileName,
   VARCHAR VigilStartTime, VARCHAR VigilStopTime, VARCHAR VolSerNumber, VARCHAR UnitType,
   VARCHAR NullColumns, VARCHAR QuotedData, VARCHAR OpenQuoteMark, VARCHAR CloseQuoteMark
  );
  APPLY
   (
    'INSERT INTO DWBATCHDEV.EXX_ATTR(
     "Col1",
     "Col2",
     "Col3",
     "Col4_DATE",
     "Col5_DATE",
     "Col6_DATE"
     )
     VALUES
     (
     :Col1 ,
     :Col2 ,
     :Col3 ,
     :Col4_DATE (DATE,FORMAT ''YYYYMMDD''),
     :Col5_DATE (DATE,FORMAT ''YYYYMMDD''),
     :Col6_DATE (DATE,FORMAT ''YYYYMMDD'')
    );'
   )
  TO OPERATOR
  (
   W_1_op_loadEXX_ATTR[1]
   ATTRIBUTES
   (
    UserName = @UserName,
    UserPassword = @PassWord,
    LogTable = 'TMP.EXX_ATTR_lg',
    ErrorTable1 = ' TMP.EXX_ATTR_e1',
    ErrorTable2 = ' TMP.EXX_ATTR_e2',
    WorkTable = ' TMP.EXX_ATTR_wk',
    TargetTable = 'DEV.EXX_ATTR',
    MaxSessions = 10,
    PrivateLogName = 'LOAD',
    TenacityHours = 1,
    TenacitySleep = 1,
    TdpId = @TdServer
   )
  )
  SELECT
     "Col1",
     "Col2",
     "Col3",
     "Col4_DATE",
     "Col5_DATE",
     "Col6_DATE"
  FROM OPERATOR
  (
   W_0_op_loadEXT_LP_LOC_SECURITY_ATTR[4]
   ATTRIBUTES
   (
    FileList = 'Y' ,
    FileName = '/dev/log/tdtst_DEV_EXX_ATTR.lst',
    Format = 'DELIMITED',
    QuotedData = 'Optional',
    OpenQuoteMark = '"',
    CloseQuoteMark = '"',
    OpenMode = 'Read',
    AcceptExcessColumns = 'Y',
    PrivateLogName = 'READ',
    NullColumns = 'Y',
    TextDelimiter = ','
   )
  );
 );

Hi,

It worked on 14.00.00.08 not in 15.10.00.00

I have attached script and DDl

Thanks

Muzammil

Teradata Employee

Re: Record rejected to error table if date feild is enclosed with double quotes

Thank you for all of the information.

I ran the same job on my system with TPT 15.10 and find that both rows end up in the error table. And this is correct in that both rows need to be treated the same way. With that data, there should never be a case where one row loads into the target table and the other one ends up in the error table.

Of course, that does not explain the problem, but on my system (and you can try the same thing), I exported the data from the DataParcel field in the error table to see what is causing the problem and I see what is happening.

If you do this:

1. run BTEQ

2. .export data file=<some file name>

3. sel dataparcel from <tablename>;

4. .export reset

5. exit out of BTEQ

Go take a look at the data file. If you are on Unix/Linux and can use 'od -c' you can dump out the contents of the file and look at the characters. And you will see that the quotes were shipped as part of the data. And this is what was causing the DATE fields to be rejected (the DBS only reports the first one it encounters that is bad, but all of the DATE fields are bad).

We are looking into why we are keeping the quotes with the data. That should not be happening. They should be stripped out.

-- SteveF
Enthusiast

Re: Record rejected to error table if date feild is enclosed with double quotes

Yes Steve , you are right both should go to error table , i think i was testing with different set and i have mention the same , my bad

Do you know why same thing is correctly loaded in TPT 14.00.00.08 but not in TPT 15.10.00.00

This is part of our testing where we are upgrating from 14.10 to 15.10 and file format cannot be chnaged because of too much work at our side and also there is no convience answer for changing all the source files

Thanks

Muzammil

Teradata Employee

Re: Record rejected to error table if date feild is enclosed with double quotes

We are investigating.

-- SteveF