Typical Warning : Implicit conversion from source type

[hemachandra.m]

Query:-

Code: Select all

SELECT a.ABCDEFGHIJ , 
               Cast(COUNT(a.AGENT_ID) as Integer) AS TOT_AGENT_ID
FROM TABLE_AAAA  a 
LEFT OUTER JOIN 
TABLE_CCCCC  c 
on a.AGENT_ID=c.AGENT_ID
WHERE 
a.XYZ <= TO_DATE('#p_Rundate#','YYYY-MM-DD') - 1
AND a.ZYX >= TO_DATE('#p_Rundate#','YYYY-MM-DD') - 1 
AND Trim(c.ABC)='XXXXXX' 
GROUP BY a.ABCDEFGHIJ

I am using oracle enterprise stage to extract the data from XXXXXX table by using user defined SQL query (Query Mentioned in the CODE path). Meta data imported from table, and it is like Column Name = TOT_AGENT_ID, Datatype = Bigint, Nullable = YES.

Job is running without any errors , but only one warning message I am getting with:

Warning:-
When checking operator: When binding output interface field "TOT_AGENT_ID" to field "TOT_AGENT_ID": Implicit conversion from source type "decimal[38,0]" to result type "int64": Possible range limitation.


Job Design is Like:

Oracle Stage  Transformer Stage  Dataset Stage

Metadata is same for entire flow ( Including Datatype, Length and Nullability). No where mentioned source datatype as DECIMAL in Datastage job and Oracle.

What would be the cause of this warning?
Where I have done wrong?

Can anybody please assist?

Thanks in advance.

[ArndW]

What data type is AGENT_ID, or are there any decimal data types involved at all?

[hemachandra.m]

@Arnd

The Actual data type of AGENT_ID is Number [10,0] in oracle and in datastage i have mentioned as Bigint.
No ware in Orcale and datastage contains Decimal datatype.

[ArndW]

But you aren't using AGENT_ID in DataStage, at least according to your SQL. The number datatype is represented as decimal in DataStage.

[ray.wurlod]

Oracle is ... different.

Storage is reported as NUMERIC and, by default, precision 38. That's too big for a BigInt, so DataStage raises an alert.

If you can be 100% confident that the numbers will always be small enough to be represented as BigInt, then you can ignore, even demote, this warning. But annotate the job that you have done so.

[chulett]

That derived data type - i.e. your count() result - would be considered to be NUMERIC which is a float when unbounded... and, as noted, too big for a BigInt.