Oracle query - failing Guess Schema

[CWest99]

Hi,

New to community. First post here. Noob designer.

I have an Oracle query with a number of conditions in a tOracleInput component. Trying to get it to  guess the schema. Getting ORA-00933 error. Can't seem to find any issues with query.  Query runs fine in Oracle SQL Developer.  Suggestions?

Thanks in advance,

Chris

----------------------------------------------

SELECT CAST(WIP_DOCUMENT_ACTION.DOCUMENT_ID AS NUMERIC(18,1)) AS \"DocumentID\",
  WIP_DOCUMENT.LNI,
  WIP_DOCUMENT.BASE_FILENAME AS \"WIPFile\",
  WIP_DOCUMENT.WIP_ID,
  WIP_DOCUMENT.SOURCE_ID,
  WIP_DOCUMENT.JURISDICTION,
  WIP_DOCUMENT.LEXIS_CITE,
  WIP_DOCUMENT.PROCESS_NO,
  WIP_DOCUMENT.START_TIME  AS \"StartTime\",
  WIP_DOCUMENT.END_TIME AS \"EndTime\",
  WIP_DOCUMENT_STATUS.NAME AS \"StatusName\",
  WIP_DOCUMENT.SPEC_HOURS,
  SUBSTR(NVL(WIP_AGGREGATE.NAME , 'null'), 1, 64) AS \"JobAggregate\",
  WIP_ACTION.NAME AS \"ActionName\",
  WIP_ACTION_STATUS.NAME AS \"ActionStatus\",
  WIP_DOCUMENT_ACTION.USER_ID,
  WIP_DOCUMENT_ACTION.SPEC_HOURS AS \"Action_Spec_Hours\",
  WIP_DOCUMENT_ACTION.GROUP_FACTOR AS \"Group_Factor\",
  WIP_DOCUMENT_ACTION.READY_TIME AS \"Action_Ready_Time\",
  WIP_DOCUMENT_ACTION.START_TIME AS \"Action_Start_Time\",
  WIP_DOCUMENT_ACTION.END_TIME AS \"Action_End_Time\",
  WIP_DOCUMENT.INPUT_VERSION,
  WIP_DOCUMENT.UPDATE_VERSION
FROM WIP_DOCUMENT_ACTION,
  WIP_AGGREGATE_DOCUMENT,
  WIP_RPT_HIERARCHY,
  WIP_ACTION,
  WIP_AGGREGATE,
  WIP_DOCUMENT_STATUS,
  WIP_ACTION_STATUS,
  WIP_DOCUMENT
WHERE WIP_RPT_HIERARCHY.ID        = WIP_DOCUMENT.RPT_HIER_ID
AND WIP_RPT_HIERARCHY.ID          = WIP_DOCUMENT_ACTION.RPT_HIER_ID
AND WIP_ACTION.ACTION_NO          = WIP_DOCUMENT_ACTION.ACTION_NO
AND WIP_AGGREGATE.AGGREGATE_ID    = WIP_AGGREGATE_DOCUMENT.AGGREGATE_ID
AND WIP_DOCUMENT_STATUS.STATUS_NO = WIP_DOCUMENT.STATUS_NO
AND WIP_ACTION_STATUS.STATUS_NO   = WIP_DOCUMENT_ACTION.ACTION_STATUS_NO
AND WIP_DOCUMENT.DOCUMENT_ID      = WIP_AGGREGATE_DOCUMENT.DOCUMENT_ID
AND WIP_DOCUMENT.DOCUMENT_ID      = WIP_DOCUMENT_ACTION.DOCUMENT_ID
AND WIP_DOCUMENT.STATUS_NO        = WIP_DOCUMENT_STATUS.STATUS_NO
AND ((WIP_DOCUMENT.PROCESS_NO IN ('1001', '1004', '1006', '1094', '1129', '1121', '1122', '1105')
OR WIP_DOCUMENT.PROCESS_NO BETWEEN 1500 AND 1599
OR WIP_DOCUMENT.PROCESS_NO BETWEEN 1700 AND 1799)
OR (WIP_DOCUMENT_ACTION.USER_ID NOT IN ('nfd', 'u1elsa', 'u1hccsad')
OR WIP_DOCUMENT_ACTION.USER_ID IS NULL))
AND WIP_DOCUMENT.END_TIME >= (SELECT (next_day(TRUNC(SysDate), 'sunday') - 14) FROM DUAL)
AND WIP_DOCUMENT.END_TIME < (SELECT (next_day(TRUNC(SysDate), 'sunday') - 😎 + 24 / 24 FROM DUAL)
AND NVL(WIP_AGGREGATE_DOCUMENT.ACTIVE_FLAG, 'Y') = 'Y'
AND NVL(WIP_AGGREGATE.TYPE_NO, 1) = 1
AND WIP_RPT_HIERARCHY.DIVISION_ID IN ('EDOP-1', 'TAX_DIV', 'FedAgencyDiv');

[TRF]

I suggest you to create a view on oracle side and use it in the tOracleInpout. It should help you to solve the problem with "guess schema". Also, try to remove the final semi column (maybe the cause of ORA-00933).

[CWest99]

Thanks TRF.  Removing the last semi-colon didn't fix the issue.

I could create a View on the Oracle DB, but in most cases, I'm using read-only creds and need to pull by query in the ETL tool.

I've used Informatica and MS SSIS in the past for ETL. Both have not had any issue handling similar queries.

Would like to see Talend handle them better.  Should be as easy as pasting the query in once the DB connection and tables are set up.

Regards,

Chris

[TRF]

The query seems to be malformed.
Did you try to remove the \" arround the column names? Maybe an issue in the "guess schema" fonction because of that.

[CWest99]

Hi TRF,
Talend's editor actually added those when I used it. I assumed it was necessary for the Talend parser.
Regards,
Chris