Skip navigation
Contact Us
1951 Views 3 Replies Latest reply: Nov 18, 2012 8:23 PM by Ken Pong RSS
Ken Pong Newbie 6 posts since
May 30, 2012
Currently Being Moderated

Nov 16, 2012 2:06 AM

SQL Override for Uncached Lookup and return error ORA-00933: SQL command not properly ended

Hi,

 

In Informatica PowerCenter (Version 9.0.1) Transformation Guide, it mentioned that

"Each column in the SELECT query uses an alias to define the output column. Do not change this syntax in the

SQL statement, or the query fails. To reference input ports in the WHERE clause, configure parameter binding.

The following example includes a WHERE statement that references the Name port:

SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?

The SQL Editor for uncached lookup displays the input ports and the lookup ports on the Ports tab."

 

I disable lookup cache and follow the syntax to add condition as below in blue color:

 

"SELECT $$Hint2

TARGET_TABLE.CHANGED_ON_DT AS CHANGED_ON_DT,

TARGET_TABLE.AUX1_CHANGED_ON_DT AS AUX1_CHANGED_ON_DT,

TARGET_TABLE.AUX2_CHANGED_ON_DT AS AUX2_CHANGED_ON_DT,

TARGET_TABLE.AUX3_CHANGED_ON_DT AS AUX3_CHANGED_ON_DT,

TARGET_TABLE.AUX4_CHANGED_ON_DT AS AUX4_CHANGED_ON_DT,

TARGET_TABLE.DELETE_FLG AS DELETE_FLG,

TARGET_TABLE.W_INSERT_DT AS W_INSERT_DT,

TARGET_TABLE.ETL_PROC_WID AS ETL_PROC_WID,

TARGET_TABLE.DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID,

TARGET_TABLE.INTEGRATION_ID AS INTEGRATION_ID,

TARGET_TABLE.GL_RECONCILED_ON_DT AS GL_RECONCILED_ON_DT,

TARGET_TABLE.GL_RECONCILED_ON_PROC_WID AS GL_RECONCILED_ON_PROC_WID,

TARGET_TABLE.DOC_STATUS_WID AS DOC_STATUS_WID,

TARGET_TABLE.POSTED_ON_DT_WID AS POSTED_ON_DT_WID,

TARGET_TABLE.POSTED_ON_TM_WID AS POSTED_ON_TM_WID,

TARGET_TABLE.ACCT_DOC_NUM AS ACCT_DOC_NUM,

TARGET_TABLE.ACCT_DOC_ITEM AS ACCT_DOC_ITEM,

TARGET_TABLE.ACCT_DOC_SUB_ITEM AS ACCT_DOC_SUB_ITEM,

TARGET_TABLE.DOC_HEADER_TEXT AS DOC_HEADER_TEXT,

TARGET_TABLE.LINE_ITEM_TEXT AS LINE_ITEM_TEXT

FROM

W_AP_XACT_F TARGET_TABLE,W_AP_XACT_FS STAGE_TABLE

WHERE

STAGE_TABLE.DATASOURCE_NUM_ID = TARGET_TABLE.DATASOURCE_NUM_ID AND

STAGE_TABLE.INTEGRATION_ID = TARGET_TABLE.INTEGRATION_ID

--Temp Start added by Ken Pong (20121116)

AND

STAGE_TABLE.DATASOURCE_NUM_ID = ?IN_DATASOURCE_NUM_ID? AND

STAGE_TABLE.INTEGRATION_ID = ?IN_INTEGRATION_ID?

--Temp End added by Ken Pong (20121116)

ORDER BY

TARGET_TABLE.DATASOURCE_NUM_ID,

TARGET_TABLE.INTEGRATION_ID --"

 

 

 

When I run ETL, then got below error

 

"TRANSF_1_1_1> CMN_1021 Database driver event...

CMN_1021 [

ORA-00933: SQL command not properly ended

 

Database driver error...

Function Name : Execute

SQL Stmt : SELECT  --

 

TARGET_TABLE.CHANGED_ON_DT AS CHANGED_ON_DT,  TARGET_TABLE.AUX1_CHANGED_ON_DT AS AUX1_CHANGED_ON_DT,  TARGET_TABLE.AUX2_CHANGED_ON_DT AS AUX2_CHANGED_ON_DT,  TARGET_TABLE.AUX3_CHANGED_ON_DT AS AUX3_CHANGED_ON_DT,  TARGET_TABLE.AUX4_CHANGED_ON_DT AS AUX4_CHANGED_ON_DT,  TARGET_TABLE.DELETE_FLG AS DELETE_FLG,  TARGET_TABLE.W_INSERT_DT AS W_INSERT_DT,  TARGET_TABLE.ETL_PROC_WID AS ETL_PROC_WID,  TARGET_TABLE.GL_RECONCILED_ON_DT AS GL_RECONCILED_ON_DT,  TARGET_TABLE.GL_RECONCILED_ON_PROC_WID AS GL_RECONCILED_ON_PROC_WID,  TARGET_TABLE.DOC_STATUS_WID AS DOC_STATUS_WID,  TARGET_TABLE.POSTED_ON_DT_WID AS POSTED_ON_DT_WID,  TARGET_TABLE.POSTED_ON_TM_WID AS POSTED_ON_TM_WID,  TARGET_TABLE.ACCT_DOC_NUM AS ACCT_DOC_NUM,  TARGET_TABLE.ACCT_DOC_ITEM AS ACCT_DOC_ITEM,  TARGET_TABLE.ACCT_DOC_SUB_ITEM AS ACCT_DOC_SUB_ITEM,  TARGET_TABLE.DOC_HEADER_TEXT AS DOC_HEADER_TEXT,  TARGET_TABLE.LINE_ITEM_TEXT AS LINE_ITEM_TEXT FROM W_AP_XACT_FS STAGE_TABLE, W_AP_XACT_F TARGET_TABLE

 

WHERE

 

STAGE_TABLE.DATASOURCE_NUM_ID = TARGET_TABLE.DATASOURCE_NUM_ID AND

 

STAGE_TABLE.INTEGRATION_ID = TARGET_TABLE.INTEGRATION_ID

 

--Temp Start added by Ken Pong (20121116)

 

AND

 

STAGE_TABLE.DATASOURCE_NUM_ID = ?IN_DATASOURCE_NUM_ID? AND

 

STAGE_TABLE.INTEGRATION_ID = ?IN_INTEGRATION_ID?

 

--Temp End added by Ken Pong (20121116)

 

ORDER BY

 

TARGET_TABLE.DATASOURCE_NUM_ID,

 

TARGET_TABLE.INTEGRATION_ID -- ORDER BY CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,DELETE_FLG,W_INSERT_DT,ETL_PROC_WID,GL_RECONCILED_ON_DT,GL_RECONCILED_ON_PROC_WID,DOC_STATUS_WID,POSTED_ON_DT_WID,POSTED_ON_TM_WID,ACCT_DOC_NUM,ACCT_DOC_ITEM,ACCT_DOC_SUB_ITEM,DOC_HEADER_TEXT,LINE_ITEM_TEXT

Oracle Fatal Error"

 

Thanks,

Ken

Attachments:
  • Felix Ho Expert 342 posts since
    Aug 23, 2011

    Let's try run your SQL in database.

More Like This

  • Retrieving data ...

Bookmarked By (0)