I have catalogs building a data mart from a vendor database which can be on either MSSQL or Oracle. There are fields in this data base with hex values ('FAD66C36', for example) stored as CHAR and I need them in decimal. I have the same initial query for both platforms, so I want to convert these hex values to decimal within the data stream using a derivation, but have so far been unsuccessful. Anyone done this before? Thanks!
I'm not aware of any inbuilt functions to convert hex into decimal. If you have the option, you could probably code something to do it in C or Java (or VB), compile and link it into a DLL, then call it as an external UDF from within Data Manager. Alternatively, are there any native database functions that would do this?
Cheers!
MF.
Forgive my ignorance as I'm not a DS/DM user myself, but if you can call function or stored procs from there you could certainly make one for each database. Might be easier than the whole C/Java/VB/whatever process.