Hi all,
my server job is a oracle -> transformer-> oracle stage. in source i have three columns as date,month(eg: JAN,DEC...format),year(all three of varchar2 datatype) but in target these three should be mapped to a date column in target. so i used a routine to map these source columns to target. but when it is inserted into the target table i am getting an error
"ORA-01858: a non-numeric character was found where a numeric was expected"
my requirement in short:
source
c1 c2 c3
eg: 10 MAR 2000
target
10-MAR-2000
(where c1,c2,c3 are column 1,2,3 respectively)
thanks in advance.
Problem when dealing with Date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 15
- Joined: Thu Jul 06, 2006 8:59 am
Problem when dealing with Date
Balaji.
Date Problem
Output format is YYYY-MM-DD 00:00:00 for Oracle dates. The way you have the date listed I would do an Iconv on the Input and Oconv to Oracle Format
Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).
Try doing a concatenation of the three columns.
output col=c1:"-":c2:"-"c3.
Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).
Code: Select all
my requirement in short:
source
c1 c2 c3
eg: 10 MAR 2000
target
10-MAR-2000
output col=c1:"-":c2:"-"c3.
Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.
And also try to use Iconv and Oconv as " mcolen" mentioned.
Thank you
meena wrote:Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).
Try doing a concatenation of the three columns.Code: Select all
my requirement in short: source c1 c2 c3 eg: 10 MAR 2000 target 10-MAR-2000
output col=c1:"-":c2:"-"c3.
Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.
Thank you
-
- Participant
- Posts: 15
- Joined: Thu Jul 06, 2006 8:59 am
meena wrote:And also try to use Iconv and Oconv as " mcolen" mentioned.
meena wrote:Hi balajimadhav ,
You have three columns(date,month,year) in the oracle table(source) and one column to be loaded in oracle table(target).
Try doing a concatenation of the three columns.Code: Select all
my requirement in short: source c1 c2 c3 eg: 10 MAR 2000 target 10-MAR-2000
output col=c1:"-":c2:"-"c3.
Check whether this works or not. And if you want to convert this to timestamp then concatenate the competele derivation with 00:00:00. I am not sure but trying to give an idea.
Thank you
Hi all,
thanks for your Suggestion. i concatenated all the three columns in the format yyyy-mm-dd but still i got the error and i did a Iconv and Oconv. i got the result as required.
thank you.
Balaji.
Bottom line is it really doesn't matter what order you stick the pieces together in, or what delimiter you use - as long as it matches the format mask used in the OCI stage.
Best Practice is to stick with ISO formatted dates, those in YYYY-MM-DD format, and let the OCI stage automatically generate the appropriate sql for you. None of this pain-in-the-ass-to-maintain custom sql just for that.
Best Practice is to stick with ISO formatted dates, those in YYYY-MM-DD format, and let the OCI stage automatically generate the appropriate sql for you. None of this pain-in-the-ass-to-maintain custom sql just for that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers