Regarding pivot stage

[vijaykumar]

Hi,
I created a table in oracle database using following query
create table salma(customerid number, ename varchar2(20),jansales decimal(12,2),febsales decimal(10,2))

My design is odbc---->pivot---->odbc.

Iam calling this table in my source odbc stage.

when i run the job ,iam getting peculiar error.

ob2..ODBC_0.DSLink3: DSD.BCIOpenR results of SQLColAttributes(CUSTID) gave MetaData mismatch
COLUMN.TYPE Expected = Integer Actual = Decimal.
when seeing this error.i tried to change the datatype in my created table in oracle to decimal but its not accepting.
I really feel embrassed to post this error, but please help me to analyze the error.

cheers;
vijay

[ray.wurlod]

Change CUST_ID to Integer in your job. Oracle uses NUMBER, which imports as Numeric, but SQLColAttributes() understands ODBC conventions rather than Oracle conventions. This can lead to minor inaccuracies when importing metadata from Oracle tables. You must make the metadata match.

[Saama]

Hi Gurus,
Still getting the same error.
I used the following create command in SQL.
create table saama(custid number,custname varchar2(12),jansales decimal(12,2),febsales(10,2)

In the odbc properties.
i defined my column properties as
Name sql type length
custid integer 17

i checked my table using sql command desc saama
EMPNO NUMBER
CUSTNAME VARCHAR2(19)
JANSALES NUMBER(14,2)
FEBSALES NUMBER(12,2)


ob2..ODBC_2.DSLink4: DSD.BCIOpenW call to SQLColAttributes(CUSTID) failed.
SQLSTATE=24000, DBMS.CODE=0
[DataStage][SQL Client]Invalid cursor state.

gurus please help me in analyzing problem

[us1aslam1us]

Your first query is completely different from second one now. As suggested by DSguru where have you made the change and how are you converting decimal field to integer?

[ray.wurlod]

IMPORT the table definition from Oracle. What comes in?

[vijaykumar]

Hi Gurus,

Thanks very much.
I have enabled create table in the target stage, there i forgot to change my datatype to decimal.

when i changed datatype for custid to decimal, its working fine.

cheers;
vijay

[I_Server_Whale]

You can mark the topic as resolved.