capture the reject record based on column length

[moulipx]

Hi,
I need some help regarding rejection of records based on column length for each record.

please find sample below file format. And my contains nearly 300 columns .

col1,col2,col3,col4
aaa,bbbb,cccc,dddd
111,22,333,datadata

And the metadata of file is

ColName length

col1 3
col2 4
col3 3
col4 4


My requirement is to reject the record if the column value length is greater than actual length.

In the above scneario i need to capture the second record since col4 of second record length is greater than 4

111,22,333,datadata.

please help me in solving this issue.

Can i achieve this in transformer without using in stagevariables. Since my file contains 300 records

Thanks in advance

[SURA]

What is your source ?

SEQ file ? If so, you can choose the data type as char(length) and use the
reject mode to output and write it in a file.

DS User

[moulipx]

My Source is Sequentail file . And i can't use CHAR datatype while reading the file Since in some times my columns values length may be less than metadata length specified.

[SURA]

You can use the Length function in the TFM and reject.

DS User

[moulipx]

as i said my source file is having 300 columns. it's very tedious to use so many stage variables . Is there anyway to do in transformer.

[zulfi123786]

Or you could use a single stage variable and dump a huge derivation for all 300 columns in the same

[moulipx]

I did sequential file stage. But for VARCHAR fields if the data length is greater than original column length the row is getting rejected.

plesae guide me on this..

[zulfi123786]

But for VARCHAR fields if the data length is greater than original column length the row is getting rejected.

From what i recollect Varchar should not drop/reject a recrod if the length exceeds what is defined, it just strips off the extra characters.

you can verify this by turning all your columns to varchar and i am sure your records wont get dropped/rejected

[moulipx]

Actally the records are not getting rejected if for varchar fields.

[moulipx]

Actally the records are not getting rejected if for varchar fields.

Could someone help me on this..

[chulett]

Are you certain you can't use CHAR for this? Have you actually tried it? It's a delimited file so it seems to me that it should read it based on the delimiters rather than the field size and if one is 'short' it will use the PAD character defined to expand it to full size, so make sure that is set to 0x20.

[mobashshar]

Hi,
One option I can think of is as follows:

Make sure the input field are defined as Varchar. In Transformer create a extra column as flagcol for each input column. So it will be like flagcol1, flagcol2,.... etc.

In transformer use this logic to set the flag flagcol1 = IF (Len(trimleadingtrailing(lnk_input.col1)) = 3) THEN "Y" ELSE "N". Do this for all your input columns and change the length value to 3 or 4 based on column requirement.

Finally use the Filter stage to filter/reject the rows. You can use this logic flagcol1 = "Y" AND flagcol2 = "Y" AND flagcol3 = "Y" AND flagcol4 = "Y" and make sure to set the Output Rejects = True.

This way you will get all the rows with valid length in Col1, Col2, Col3 and Col4 in the output and rows with Invalid length will be rejected.

Hope this helps

Mobashshar

[moulipx]

I used CHAR datatypes for all fields. But in PADCHAR property it is not allowing me to set 0x20.Then i choosed SPACE property. But all the colums fields the short of length specified are failed are getting dropped.

[moulipx]

please somebody help me on this

[paultechm]

Read it as a single varchar column with out any lenght.In transformer put a constraint like length(column)>(take the total metadata length sum) then reject it


-Paul