type conversions

[mirogak]

Hi folks,

I am new to this forum and to Informatica, and looking for your help ...

I have a SQL and related PC Designer question

Running PC 7.1.5, pulling from Oracle 8i and pushing to Oracle 8i

Ok here is the thing:

In Oracle when I create a join between two tables and the join columns have incompatible data types, then Oracle spits out an error and stops the query (ORA-01722)

HERE IS THE QUERY
=================
SELECT
A.LOOKUP_CODE,
A.MEANING
FROM
INV.ITEMS_B I,
APPLSYS.LOOKUP_VALUES A
WHERE
I.ATO_FORECAST_CONTROL = A.LOOKUP_CODE(+)

So the above doesn't work because ATO_FORECAST_CONTROL is a number and LOOKUP_CODE is a String (Varchar2)

However, if I append this line to the above query in the WHERE clause, which simply gives me a subset of the LOOKUP_VALUES table, then I don't get an error ....

AND
A.LOOKUP_TYPE = 'MRP_ATO_FORECAST_CONTROL'

WHY IS THIS SO ... I still have incompatible data types ...

Desginer Question
-----------------
When I try to do the same in Designer, it gives me an error (mapping is INVALID) because of the incompatibility of the join columns. I tried to change the data type for that particular column (ATO_FORECAST_CONTROL) in the Source Qualifier, I still got an error (Invalid mapping). Then I tried to change it in an Expression transformation, so I did. It is valid. I ran it but all rows rejected. I am probably doing something else wrong, which I'll fix.

But here are my two questions
1. Why is Oracle SQL behaving like that (as mentioned above)
2. Should I be using the expression transformation to change the data type

????

Thanks in advance
mirogak

 

[blom0344]

Oracle is using something defined as 'implicit conversion'
To a certain degree it permits the use of incompatible datatypes (like calculation with a char type and a numeric type.)
It is not so generous to allow this in joins.

You can either define a join by casting one of the datatypes to match the other (can be hurtful to performance)
or change the datatype of both fields to a matching one and perform the join after that.

Use the proper INFA function to set the datatype!

Ties Blom

 

[mirogak]

Thanks Blom,

Where would I do the conversion using the proper INFA (assuming this stands for Informatica) function. Should this occur in a separate Expression Transformation?

mirogak,

 

[blom0344]

In that scenario read the sources through 2 seperate Source qualifiers and prior to joining these 2, make sure to use an expression transformation to do the datatype conversion.

Ties Blom