ora stage warning.

[suresh_dsx]

Hi,

I have a job

Oracle stage - > transformer - >dataset

Oracle Stage:
Cust_id decimal 38 not null

User deifned sql query: select cust_id from table_name

The job is running fine. But it is showing the below warning.

Val_ORA: Column CUST_ID floating point decimal is not fully supported; adjusting the scale.

Val_ORA: When checking operator: When binding output interface field "CUST_ID" to field "CUST_ID": Implicit conversion from source type "decimal[38,10]" to result type "decimal[38,0]": Possible precision limitation.

Solution: I converted the column in varchar 10.
User_defined_query: select to_char(CUST_ID) AS CUST_ID from table_name.

In the transformer -> converted in to the asinteger(CUST_ID).


i have another job which contains the 7 columns and data types decimal.

i am getting the same warnings. how can i handle this situation in parallel jobs.

Thanks and Regards,
Suresh

[chulett]

Search. You're not the first person to have this issue.

[dspxlearn]

Your warning gives you the hint and solution.

Code: Select all

Val_ORA: Column CUST_ID floating point decimal is not fully supported; adjusting the scale. 

Val_ORA: When checking operator: When binding output interface field "CUST_ID" to field "CUST_ID": Implicit conversion from source type "decimal[38,10]" to result type "decimal[38,0]": Possible precision limitation. 

Check the decimal lengths of your source and target of the field 'CUST_ID'.

[suresh_dsx]

In the oracle table column datatype/length
Cust_id not null Number 38

In data stage, oracle stage
cust_id not null decimal 38

i specified only the length 38, no need to specify the scale. Any way the scale value will be 0.

All the stages in the job, I gave the data type is Decimal and length is 38.

Please share with me if any one knows the solution.

Thanks and Regards,
Suresh

[suresh_dsx]

In the oracle table column datatype/length
Cust_id not null Number 38

In data stage, oracle stage
cust_id not null decimal 38

i specified only the length 38, no need to specify the scale. Any way the scale value will be 0.

All the stages in the job, I gave the data type is Decimal and length is 38.

Please share with me if any one knows the solution.

Thanks and Regards,
Suresh

[Teej]

Chris is right -- use the search engine.

Even though you think you do not have a scale, Oracle is treating NUMBER as 38,10. It could be just an integer, and it could be a full blown decimal field. The closest equivalent SQL field is Decimal[38,10] which is what DataStage chose to use.

Just suppress the warning message if you feel it is not important. I am quite sure someone else left instructions on how to do that (or use the Director help documentation).

[suresh_dsx]

if i give the actual length for all the columns same in the source and target. it is working fine.

But in my oracle stage
user defined Sql: select nvl(a.col1,999) as col1,a.col2,b.col3 from table a

when ever i use the functions like nvl, to_char, min,max and so on in the sql query it is showing that error.

Please let me know how we can handle this in parallel

[Teej]

Don't use those functions?

:D

Seriously, there are very few reasons why you should be using the database-specific functions within a SQL compared to doing it within DataStage. Only use it to specifically gather the proper data in order to transform. Don't transform within the SQL.

[ray.wurlod]

The functions themselves are documented as returning specific data types. Importing table definitions can not help with the data type returned by any function you may choose to use in your SQL. You can, of course, research the data type and amend the imported table definition or the columns within the particular stage so as to match what the function returns.