Modify Stage

[medakiran]

Hi,

I'm trying to convert a data type of a field from "UnKnown" to "Date" using the Modify Stage, but to no success. So, can plz someone, help me out!!!!!

Plz tell the "Specification" that i need.

Thank you,
kiran

[kumar_s]

Hi,
I hope the type unknown comes for top level catogory for any cobol layout.
In that case did you tried stringtodate funciton.

regards
kumar

[medakiran]

Hi,
I hope the type unknown comes for top level catogory for any cobol layout.
In that case did you tried stringtodate funciton.

regards
kumar

Hi Kumar,

I tired using the date_from_string function and that did'nt work
here's what i'm trying to do
"FieldNm:Date = date_from_string(New_FieldNm)"

Any Suggestions...

Thanks,
kiran

[ray.wurlod]

From where did you import the table definition?

[medakiran]

From where did you import the table definition?

Hi Ray,
The header in .csv file was not in a single line, so i had to copy the header to a .txt file and brought everything in one line, then only I was able to imort the table definitions. So, in this process i lost the actuall data types for the fields(196 fields) and got the data type "UnKnown" for all the fields.

Regards,
kiran

[ray.wurlod]

In the Table Definition in the Repository edit the SQL types to what they should be. Then reload the table definition into the job so that there are no "Unknown" data types.

[kumar_s]

Hi,
If you are reading it from sequential file, why cant you read it as varchar or decimal as required???

regards
kumar

[medakiran]

Hi,
If you are reading it from sequential file, why cant you read it as varchar or decimal as required???

regards
kumar

Hi kumar,

how shall i read the field with date type. its giving me errors.

thanks
kiran

[kumar_s]

Pls let me know more about your circustance,

If the situtuation is as....
input is sequential file with delimited data,
its obvious that, whatever the case be, varchar holds good for all the case.
it can be integer or even special character(excepts the feild delimter or record delimter).

Pls also post the warning or error you get while reading as varchar.

regards
kumar

[medakiran]

Pls let me know more about your circustance,

If the situtuation is as....
input is sequential file with delimited data,
its obvious that, whatever the case be, varchar holds good for all the case.
it can be integer or even special character(excepts the feild delimter or record delimter).

Pls also post the warning or error you get while reading as varchar.

regards
kumar

Hi kumar,

I'm actually reading the data as VarChar, but my requirement is to read certain fields as "Date" data type. i have no problem reading the data if i read as Varchar, but when i try to read certain fields related
to data data type, i 'm gettin the following error:
"##W TOIX 000000 08:24:07(000) <Sequential_File_49,0> Field "PMT_NEXT_DUE_DATE" has import error and no default value; data: {0 5 / 0 1 / 2 0 0 5}, at offset: 172
##W TOIX 000154 08:24:07(001) <Sequential_File_49,0> Import warning at record 0:
##W TOIX 000018 08:24:07(002) <Sequential_File_49,0> Import unsuccessful at record 0:
##W TOIX 000000 08:24:07(003) <Sequential_File_49,0> Field "PMT_NEXT_DUE_DATE" has import error and no default value; data: {0 4 / 0 1 / 2 0 0 5}, at offset: 172
##W TOIX 000154 08:24:07(004) <Sequential_File_49,0> Import warning at record 1:
##W TOIX 000018 08:24:07(005) <Sequential_File_49,0> Import unsuccessful at record 1:
##W TOIX 000000 08:24:07(006) <Sequential_File_49,0> Field "PMT_NEXT_DUE_DATE" has import error and no default value; data: {0 5 / 0 1 / 2 0 0 5}, at offset: 172
##W TOIX 000154 08:24:07(007) <Sequential_File_49,0> Import warning at record 2:
##W TOIX 000018 08:24:07(008) <Sequential_File_49,0> Import unsuccessful at record 2:
##W TOIX 000000 08:24:07(009) <Sequential_File_49,0> Field "PMT_NEXT_DUE_DATE" has import error and no default value; data: {0 5 / 0 1 / 2 0 0 5}, at offset: 172
##W TOIX 000154 08:24:07(010) <Sequential_File_49,0> Import warning at record 3:
##W TOIX 000018 08:24:07(011) <Sequential_File_49,0> Import unsuccessful at record 3:
##W TOIX 000000 08:24:07(012) <Sequential_File_49,0> Field "PMT_NEXT_DUE_DATE" has import error and no default value; data: {0 5 / 0 1 / 2 0 0 5}, at offset: 172
##W TOIX 000154 08:24:07(013) <Sequential_File_49,0> Import warning at record 4:
##W TOIX 000018 08:24:07(014) <Sequential_File_49,0> Import unsuccessful at record 4:
##I TOIX 000157 08:24:07(015) <Sequential_File_49,0> (no further reports will be generated from this partition until successful import)
##I TOIX 000163 08:24:07(016) <Sequential_File_49,0> Import complete. 0 records imported successfully, 37 rejected."

For this reason i'm reading the field as VarChar.

So, thats my problem.
Kindly help me on this, thanks
kiran

[kumar_s]

Hi,
So from your post i could understand that you can read all feild as varchar.
Now you can start playing with the inbuilt fuction of datastage to make the type casting. stringtodate().....
I also suspect bad data.... (if your data file as more that 34 record).
Try giving default date to the "PMT_NEXT_DUE_DATE" feild. some standard values like 9999-01-01 or 01-01-0001.

regards
kumar

[ray.wurlod]

Change your invalid requirement to read certain fields as "Date" data type - there are no data types in text files, only text.

Transmogrify it into a date once you've gotten it into DataStage.