Loading the Target

[rajeevm]

Hi All,

I am trying to load the Target table as I have one column in the source which is a combination of Numeric and alphabetical data i.e letters and numbers but i wanted to load in the target as letters should load into letter column and the numbers would go into number column

I have tried alpha function but still i am getting warnings saying that non numeric data is entered when numeric required.

As my source column also has B- ,B+, A - and so on

How would I handle this

Please provide your valuable inputs.

Thanks

[us1aslam1us]

The warning indiactes that you are forcing a non-numeric data into a numeric field. What is the DataType for those two columns and what functions you are using for tranformations?

[ds_developer]

For numbers I would remove the plus "+" sign and any spaces. I've never tried loading a trailing negative sign so you might have to detect it, remove it then use the NEG() function (or move it to the front of the number).

Since you start with a string, there is always the possibility of non-printable characters (carriage return, tabs, linefeeds, etc.) that won't convert to numeric. You'd have to remove those too.

I would suggest adding a reject file on your job so you can look at the data that doesn't get loaded.

John

[Madhusv]

For Alpha:
Convert('<Enter all other Non-Alpha characters> ','',FieldName)

For Non-Alpha :
Convert('<Enter all Alpha Characters>','',FieldName)


Thanks,
Madhu

[DSguru2B]

Use DIGITS() to extract all numbers from alpha-numeric data and LETTERS() or ALPHA() one of those I believe (cant remember exactly) for alphabets.

[rajeevm]

hi dsguru2b ,

there is no digits function in the i have checked please tell me syntax of that function

thanks

[rajeevm]

i have datatypes as letter column as varchar and the number column as decimal . but in the source it is varchar

[DSguru2B]

Its a transform functioin. Its syntax is exactly what I gave you.

Code: Select all

DIGITS(in.Col)

where in.Col is your incoming column.
You can also use OCONV with MCN.

Code: Select all

OCONV(in.Col, "MCN")

[rajeevm]

Hi DSguru,


I have tried by using LETTERS and DIGITS transform functions , but here is my doubt i have apart from alphabetic and numeric fields coming from the source but also the source table has something like A+ ,A- ,B+ B- but these are not loading into the target .

What could be done to capture the letters having +,- atlast

Any input would be appreciated


Thanks

[DSguru2B]

If thats the case then use EREPLACE() or convert() to replace all alphabets for so called numeric fields and replace all numbers for strings that dont require numbers.

[rajeevm]

I did not get your point please elaborate it .

what exactly you want me to do ? I am fine with the letters an ddigits functions inspite of that wht else i need to add
to the transformer stage


Thanks

[DSguru2B]

Ok now your confusing me, I thought that you wanted to capture other characters like (-) and (+) etc. Isnt that the case?
Once again, for only extracting numerics use

Code: Select all

DIGITS(in.Col) 
OR
OCONV(in.Col, "MCN") 

for only alphabets use

Code: Select all

LETTERS(in.Col)
OR
OCONV(in.Col, "MCA") 

If you want everything except numerics then use

Code: Select all

OCONV(in.Col, "MC/N") 

This is what will get you A- and A+

And at last, if you need everything except alphabets then use

Code: Select all

OCONV(in.Col, "MC/A") 

Forget the EREPLACE() or CONVERT() functions.

[rajeevm]

Hi DSguru,

I am not confusing you just let me be clear with this

I have a source column which is a combination of A,A+,B,B-,A- , C,D,E,S,U, 0,1,2,3,5,10,20,25,30,99,100 and I have two columns in the target which are letter and Number , the letters from the source should go into the letter column and the numbers alltogether into the Number column . But for the letters its not only A,B,C,D,E,U,S the target should also have A+,B-,A-,B+ etc . for that I am using the functions mentioned by you . when i ran the job and view the data i could not able to see the A+,A-,B-,B+ etc., how do i capture this in the target column .

I think i am clear now .

Please suggest me the valuable inputs.

Thanks

[DSguru2B]

Code: Select all

OCONV(in.Col, "MC/N") 

Use the above for alphabet column. This will give you A-, B+ etc.

Code: Select all

OCONV(in.Col, "MCN") 

Use the above for numeric field. This will give you only numbers.