I have a sequential file stage as a source in my parallel job. I have a reject link to the file to capture the records which don't match the metadata. But for the below data sequential file is not rejecting the record.
Ex:
Metadata
Col1 - date
Col2 - integer(3)
Col3 - decimal(5,2)
Sample data:
2014-05-06,4a,100.20
I expected the above record to be rejected as the integer field has character value but instead this record was sent to output as
2014-05-06,4,100.20
Please let me know if anyone encountered the same issue and how you resolved it. Thanks in advance.
Sequential file issue in parallel job version 8.1
Moderators: chulett, rschirm, roy
It seems to be removing the a from the 4a value.
Read the file as varchar and then in transformer use function in constraint :
There reject the records.
Read the file as varchar and then in transformer use function in constraint :
Code: Select all
IsValid('Date', COL1) and IsValid('int64',COL2) and IsValid('Decimal',COL3)
ssnegi,
Thanks for your reply.
As you said this can be achieved using a transformer. But I was trying to understand the reason why it is not rejected. If the value in integer column is "aa" it is rejecting. Even in the decimal column if i give a value "1 0.20" it is changing it to "001.00" instead of rejecting it. Is there a way to handle thus issues in sequential file itself?
Thanks for your reply.
As you said this can be achieved using a transformer. But I was trying to understand the reason why it is not rejected. If the value in integer column is "aa" it is rejecting. Even in the decimal column if i give a value "1 0.20" it is changing it to "001.00" instead of rejecting it. Is there a way to handle thus issues in sequential file itself?
For an integer column, values of "4" and "4a" will both be accepted as 4 because the software is able to read an integer up to a point and then move on to the next delimiter. Values of "a4" and "aa" will be rejected, unless a default value is specified to handle import errors, because the software is reading left to right and not finding any valid integers.bgs wrote:But I was trying to understand the reason why it is not rejected. If the value in integer column is "aa" it is rejecting. Even in the decimal column if i give a value "1 0.20" it is changing it to "001.00" instead of rejecting it. Is there a way to handle thus issues in sequential file itself?
Sequential File stage data validation options are pretty limited while Transformer stage options are extensive. In the Sequential File stage's columns tab, edit the column metadata (double click the column ID on the left), and set a type default value, such as "-999". Values of "a4" and "aa" will generate import errors that are handled by using the default value. That can help prevent some rejects but it varies by data type and the severity of the data problems.
Choose a job you love, and you will never have to work a day in your life. - Confucius