Loading the Target
Moderators: chulett, rschirm, roy
Loading the Target
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
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
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
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
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
Last edited by ds_developer on Mon Apr 09, 2007 4:06 pm, edited 1 time in total.
Its a transform functioin. Its syntax is exactly what I gave you.
where in.Col is your incoming column.
You can also use OCONV with MCN.
Code: Select all
DIGITS(in.Col)
You can also use OCONV with MCN.
Code: Select all
OCONV(in.Col, "MCN")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
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
for only alphabets use
If you want everything except numerics then use
This is what will get you A- and A+
And at last, if you need everything except alphabets then use
Forget the EREPLACE() or CONVERT() functions.
Once again, for only extracting numerics use
Code: Select all
DIGITS(in.Col)
OR
OCONV(in.Col, "MCN")
Code: Select all
LETTERS(in.Col)
OR
OCONV(in.Col, "MCA")
Code: Select all
OCONV(in.Col, "MC/N")
And at last, if you need everything except alphabets then use
Code: Select all
OCONV(in.Col, "MC/A")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
Code: Select all
OCONV(in.Col, "MC/N")
Code: Select all
OCONV(in.Col, "MCN")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.