Page 1 of 1
About Oconv & Iconv
Posted: Sun Jun 03, 2007 11:56 pm
by loe_ram13
At Source:
I have a '13-may-07' value stored as string.
After transformation ,I convert it to date as I have to load that data in to a table where that column datatype in the table is Date.
If I use
Oconv & Iconv then error message is thrown.
But If i change the target datatype to timestamp after transformation,same transformation logic works fine!!!!
Kindly help....
Posted: Mon Jun 04, 2007 1:08 am
by ray.wurlod
Be more specific about the arguments you used with Oconv() and Iconv(). Without that information it is impossible to provide any diagnosis. We also need the exact text of the error message that is thrown in your first example, and information about when it is thrown (compile time, run time, etc.).
Oconv & Iconv
Posted: Mon Jun 04, 2007 2:34 am
by loe_ram13
ray.wurlod wrote:Be more specific about the arguments you used with Oconv() and Iconv(). Without that information it is impossible to provide any diagnosis. We also need the exact text of the error message that is t ...
My source is:
start_date varchar2 (10) (Format of data is:'12-may-07')
My desired target is s
start_date date (format should be '2007-05-12')
My transformation logic for the same is:
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,3,2]"),"D-YMD[4,2,2]")
But if I keep the target datatype as Date,I am getting warning..
If I keep it as TIMESTAMP,it is getting solved.
Kindly help......
Posted: Mon Jun 04, 2007 6:49 am
by chulett
Your conversion seems fine. However, you haven't told us what your target database / table is, the nature of your sql nor what the actual error is that you get. So...
Database?
Target field type in database (not the job)?
SQL is generated or user defined? Post it.
Error when target field is declared as Date in job?
Posted: Tue Jun 05, 2007 1:10 am
by ray.wurlod
Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.
Code: Select all
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[4,2,2]")
Easier to maintain would be
Code: Select all
Oconv(Iconv(ToT1.START_DATE,"D-DMY"),"D-YMD[4,2,2]")
Posted: Tue Jun 05, 2007 4:39 am
by chulett
ray.wurlod wrote:You have omitted "A" for the month.
Dang, missed that.
Posted: Tue Jun 05, 2007 11:02 pm
by loe_ram13
ray.wurlod wrote:Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.
Code: Select all
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[ ...[/quote]
My transformation logic is:
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-DMY[2,2,4]")
After transformation,My target database column has the datatype as Date.
I am using the following sql qery that is generated..
UPDATE #SchemaSum#.S_MCD_LASTCDR SET IN_VOICE_LAST_DT=TO_DATE(:2, 'DD-MON-YY'),RECORD_DATE=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS') WHERE MIN_NO=:1;
INSERT INTO #SchemaSum#.S_MCD_LASTCDR (MIN_NO,IN_VOICE_LAST_DT,RECORD_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'))
I am getting the following warning.
ic_Mcd_update..T1: At row 987, link "To_MCD", while processing column "IN_VOICE_LAST_DT"
Value treated as NULL
Attempt to convert String value "29-05-2007" to Date type unsuccessful
In my target table that column is updated as NULL.
kindly help
Posted: Wed Jun 06, 2007 12:21 am
by georgesebastian
loe_ram13 wrote:ray.wurlod wrote:Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.
Code: Select all
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[ ...[/quote]
My transformation logic is:
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-DMY[2,2,4]")
After transformation,My target database column has the datatype as Date.
I am using the following sql qery that is generated..
UPDATE #SchemaSum#.S_MCD_LASTCDR SET IN_VOICE_LAST_DT=TO_DATE(:2, 'DD-MON-YY'),RECORD_DATE=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS') WHERE MIN_NO=:1;
INSERT INTO #SchemaSum#.S_MCD_LASTCDR (MIN_NO,IN_VOICE_LAST_DT,RECORD_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'))
I am getting the following warning.
ic_Mcd_update..T1: At row 987, link "To_MCD", while processing column "IN_VOICE_LAST_DT"
Value treated as NULL
Attempt to convert String value "29-05-2007" to Date type unsuccessful
In my target table that column is updated as NULL.
kindly help[/quote]
Hi,
In the tranformer change the data type of the target date column to Varchar .I will work
Thanks
George
Posted: Wed Jun 06, 2007 5:59 am
by chulett
Your problem is the fact that you are converting the date
from the format it is expecting to one that it can't use. Check your generated SQL - see the TO_DATE function and the mask it is expecting? Look familiar? What you have will work "as is",
stop converting it. 
Re: About Oconv & Iconv
Posted: Thu Jun 07, 2007 12:15 am
by sachin1
hello, as replied by George it works fine, also by changing data-type of date in OCI stage to timestamp, and without applying any format specification it will work.