Rr 4035 sql error

Communities

Knowledge Center

Learn

Resources

FOLLOW THE BLOG

FACE BOOK

RR_4035 SQL ERROR Fetch failed

Posted by ashwinsestar on October 2, 2011

PROBLEM

SqL error “fetch failed” Occurs whenever the User specified sql query in the source qualifier doesnt match the ports defined in the source qualifier.In such cases you get an error similar to the one mentioned below:

2011-09-12 11:26:50 : ERROR : (26982 | READER_1_1_1) : (IS | int_etldev_ecods) : node01_apsr9057 : RR_4035 : SQL Error [
FnName: Fetch — [Informatica][ODBC SQL Server Driver]20125].
2011-09-12 11:26:50 : ERROR : (26982 | READER_1_1_1) : (IS | int_etldev_ecods) : node01_apsr9057 : BLKR_16034 : ERROR: Fetch failed.

For eg;

Let us consider a sql query as below:

Select Name,id,address from employee

And you define the ports as below missing some column then we get this error

SOLUTION

check the format in which the columns in the source qualifier is defined and see if all these column ports are present in same order.
For the above example we need define the source qualifier ports as

If you miss any of the column port or add any of the extra port without defining in user specified sql query then such failures occur

Источник

Workflow Failing with RR 4035 SQL Error

I am using Informatica 8.1. I have gone through all the replies mentioned for RR 4035 error. Here is mine: I am trying to fetch a table from my database (SQL Server 2005). I create the mapping. Create the Target. Map the columns. Go to Workflow Manager. Create the Task and Workflow. Run the workflow and get the following error:

Node: node01_wks10936 Thread: READER_1_1_1 Message Code: RR_4035 Message: SQL Error [ Database driver error. Function Name : Execute SQL Stmt : SELECT Marks.FirstName, Marks.LastName, Marks.Age, Marks.Gender, Marks.Score, Marks.City FROM Marks].

Popular Topics in Data Management

See below regarding RR 4035 error.

RR_4035 SQL error.

Cause: Error executing SQL query.

Action: Make sure the query can run using SQL Override in the Source Qualifier transformation. Also, verify that the source definition name matches the table name in the source database. Otherwise, contact Informatica Global Customer Support.

see below regarding RR 4035 error.

RR_4035 SQL error.

Cause: Error executing SQL query.

Action: Make sure the query can run using SQL Override in the Source Qualifier transformation. Also, verify that the source definition name matches the table name in the source database. Otherwise, contact Informatica Global Customer Support.

Thanks for your reply.

1) I didnt get the SQL Override point. Will have to chk with other colleagues.

2) But, I have checked that, the Source Definition name is same as the Table Name in Source Database. So this one is OK.

Just need to ask one thing:
Once the mapping is done, in the Workflow Manager, when we double click on the Task and click on the Mappings Tab, under the Sources on the left side, we get the Owner Name attribute. Do we need to fill it with the owner name or we leave it blank ?

If you are overriding the default SQL in your source qualifier or lookup transformations, check the order of the ports in the transformation is the same as your SQL override.

If the database user specified in the database connection is not the owner of the target tables in a session, specify the table owner for each target instance. Otherwise you can leave it blank.

Thanks for the suggestions. I guess I have cross checked all what you have mentioned. I have just listed the steps I am following ith some details about the database and the tables. Not sure whether this is the right way/platform to clarify in such a detailed manner, but I really wanna get this thing working.

Kindly suggest after reading:

IN SQL Server 2005, I have the following observations:

In Database Properties window:

In General TAB: Database Name = Student and Database Owner = QGCP-QAM15
In Permissions TAB: Under «Explicit Permissions For Student», I have Checked all the boxes under GRANT.

Now coming to Table properties window for the above database:

Under the Student Database, I have the table Student.Marks
Where: Table Name = Marks and Schema = Student

I have another empty table, Student.Deposits in the same database.

Now, first, I import the Marks table into Source Analyser.
In the Import Table window, we get all the schemas with a + sign next to them.
So I click on the + next to Student and I see the Marks table listed there and click on OK.
Here, I suppose, Student is the schema name and can be treated as the owner.
Once this table is in the Source Analyser, I double click on it and it lists the Owner as Student. So confirmed.

Next, in a similar way I import the table, Deposits into the Target Designer.
But when I double click on this target, it does not say anything about an owner.

Next, in the Mapping Designer, I insert the Source and the Target.

Now, my Source Qualifier table has 6 columns and Target table has 4 columns (The Column names are different)
So I map the first four columns of Source Qualifier with the Target Table and save it.

Now in Source Qualifier > Properties > SQL Query > Generate SQL, I get the following query:
SELECT Marks.FirstName, Marks.LastName, Marks.Age, Marks.Gender
FROM Marks
— This is an invalid query saying that Invalid Object ‘Marks’.
So I add Student and change it to FROM Student.Marks and the query PASSES. (Student was the schema name)
I click on OK. Save the mapping and now move onto Workflow Manager.

Now in Workflow Manager, I goto Workflow > Wizard > Mapping Name > Select the mapping for the Session to be created.
I double click the Session > Edit Tasks > Mapping > Sources > I have a blank Owner Name.

Also, I leave the SQL Query blank in the source properties.

Run the session and again get the same error, RR 4035.

Источник

Workflow Failing with RR 4035 SQL Error

I am using Informatica 8.1. I have gone through all the replies mentioned for RR 4035 error. Here is mine: I am trying to fetch a table from my database (SQL Server 2005). I create the mapping. Create the Target. Map the columns. Go to Workflow Manager. Create the Task and Workflow. Run the workflow and get the following error:

Node: node01_wks10936 Thread: READER_1_1_1 Message Code: RR_4035 Message: SQL Error [ Database driver error. Function Name : Execute SQL Stmt : SELECT Marks.FirstName, Marks.LastName, Marks.Age, Marks.Gender, Marks.Score, Marks.City FROM Marks].

Popular Topics in Data Management

See below regarding RR 4035 error.

RR_4035 SQL error.

Cause: Error executing SQL query.

Action: Make sure the query can run using SQL Override in the Source Qualifier transformation. Also, verify that the source definition name matches the table name in the source database. Otherwise, contact Informatica Global Customer Support.

see below regarding RR 4035 error.

RR_4035 SQL error.

Cause: Error executing SQL query.

Action: Make sure the query can run using SQL Override in the Source Qualifier transformation. Also, verify that the source definition name matches the table name in the source database. Otherwise, contact Informatica Global Customer Support.

Thanks for your reply.

1) I didnt get the SQL Override point. Will have to chk with other colleagues.

2) But, I have checked that, the Source Definition name is same as the Table Name in Source Database. So this one is OK.

Just need to ask one thing:
Once the mapping is done, in the Workflow Manager, when we double click on the Task and click on the Mappings Tab, under the Sources on the left side, we get the Owner Name attribute. Do we need to fill it with the owner name or we leave it blank ?

If you are overriding the default SQL in your source qualifier or lookup transformations, check the order of the ports in the transformation is the same as your SQL override.

If the database user specified in the database connection is not the owner of the target tables in a session, specify the table owner for each target instance. Otherwise you can leave it blank.

Thanks for the suggestions. I guess I have cross checked all what you have mentioned. I have just listed the steps I am following ith some details about the database and the tables. Not sure whether this is the right way/platform to clarify in such a detailed manner, but I really wanna get this thing working.

Kindly suggest after reading:

IN SQL Server 2005, I have the following observations:

In Database Properties window:

In General TAB: Database Name = Student and Database Owner = QGCP-QAM15
In Permissions TAB: Under «Explicit Permissions For Student», I have Checked all the boxes under GRANT.

Now coming to Table properties window for the above database:

Under the Student Database, I have the table Student.Marks
Where: Table Name = Marks and Schema = Student

I have another empty table, Student.Deposits in the same database.

Now, first, I import the Marks table into Source Analyser.
In the Import Table window, we get all the schemas with a + sign next to them.
So I click on the + next to Student and I see the Marks table listed there and click on OK.
Here, I suppose, Student is the schema name and can be treated as the owner.
Once this table is in the Source Analyser, I double click on it and it lists the Owner as Student. So confirmed.

Next, in a similar way I import the table, Deposits into the Target Designer.
But when I double click on this target, it does not say anything about an owner.

Next, in the Mapping Designer, I insert the Source and the Target.

Now, my Source Qualifier table has 6 columns and Target table has 4 columns (The Column names are different)
So I map the first four columns of Source Qualifier with the Target Table and save it.

Now in Source Qualifier > Properties > SQL Query > Generate SQL, I get the following query:
SELECT Marks.FirstName, Marks.LastName, Marks.Age, Marks.Gender
FROM Marks
— This is an invalid query saying that Invalid Object ‘Marks’.
So I add Student and change it to FROM Student.Marks and the query PASSES. (Student was the schema name)
I click on OK. Save the mapping and now move onto Workflow Manager.

Now in Workflow Manager, I goto Workflow > Wizard > Mapping Name > Select the mapping for the Session to be created.
I double click the Session > Edit Tasks > Mapping > Sources > I have a blank Owner Name.

Also, I leave the SQL Query blank in the source properties.

Run the session and again get the same error, RR 4035.

Источник

Socket Closed Issue in Informatica 9.5

Popular Topics in Data Management

This error occurs due to incorrect override in your sql either in SQ/LKP transformation. Make sure all the overridden column have same & sequence in their respective ports.

For quick fix compare your PROD mapping/workflow with QA mapping/workflow. You will get to know the difference.

Hi ggurjar ,
If the override query is incorrect then, I will not execute when I
executing manual run. though. I just compared all QA and PROD. There is no
any difference between QA and PROD.
Note: our jobs are scheduled in maestro schedular Tool.
Please let me know if any other details needed.

If you refer INFA RR Messages RR_4035 has below reason-

RR_4035 SQL error.
Explanation: Error executing SQL query.

User Response: Make sure the query can run using SQL Override in the Source Qualifier transformation. Also, verify that the source definition name matches the table name in the source database. Otherwise, contact Informatica Global Customer Support.

You may get in touch with INFA Global Admin team for more details.

There is one more alternative
Find odbc.ini File in your

Locate below entry
HostName= EncryptionMethod=1 ValidateServerCertificate=1 TrustStore= TrustStorePassword= HostNameInCertificate= InitializationString= Language= [SQL Server Legacy Wire Protocol] Driver=/export/home/build_root/odbc_6.1/install/lib/DWmsss25.so Description=DataDirect SQL Server Wire Protocol Database= EnableBulkLoad=0

Источник

RR_4035 SQL Error ‘ORA-01843: not a valid month’ in Informatica

The mapping was working fine until yesterday, now it is showing this error. The fields in SQL transformation are in order. What could be the cause of this?

Ensure AV Gear Plays Nice on the Corporate Network

This part of to_date(to_char(V_ACCNT_POSTN.LAST_CALL_DT,’mm/dd/yyyy’),’mm/dd/yyyy’) doesn’t have an alias.

My guess is you need to change it to:

It looks like you are extracting from a Siebel CRM. (For Siebel Analytics target?)

There must be a new row in your source system since the last extract that is causing the SQL to fail. If so, the V_ACCNT_POSTN view will probably have the field called CREATED. Look to see what rows have been CREATED > [last run date]. Take a look at those rows and their LAST_CALL_DT field and see if there is anything weird in that column.

I’m not sure why the SQL is doing a to_char and then a to_date on the field LAST_CALL_DT. What datatype is the field?

Thanks for the input. We are extracting from Siebel CRM.

I have checked the data in CREATED in the view. All the data are perfect. And the mapping was working fine til yesterday. Today not only this mapping all other mappings which has date field in the SQL fails.

The datatype is date. We are not using the to_char and then to_date conversions. It was a suggestion given by Krishnan. But we are directly pulling the field from the view and there is no conversion since it is a datafield.

To_date and To_char is already in your code and not a suggestion from me, I only added an alias to it.

In fact I am also curious as to why you have to do that when field is of datatype Date?

This problem is coming due to bad data in your month field in date field. Like someone has entered 22-jum-2005 instead of jun.

To extract this record, just have a mapping which is having this date field, and fetch this field as varchar or char data type field from source. never ever put it as date. Check your source also for this.

And it is coming since today means you have loaded some new record I suppose which is having this invalid month field.let me know for further problem.

If you have generated sql query before you connected the column , the sql query will generate for all the rows that are in the source.

Try coding sql after you have done with the mapping connection. I hope this should help you.

Does the userid specified in DBConnection has the SELECT privilege on all the tables being accessed inside view definition? I assume this SQL is not returning much volume of data. so there is no chances of lack of space in temporary tablespace for temporary sorting etc.

Pls check the privileges first. Connect in sqlplus with the same specified userid/passwd as in your DBConnection and execute the SQL as appearing in sessionlog.

I had tried that as well.

SELECT
TO_DATE(VW_ORDERDATA.SVCORDER_DT,’MM/DD/YYYY’)
FROM
asap_report.VW_ORDERDATA

Throws the same error. I tried ‘DD-MON-YYYY’ also.

Is datatype of SRCORDER_DT column is DATE ? If so then you are converting date column back to date again.

Are you passing a date column in source qualifier into a char column? Data type should be the same in sql as well in source qualifier ports.

Follow the below procedure.

Suppose you want to convert i_date into date format. First take i_date as of Sting data type and then check this.

IIF(isdate(i_date,’MM/DD/YYYY’),to_date(i_date,’MM/DD/YYYY’),
IIF(isdate(i_date,’ MM/DD/YYYY ‘),to_date(i_date,’ MM/DD/YYYY ‘)))

The above condition first checks for the format of the date. If is in any format then it converts into that format. If its not matching any of our requirements then it will return NULL. Just check your required format.

I do not understand as to why you want to convert a date to Character and then back to date. Further that being said you are not having a alias for that converted field.

Further I am seeing a space between first y and second y of YYYY while converting to character. This is a typo.

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.

Read these next.

poor wifi, school’s third floor

I work as a help desk technician at a high school for a school district. Teachers/students on the building’s third floor have been reporting poor wifi, with their Chromebooks/laptops etc experiencing slow connectivity and random disconnections. We hav.

Need help crafting a job posting for an IT Pro

I’d really appreciate some thoughts and advice. I’m looking to hire an IT pro to be our resident go-to for all things IT (device support, SQL Server, network admin, etc) but who also is interested in learning — or even has some experience in — the.

Snap! — AI Eye Contact, Mine Batteries, Headset-free Metaverse, D&D Betrayal

Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: January 13, 1874: Adding Machine Patented (Read more HERE.) Bonus Flashback: January 13, 1990: Astronauts awakened to the song Attack of the Killer Tomatoes (Read mor.

Spark! Pro series – 13th January 2023

Happy Friday the 13th! This day has a reputation for being unlucky, but I hope that you’ll be able to turn that around and have a great day full of good luck and good fortune. Whether you’re superstitious or not, .

Источник

I am trying  my first ETL on OBIA 7.9.6.4

i’m using  Oracle EBS 12.1.1 as source system.

the ETL completes 314 tasks successfully ,but it fails the task named:

«SDE_ORA_GL_AR_REV_LinkageInformation_Extract»

DAC Error log:

=====================================

STD OUTPUT

=====================================

Informatica(r) PMCMD, version [9.1.0 HotFix2], build [357.0903], Windows 32-bit

Copyright (c) Informatica Corporation 1994 — 2011

All Rights Reserved.

Invoked at Wed Sep 18 09:46:41 2013

Connected to Integration Service: [infor_int].

Folder: [SDE_ORAR1211_Adaptor]

Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

Instance: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

Mapping: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract]

Session log file: [C:Informaticaserverinfa_sharedSessLogs.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.log]

Source success rows: [0]

Source failed rows: [0]

Target success rows: [0]

Target failed rows: [0]

Number of transformation errors: [0]

First error code [4035]

First error message: [RR_4035 SQL Error [

ORA-00904: «XLA_EVENTS».»UPG_BATCH_ID»: invalid identifier

Database driver error…

Function Name : Execute

SQL Stmt : SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_COMBINATI]

Task run status: [Failed]

Integration Service: [infor_int]

Integration Service Process: [infor_int]

Integration Service Grid: [infor_int]

—————————-

Node Name(s) [node01_AMAZON-9C628AAE]

Preparation fragment

Partition: [Partition #1]

Transformation instance: [SQ_XLA_AE_LINES]

Transformation: [SQ_XLA_AE_LINES]

Applied rows: [0]

Affected rows: [0]

Rejected rows: [0]

Throughput(Rows/Sec): [0]

Throughput(Bytes/Sec): [0]

Last error code [16004], message [ERROR: Prepare failed. : [

ORA-00904: «XLA_EVENTS».»UPG_BATCH_ID»: invalid identifier

Database driver error…

Function Name : Execute

SQL Stmt : SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_CO]

Start time: [Wed Sep 18 09:46:13 2013]

End time: [Wed Sep 18 09:46:13 2013]

Partition: [Partition #1]

Transformation instance: [W_GL_LINKAGE_INFORMATION_GS]

Transformation: [W_GL_LINKAGE_INFORMATION_GS]

Applied rows: [0]

Affected rows: [0]

Rejected rows: [0]

Throughput(Rows/Sec): [0]

Throughput(Bytes/Sec): [0]

Last error code [0], message [No errors encountered.]

Start time: [Wed Sep 18 09:46:14 2013]

End time: [Wed Sep 18 09:46:14 2013]

Disconnecting from Integration Service

Completed at Wed Sep 18 09:46:41 2013

——————————————————————————————————

Informatica session logs:

DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].

DIRECTOR> VAR_27028 Use override value [ORA_R1211] for session parameter:[$DBConnection_OLTP].

DIRECTOR> VAR_27028 Use override value [.SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full.ORA_R1211.log] for session parameter:[$PMSessionLogFile].

DIRECTOR> VAR_27028 Use override value [26] for mapping parameter:[$$DATASOURCE_NUM_ID].

DIRECTOR> VAR_27028 Use override value [‘N’] for mapping parameter:[$$FILTER_BY_LEDGER_ID].

DIRECTOR> VAR_27028 Use override value [‘N’] for mapping parameter:[$$FILTER_BY_LEDGER_TYPE].

DIRECTOR> VAR_27028 Use override value [] for mapping parameter:[$$Hint1].

DIRECTOR> VAR_27028 Use override value [01/01/1970] for mapping parameter:[$$INITIAL_EXTRACT_DATE].

DIRECTOR> VAR_27028 Use override value [01/01/1990] for mapping parameter:[$$LAST_EXTRACT_DATE].

DIRECTOR> VAR_27028 Use override value [1] for mapping parameter:[$$LEDGER_ID_LIST].

DIRECTOR> VAR_27028 Use override value [‘NONE’] for mapping parameter:[$$LEDGER_TYPE_LIST].

DIRECTOR> TM_6014 Initializing session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] at [Wed Sep 18 09:46:13 2013].

DIRECTOR> TM_6683 Repository Name: [infor_rep]

DIRECTOR> TM_6684 Server Name: [infor_int]

DIRECTOR> TM_6686 Folder: [SDE_ORAR1211_Adaptor]

DIRECTOR> TM_6685 Workflow: [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] Run Instance Name: [] Run Id: [2130]

DIRECTOR> TM_6101 Mapping name: SDE_ORA_GL_AR_REV_LinkageInformation_Extract [version 1].

DIRECTOR> TM_6963 Pre 85 Timestamp Compatibility is Enabled

DIRECTOR> TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS]

DIRECTOR> TM_6827 [C:Informaticaserverinfa_sharedStorage] will be used as storage directory for session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full].

DIRECTOR> CMN_1805 Recovery cache will be deleted when running in normal mode.

DIRECTOR> CMN_1802 Session recovery cache initialization is complete.

DIRECTOR> TM_6708 Using configuration property [DisableDB2BulkMode ,Yes]

DIRECTOR> TM_6708 Using configuration property [OraDateToTimestamp ,Yes]

DIRECTOR> TM_6708 Using configuration property [overrideMpltVarWithMapVar,Yes]

DIRECTOR> TM_6708 Using configuration property [SiebelUnicodeDB,[APPS]@[ 54.225.65.108:1521:VIS] [DWH_REP2]@[AMAZON-9C628AAE:1521:obiaDW1]]

DIRECTOR> TM_6703 Session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] is run by 32-bit Integration Service  [node01_AMAZON-9C628AAE], version [9.1.0 HotFix2], build [0903].

MANAGER> PETL_24058 Running Partition Group [1].

MANAGER> PETL_24000 Parallel Pipeline Engine initializing.

MANAGER> PETL_24001 Parallel Pipeline Engine running.

MANAGER> PETL_24003 Initializing session run.

MAPPING> CMN_1569 Server Mode: [ASCII]

MAPPING> CMN_1570 Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]

MAPPING> TM_6151 The session sort order is [Binary].

MAPPING> TM_6156 Using low precision processing.

MAPPING> TM_6180 Deadlock retry logic will not be implemented.

MAPPING> TM_6187 Session target-based commit interval is [10000].

MAPPING> TM_6307 DTM error log disabled.

MAPPING> TE_7022 TShmWriter: Initialized

MAPPING> TE_7004 Transformation Parse Warning [IIF(EVENT_TYPE_CODE=’RECP_REVERSE’,

IIF(UPG_BATCH_ID>0,

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID,

SOURCE_TABLE || ‘~RECEIPTREVERSE~’ || DISTRIBUTION_ID),

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID)

]; transformation continues…

MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

… IIF(EVENT_TYPE_CODE=’RECP_REVERSE’,

IIF(UPG_BATCH_ID>0,

SOURCE_TABLE || ‘~’ || >>>>DISTRIBUTION_ID<<<<,

SOURCE_TABLE || ‘~RECEIPTREVERSE~’ || DISTRIBUTION_ID),

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID)

<<PM Parse Warning>> [||]: operand converted to a string

… IIF(EVENT_TYPE_CODE=’RECP_REVERSE’,

IIF(UPG_BATCH_ID>0,

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID,

SOURCE_TABLE || ‘~RECEIPTREVERSE~’ || >>>>DISTRIBUTION_ID<<<<),

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID)

<<PM Parse Warning>> [||]: operand converted to a string

… IIF(EVENT_TYPE_CODE=’RECP_REVERSE’,

IIF(UPG_BATCH_ID>0,

SOURCE_TABLE || ‘~’ || DISTRIBUTION_ID,

SOURCE_TABLE || ‘~RECEIPTREVERSE~’ || DISTRIBUTION_ID),

SOURCE_TABLE || ‘~’ || >>>>DISTRIBUTION_ID<<<<)

]; transformation continues…

MAPPING> TE_7004 Transformation Parse Warning [JE_HEADER_ID || ‘~’ || JE_LINE_NUM]; transformation continues…

MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

… >>>>JE_HEADER_ID<<<< || ‘~’ || JE_LINE_NUM<<PM Parse Warning>> [JE_LINE_NUM]: operand converted to a string

… JE_HEADER_ID || ‘~’ || >>>>JE_LINE_NUM<<<<]; transformation continues…

MAPPING> TE_7004 Transformation Parse Warning [AE_HEADER_ID || ‘~’ || AE_LINE_NUM]; transformation continues…

MAPPING> TE_7004 Transformation Parse Warning [<<PM Parse Warning>> [||]: operand converted to a string

… >>>>AE_HEADER_ID<<<< || ‘~’ || AE_LINE_NUM<<PM Parse Warning>> [AE_LINE_NUM]: operand converted to a string

… AE_HEADER_ID || ‘~’ || >>>>AE_LINE_NUM<<<<]; transformation continues…

MAPPING> TM_6007 DTM initialized successfully for session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full]

DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].

MANAGER> PETL_24004 Starting pre-session tasks. : (Wed Sep 18 09:46:13 2013)

MANAGER> PETL_24027 Pre-session task completed successfully. : (Wed Sep 18 09:46:13 2013)

DIRECTOR> PETL_24006 Starting data movement.

MAPPING> TM_6660 Total Buffer Pool size is 12582912 bytes and Block size is 128000 bytes.

READER_1_1_1> DBG_21438 Reader: Source is [54.225.65.108:1521/VIS], user [APPS]

READER_1_1_1> BLKR_16003 Initialization completed successfully.

WRITER_1_*_1> WRT_8146 Writer: Target is database [AMAZON-9C628AAE:1521/obiaDW1], user [DWH_REP2], bulk mode [ON]

WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session — recovery is not guaranteed.

WRITER_1_*_1> WRT_8124 Target Table W_GL_LINKAGE_INFORMATION_GS :SQL INSERT statement:

INSERT INTO W_GL_LINKAGE_INFORMATION_GS(SOURCE_DISTRIBUTION_ID,JOURNAL_LINE_INTEGRATION_ID,LEDGER_ID,LEDGER_TYPE,DISTRIBUTION_SOURCE,JE_BATCH_NAME,JE_HEADER_NAME,JE_LINE_NUM,POSTED_ON_DT,GL_ACCOUNT_ID,SLA_TRX_INTEGRATION_ID,DATASOURCE_NUM_ID)  VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_GL_LINKAGE_INFORMATION_GS]

WRITER_1_*_1> WRT_8003 Writer initialization complete.

READER_1_1_1> BLKR_16007 Reader run started.

READER_1_1_1> RR_4029 SQ Instance [SQ_XLA_AE_LINES] User specified SQL Query [SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_COMBINATION_ID,

AEHEADER.EVENT_TYPE_CODE,

NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

FROM XLA_DISTRIBUTION_LINKS DLINK

   , GL_IMPORT_REFERENCES        GLIMPREF

   , XLA_AE_LINES                              AELINE

   , GL_JE_HEADERS                         JHEADER

   , GL_JE_BATCHES                         JBATCH

   , GL_LEDGERS                                 T

   , GL_PERIODS   PER

WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

         (  ‘AR_DISTRIBUTIONS_ALL’

          , ‘RA_CUST_TRX_LINE_GL_DIST_ALL’)

AND DLINK.APPLICATION_ID = 222

AND AELINE.APPLICATION_ID = 222

AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

AND JHEADER.STATUS                         = ‘P’

AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

AND JHEADER.CREATION_DATE >=

          TO_DATE(’01/01/1970 00:00:00′

                , ‘MM/DD/YYYY HH24:MI:SS’ )

AND DECODE(‘N’, ‘Y’, T.LEDGER_ID, 1) IN (1)

AND DECODE(‘N’, ‘Y’, T.LEDGER_CATEGORY_CODE, ‘NONE’) IN (‘NONE’)]

READER_1_1_1> RR_4049 SQL Query issued to database : (Wed Sep 18 09:46:13 2013)

WRITER_1_*_1> WRT_8005 Writer run started.

WRITER_1_*_1> WRT_8158

*****START LOAD SESSION*****

Load Start Time: Wed Sep 18 09:46:13 2013

Target tables:

     W_GL_LINKAGE_INFORMATION_GS

READER_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 18 09:46:13 2013]

READER_1_1_1> RR_4035 SQL Error [

ORA-00904: «XLA_EVENTS».»UPG_BATCH_ID»: invalid identifier

Database driver error…

Function Name : Execute

SQL Stmt : SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_COMBINATION_ID,

AEHEADER.EVENT_TYPE_CODE,

NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

FROM XLA_DISTRIBUTION_LINKS DLINK

   , GL_IMPORT_REFERENCES        GLIMPREF

   , XLA_AE_LINES                              AELINE

   , GL_JE_HEADERS                         JHEADER

   , GL_JE_BATCHES                         JBATCH

   , GL_LEDGERS                                 T

   , GL_PERIODS   PER

WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

         (  ‘AR_DISTRIBUTIONS_ALL’

          , ‘RA_CUST_TRX_LINE_GL_DIST_ALL’)

AND DLINK.APPLICATION_ID = 222

AND AELINE.APPLICATION_ID = 222

AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

AND JHEADER.STATUS                         = ‘P’

AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

AND JHEADER.CREATION_DATE >=

          TO_DATE(’01/01/1970 00:00:00′

                , ‘MM/DD/YYYY HH24:MI:SS’ )

AND DECODE(‘N’, ‘Y’, T.LEDGER_ID, 1) IN (1)

AND DECODE(‘N’, ‘Y’, T.LEDGER_CATEGORY_CODE, ‘NONE’) IN (‘NONE’)

Oracle Fatal Error

Database driver error…

Function Name : Execute

SQL Stmt : SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_COMBINATION_ID,

AEHEADER.EVENT_TYPE_CODE,

NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

FROM XLA_DISTRIBUTION_LINKS DLINK

   , GL_IMPORT_REFERENCES        GLIMPREF

   , XLA_AE_LINES                              AELINE

   , GL_JE_HEADERS                         JHEADER

   , GL_JE_BATCHES                         JBATCH

   , GL_LEDGERS                                 T

   , GL_PERIODS   PER

WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

         (  ‘AR_DISTRIBUTIONS_ALL’

          , ‘RA_CUST_TRX_LINE_GL_DIST_ALL’)

AND DLINK.APPLICATION_ID = 222

AND AELINE.APPLICATION_ID = 222

AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

AND JHEADER.STATUS                         = ‘P’

AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

AND JHEADER.CREATION_DATE >=

          TO_DATE(’01/01/1970 00:00:00′

                , ‘MM/DD/YYYY HH24:MI:SS’ )

AND DECODE(‘N’, ‘Y’, T.LEDGER_ID, 1) IN (1)

AND DECODE(‘N’, ‘Y’, T.LEDGER_CATEGORY_CODE, ‘NONE’) IN (‘NONE’)

Oracle Fatal Error].

READER_1_1_1> CMN_1761 Timestamp Event: [Wed Sep 18 09:46:13 2013]

READER_1_1_1> BLKR_16004 ERROR: Prepare failed.

WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.

WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_GL_LINKAGE_INFORMATION_GS] at end of load

WRITER_1_*_1> WRT_8035 Load complete time: Wed Sep 18 09:46:13 2013

LOAD SUMMARY

============

WRT_8036 Target: W_GL_LINKAGE_INFORMATION_GS (Instance Name: [W_GL_LINKAGE_INFORMATION_GS])

WRT_8044 No data loaded for this target

WRITER_1_*_1> WRT_8043 *****END LOAD SESSION*****

MANAGER> PETL_24031

***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****

Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_XLA_AE_LINES] has completed. The total run time was insufficient for any meaningful statistics.

Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_XLA_AE_LINES] has completed. The total run time was insufficient for any meaningful statistics.

Thread [WRITER_1_*_1] created for [the write stage] of partition point [W_GL_LINKAGE_INFORMATION_GS] has completed. The total run time was insufficient for any meaningful statistics.

MANAGER> PETL_24005 Starting post-session tasks. : (Wed Sep 18 09:46:14 2013)

MANAGER> PETL_24029 Post-session task completed successfully. : (Wed Sep 18 09:46:14 2013)

MAPPING> TM_6018 The session completed with [0] row transformation errors.

MANAGER> PETL_24002 Parallel Pipeline Engine finished.

DIRECTOR> PETL_24013 Session run completed with failure.

DIRECTOR> TM_6022

SESSION LOAD SUMMARY

================================================

DIRECTOR> TM_6252 Source Load Summary.

DIRECTOR> CMN_1740 Table: [SQ_XLA_AE_LINES] (Instance Name: [SQ_XLA_AE_LINES])

  Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]

DIRECTOR> TM_6253 Target Load Summary.

DIRECTOR> CMN_1740 Table: [W_GL_LINKAGE_INFORMATION_GS] (Instance Name: [W_GL_LINKAGE_INFORMATION_GS])

  Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]

DIRECTOR> TM_6023

===================================================

DIRECTOR> TM_6020 Session [SDE_ORA_GL_AR_REV_LinkageInformation_Extract_Full] completed at [Wed Sep 18 09:46:14 2013].

——————————————————————————————————————————————————————

*I did some queries in my source database (Vision) , table «XLA_EVENTS» exists , column «UPG_BATCH_ID» also exists

*I added «XLA_EVENTS» to the FROM clause and ran it in SQL Developer

*in the SELECT clause ,i see a column named «AEHEADER.EVENT_TYPE_CODE»

but there is no table named «AEHEADER» in the FROM clause

so i added it manually , it’s probably refers to «XLA_AE_HEADERS»

Final query looks like this:

SELECT DISTINCT

DLINK.SOURCE_DISTRIBUTION_ID_NUM_1 DISTRIBUTION_ID,

DLINK.SOURCE_DISTRIBUTION_TYPE SOURCE_TABLE,

AELINE.ACCOUNTING_CLASS_CODE,

GLIMPREF.JE_HEADER_ID JE_HEADER_ID,

GLIMPREF.JE_LINE_NUM JE_LINE_NUM,

AELINE.AE_HEADER_ID AE_HEADER_ID,

AELINE.AE_LINE_NUM AE_LINE_NUM,

T.LEDGER_ID LEDGER_ID,

T.LEDGER_CATEGORY_CODE LEDGER_TYPE,

    JBATCH.NAME BATCH_NAME,

   JHEADER.NAME HEADER_NAME,

      PER.END_DATE,

AELINE.CODE_COMBINATION_ID,

AEHEADER.EVENT_TYPE_CODE,

NVL(XLA_EVENTS.UPG_BATCH_ID,0) UPG_BATCH_ID

FROM XLA_DISTRIBUTION_LINKS DLINK

   , GL_IMPORT_REFERENCES        GLIMPREF

   , XLA_AE_LINES                              AELINE

   , GL_JE_HEADERS                         JHEADER

   , GL_JE_BATCHES                         JBATCH

   , GL_LEDGERS                                 T

   , GL_PERIODS   PER

   , XLA_AE_HEADERS AEHEADER

   , XLA_EVENTS

WHERE DLINK.SOURCE_DISTRIBUTION_TYPE IN

         (  ‘AR_DISTRIBUTIONS_ALL’

          , ‘RA_CUST_TRX_LINE_GL_DIST_ALL’)

AND DLINK.APPLICATION_ID = 222

AND AELINE.APPLICATION_ID = 222

AND AELINE.GL_SL_LINK_TABLE = GLIMPREF.GL_SL_LINK_TABLE

AND AELINE.GL_SL_LINK_ID         = GLIMPREF.GL_SL_LINK_ID

AND AELINE.AE_HEADER_ID         = DLINK.AE_HEADER_ID        

AND AELINE.AE_LINE_NUM           = DLINK.AE_LINE_NUM

AND GLIMPREF.JE_HEADER_ID   = JHEADER.JE_HEADER_ID

AND JHEADER.JE_BATCH_ID       = JBATCH.JE_BATCH_ID

AND JHEADER.LEDGER_ID                   = T.LEDGER_ID

AND JHEADER.STATUS                         = ‘P’

AND T.PERIOD_SET_NAME = PER.PERIOD_SET_NAME

AND JHEADER.PERIOD_NAME = PER.PERIOD_NAME

AND JHEADER.CREATION_DATE >=

          TO_DATE(’01/01/1970 00:00:00′

                , ‘MM/DD/YYYY HH24:MI:SS’ )

AND DECODE(‘N’, ‘Y’, T.LEDGER_ID, 1) IN (1)

AND DECODE(‘N’, ‘Y’, T.LEDGER_CATEGORY_CODE, ‘NONE’) IN (‘NONE’)

*when i run that query,it takes a lot of time executing without returning any results (last time it took 4 hours before i cancel it)

my questions are:
-what’s wrong with that query?

-how can i change the query in the workflow?

could anyone please help?