Zoned data type(Urgent)

[Chanti123]

What is zoned data type? Can Informatica work with Zoned data type?
My problem is I have a flat file with combination data of both characters and numbers. They are calling it as zoned data type. This value they want us to load into oracle (in oracle the data type is number for this) so they want to convert the zoned value into number. Is there nay way I can do with informatica? When I tried to load data with out using any transformation, it was loading wrong data.
For ex:
In flat file the data is
100{
10A0
B345
2000
It is loading into oracle correspondingly as
100
10
0
2000
What transformation can I use to make the whole value into number?
Is there anyway to convert a non-numerical value into numeric value?
Thanks in advance,
Chanti.

 

[blom0344]

Are you kidding?

A value like 'B345' is a string. If you handle the data as if they are strings INFA will accept the integers as such.

The proper datatype in your target should be a varchar, cause it enables you to store both numerical (as string type)as well as string data. A numerical datatype is restricted to such only.....

Do not make this more complicated than necessary!

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ArtieChoke]

Search google for zoned data. Typically, it includes the sign in one of the characters. It can also store the digits in each byte, so what you are seeing is really a hexidecimal character representation of the number - that's why you see some characters like A and B in there.

I don't see any data types that will handle this - you'll either have to get the data corrected at the source or write a translation procedure to handle it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Chanti123]

Blom,
Yes really I need to work with that type of scenario. If at target level I have Varchar type I need not put my question in forums. But the target data field is numeric. I have to go for integer data type, bz the final value we use for calculations.

ArtieChoke,
I did search for Zoned data type in Google. That type we can have only in SQL*Loader. Yes what you said about Zoned data type is true. But still it have special characters ike } and { and not only up to F(hexadecimal contains only still F). I have some characters are up to J. I did try by converting into hexadecimal. At oracle level. But the resulted data again holding D where ever there is }. So again I can’t insert that data into a number.
So what I finally thought is by using SQL loader at Informatica level as external loading. Again I don’t have much idea how to use that. Could you please le me know, how to use that? Or else do you have any other idea then this. Please let me know, I appreciate your help.
Thanks in advance,
Milinda.

 

[ArtieChoke]

Where did you get this data? Maybe if we know exactly the zone formatting, we can modify it before it gets to PowerCenter. Can you get specifics from the supplier of the data?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Chanti123]

ArtieChoke,
what do you mean by zone formatting? I dint get you exactly, so far i tried to implement SQL loader through informatica command task by creating a control file. but I am getting errors. Do you know any other process. Please let me know what do you mean by zone formatting, I can sure get those details.
Thanks,
Chanti.

 

[ArtieChoke]

I mispoke (mistyped? ). Can you get the specs for the zone data they are giving you?

"I think we're all Bozos on this bus!" - Firesign Theatre