user defined query error

[raja123]

I am having a complex user defined query, which is fetching data from many tables having nested query.

My datastage job is as below:

oracle stage -> transformer -> oracle stage

in source oracle stage, I am reading the data using above user defined query. this query runs fine if I fire it in toad.

My datastage job runs fine if I remove all the joins from the query and put straight extract sql.

But if I try to use complex user defined query with above job, I am getting following error message.


Oracle_Enterprise_0: Error occurred during initializeFromArgs().

Oracle_Enterprise_0: The provided query statement did not prepare correctly;
please verify that your statement is correct;


The same job if I designed with server job, it works fine.

I made sure that all required rights are there on source/target tables.

appreciate your help

Thanks
Raja

[ray.wurlod]

Get your Oracle DBA to monitor the session and, in particular, the SQL statement that is received in that session. Compare that with the SQL statement reported in the DataStage job log. Figure out any differences.

[raja123]

Get your Oracle DBA to monitor the session and, in particular, the SQL statement that is received in that session. Compare that with the SQL statement reported in the DataStage job log. Figure out any differences.

I copied sql statement from DS logs and passed it to DBA, they are able to run the same query in database.

[ray.wurlod]

Get your Oracle DBA to monitor the session associated with the running DataStage job and, in particular, the SQL statement that is received in that session.

[raja123]

Get your Oracle DBA to monitor the session associated with the running DataStage job and, in particular, the SQL statement that is received in that session.

The job is not running more than 15 sec. It fails as soon as I start it. DBA didnt find any active session for this SQL.

[chulett]

I'm curious what you mean by "complex sql" and what this "straight extract sql" is that works in the job where the allegedly complex stuff doesn't. Any chance you can post both user-defined queries so we can see them?

[raja123]

simple query like :

SELECT
RECOG_REVENUE_USD_AMOUNT,
NON_DEFER_REVENUE_USD_AMOUNT,
SPREAD_USD_AMOUNT,
FC_SPREAD_AMOUNT,
CORP_FLAG,
EXIS_WATERFALL_SPREAD_USD_AMT,
NEW_SPREAD_USD_AMOUNT,
BACKBILL_PICKUP_SPREAD_USD_AMT,
CATCHUP_SPREAD_USD_AMOUNT,
UNRECOG_SPREAD_USD_AMOUNT,
EXT_STANDARD_USD_PRICE,
EXT_SELLING_USD_PRICE,
GROSS_EXT_SELLING_USD_PRICE,
GROSS_EXTENDED_USD_AMOUNT,
EXTENDED_USD_AMOUNT,
EXTENDED_ACCTD_USD_AMOUNT,
REVENUE_USD_AMOUNT,
INVOICED_QUANTITY,
TAXABLE_USD_AMOUNT,
INVOICED_SYSTEM_QUANTITY,
INVOICED_CONTROLLER_QUANTITY,
BOX_QUANTITY,
ME_EXT_SALE_PRICE,
ME_EXT_STANDARD_COST,
ME_EXT_BURDENED_COST,
FISCAL_MONTH_KEY,
PATHWAY_KEY,
RESERVE_FLAG,
SALES_ORDER_CATEGORY_TYPE_CODE,
SALES_REP_AT_TRX_KEY,
SALES_REP_AS_IS_KEY,
PRODUCT_AT_TRX_KEY,
PRODUCT_AS_IS_KEY,
BILLTO_CUSTOMER_AT_TRX_KEY,
BILLTO_CUSTOMER_AS_IS_KEY,
SHIPTO_CUSTOMER_AT_TRX_KEY,
SHIPTO_CUSTOMER_AS_IS_KEY,
SOLDTO_CUSTOMER_AT_TRX_KEY,
SOLDTO_CUSTOMER_AS_IS_KEY,
END_CUSTOMER_AT_TRX_KEY,
END_CUSTOMER_AS_IS_KEY,
AOO_CUSTOMER_AT_TRX_KEY,
AOO_CUSTOMER_AS_IS_KEY,
SOLDTO_PARTNER_AT_TRX_KEY,
SOLDTO_PARTNER_AS_IS_KEY,
VALUE_ADD_PARTNER_AT_TRX_KEY,
VALUE_ADD_PARTNER_AS_IS_KEY,
DISTRIBUTOR_AT_TRX_KEY,
DISTRIBUTOR_AS_IS_KEY,
INSTALL_AT_CUSTOMER_AT_TRX_KEY,
INSTALL_AT_CUSTOMER_AS_IS_KEY,
MANUAL_FLAG,
BK_SALES_REP_NUMBER,
BK_PART_NUMBER,
TOTAL_STORAGE_CAPACITY,
ME_EXT_ADJ_LIST_PRICE,
ME_EXT_LIST_PRICE,
ME_EXT_CONFIG_LIST_PRICE,
ME_EXT_DISCOUNT_AMT,
HOLD_FLAG,
CANCLLED_FLAG,
OPTY_END_CUSTOMER_ACCOUNT_NAME
FROM AGGR.INV_REV_MNTHLY_AGG



complex query like :


SELECT
SUM(IR.RECOG_REVENUE_USD_AMOUNT) AS RECOG_REVENUE_USD_AMOUNT,
SUM(IR.NON_DEFER_REVENUE_USD_AMOUNT) AS NON_DEFER_REVENUE_USD_AMOUNT,
SUM(IR.SPREAD_USD_AMOUNT) AS SPREAD_USD_AMOUNT,
SUM(IR.FC_SPREAD_AMOUNT) AS FC_SPREAD_AMOUNT,
nvl(IR.CORP_FLAG,'N') AS CORP_FLAG,
SUM(IREX.EXIS_WATERFALL_SPREAD_USD_AMT) AS EXIS_WATERFALL_SPREAD_USD_AMT,
SUM(IREX.NEW_SPREAD_USD_AMOUNT) AS NEW_SPREAD_USD_AMOUNT,
SUM(IREX.BACKBILL_PICKUP_SPREAD_USD_AMT) AS BACKBILL_PICKUP_SPREAD_USD_AMT,
SUM(IREX.CATCHUP_SPREAD_USD_AMOUNT) AS CATCHUP_SPREAD_USD_AMOUNT,
SUM(IREX.UNRECOG_SPREAD_USD_AMOUNT) AS UNRECOG_SPREAD_USD_AMOUNT,
SUM(INV_APPEND.EXT_STANDARD_USD_PRICE) AS EXT_STANDARD_USD_PRICE,
SUM(INV_APPEND.EXT_SELLING_USD_PRICE) AS EXT_SELLING_USD_PRICE,
SUM(INV_APPEND.GROSS_EXT_SELLING_USD_PRICE) AS GROSS_EXT_SELLING_USD_PRICE,
SUM(INV_APPEND.GROSS_EXTENDED_USD_AMOUNT) AS GROSS_EXTENDED_USD_AMOUNT,
SUM(INV_APPEND.EXTENDED_USD_AMOUNT) AS EXTENDED_USD_AMOUNT,
SUM(INV_APPEND.EXTENDED_ACCTD_USD_AMOUNT) AS EXTENDED_ACCTD_USD_AMOUNT,
SUM(INV_APPEND.REVENUE_USD_AMOUNT) AS REVENUE_USD_AMOUNT,
SUM(INV_APPEND.INVOICED_QUANTITY) AS INVOICED_QUANTITY,
SUM(INV_APPEND.TAXABLE_USD_AMOUNT) AS TAXABLE_USD_AMOUNT,
SUM(INV_APPEND.INVOICED_SYSTEM_QUANTITY) AS INVOICED_SYSTEM_QUANTITY,
SUM(INV_APPEND.INVOICED_CONTROLLER_QUANTITY) AS INVOICED_CONTROLLER_QUANTITY,
SUM(INV_APPEND.BOX_QUANTITY) AS BOX_QUANTITY,
SUM(INV_APPEND.ME_EXT_SALE_PRICE) AS ME_EXT_SALE_PRICE,
SUM(INV_APPEND.ME_EXT_STANDARD_COST) AS ME_EXT_STANDARD_COST,
SUM(INV_APPEND.ME_EXT_BURDENED_COST) AS ME_EXT_BURDENED_COST,
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY,
nvl(IR.PATHWAY_KEY,-999999) AS PATHWAY_KEY,
DECODE(SOLR.SO_KEY,NULL,'N','Y') AS RESERVE_FLAG,
DECODE(SOL.PRE_BUILD_FLAG,'Y', 'PREBUILD', DECODE(SOL.INTERNAL_FLAG,'Y','INTERNAL','EXTERNAL')) AS SALES_ORDER_CATEGORY_TYPE_CODE,
nvl(IR.SALES_REP_AT_TRX_KEY,-999999) AS SALES_REP_AT_TRX_KEY,
nvl(IR.SALES_REP_AS_IS_KEY,-999999) AS SALES_REP_AS_IS_KEY,
nvl(IR.PRODUCT_AT_TRX_KEY,-999999) AS PRODUCT_AT_TRX_KEY,
nvl(IR.PRODUCT_AS_IS_KEY,-999999) AS PRODUCT_AS_IS_KEY,
nvl(IR.BILLTO_CUSTOMER_AT_TRX_KEY,-999999) AS BILLTO_CUSTOMER_AT_TRX_KEY,
nvl(IR.BILLTO_CUSTOMER_AS_IS_KEY,-999999) AS BILLTO_CUSTOMER_AS_IS_KEY,
nvl(IR.SHIPTO_CUSTOMER_AT_TRX_KEY,-999999) AS SHIPTO_CUSTOMER_AT_TRX_KEY,
nvl(IR.SHIPTO_CUSTOMER_AS_IS_KEY,-999999) AS SHIPTO_CUSTOMER_AS_IS_KEY,
nvl(IR.SOLDTO_CUSTOMER_AT_TRX_KEY,-999999) AS SOLDTO_CUSTOMER_AT_TRX_KEY,
nvl(IR.SOLDTO_CUSTOMER_AS_IS_KEY,-999999) AS SOLDTO_CUSTOMER_AS_IS_KEY,
nvl(IR.END_CUSTOMER_AT_TRX_KEY,-999999) AS END_CUSTOMER_AT_TRX_KEY,
nvl(IR.END_CUSTOMER_AS_IS_KEY,-999999) AS END_CUSTOMER_AS_IS_KEY,
nvl(IR.AOO_CUSTOMER_AT_TRX_KEY,-999999) AS AOO_CUSTOMER_AT_TRX_KEY,
nvl(IR.AOO_CUSTOMER_AS_IS_KEY,-999999) AS AOO_CUSTOMER_AS_IS_KEY,
nvl(IR.SOLDTO_PARTNER_AT_TRX_KEY,-999999) AS SOLDTO_PARTNER_AT_TRX_KEY,
nvl(IR.SOLDTO_PARTNER_AS_IS_KEY,-999999) AS SOLDTO_PARTNER_AS_IS_KEY,
nvl(IR.VALUE_ADD_PARTNER_AT_TRX_KEY,-999999) AS VALUE_ADD_PARTNER_AT_TRX_KEY,
nvl(IR.VALUE_ADD_PARTNER_AS_IS_KEY,-999999) AS VALUE_ADD_PARTNER_AS_IS_KEY,
nvl(IR.DISTRIBUTOR_AT_TRX_KEY,-999999) AS DISTRIBUTOR_AT_TRX_KEY,
nvl(IR.DISTRIBUTOR_AS_IS_KEY,-999999) AS DISTRIBUTOR_AS_IS_KEY,
nvl(IR.INSTALL_AT_CUSTOMER_AT_TRX_KEY,-999999) AS INSTALL_AT_CUSTOMER_AT_TRX_KEY,
nvl(IR.INSTALL_AT_CUSTOMER_AS_IS_KEY,-999999) AS INSTALL_AT_CUSTOMER_AS_IS_KEY,
DECODE(ARI.AR_INVOICE_CREATE_SOURCE_NAME, 'RAXTRX', 'N', 'Y') AS MANUAL_FLAG,
INV_APPEND.BK_SALES_REP_NUMBER AS BK_SALES_REP_NUMBER,
INV_APPEND.BK_PART_NUMBER AS BK_PART_NUMBER,
SUM(INV_APPEND.TOTAL_STORAGE_CAPACITY) AS TOTAL_STORAGE_CAPACITY,
SUM(INV_APPEND.ME_EXT_ADJ_LIST_PRICE) AS ME_EXT_ADJ_LIST_PRICE,
SUM(INV_APPEND.ME_EXT_LIST_PRICE) AS ME_EXT_LIST_PRICE,
SUM(INV_APPEND.ME_EXT_CONFIG_LIST_PRICE) AS ME_EXT_CONFIG_LIST_PRICE,
SUM(INV_APPEND.ME_EXT_DISCOUNT_AMT) AS ME_EXT_DISCOUNT_AMT,
DECODE(SOLH.SO_KEY, NULL,'N','Y') AS HOLD_FLAG,
nvl(SOL.CANCELLED_FLAG,'N') AS CANCLLED_FLAG,
nvl(EC.OPTY_END_CUSTOMER_ACCOUNT_NAME,'UNKNOWN') AS OPTY_END_CUSTOMER_ACCOUNT_NAME
FROM
FACTS.INVOICE_REVENUE IR,
DIMS.AR_INVOICE ARI,
FACTS.INVOICE_REVENUE_EXT IREX,
DIMS.END_CUSTOMER EC,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM,
DIMS.SALES_ORDER_LINE SOL, (SELECT FISCAL_MONTH.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY, SALES_ORDER_KEY AS SALES_ORDER_KEY, SALES_ORDER_LINE_KEY AS SALES_ORDER_LINE_KEY,
SALES_REP_AS_IS_KEY AS SALES_REP_AS_IS_KEY, SALES_REP_AT_TRX_KEY AS SALES_REP_AT_TRX_KEY,AR_INVOICE_KEY AS AR_INVOICE_KEY, AR_INVOICE_LINE_KEY AS AR_INVOICE_LINE_KEY,
SOURCE_TRANSACTION_DATE_KEY AS SOURCE_TRANSACTION_DATE_KEY,
SUM(PRODUCT_TV.STORAGE_CAPACITY * INVOICE.INVOICED_QUANTITY) AS TOTAL_STORAGE_CAPACITY,
SUM(UNIT_STANDARD_USD_PRICE*INVOICED_QUANTITY) AS EXT_STANDARD_USD_PRICE,
SUM(UNIT_SELLING_USD_PRICE*INVOICED_QUANTITY) AS EXT_SELLING_USD_PRICE,
SUM(GROSS_UNIT_SELLING_USD_PRICE*INVOICED_QUANTITY) AS GROSS_EXT_SELLING_USD_PRICE,
SUM(GROSS_EXTENDED_USD_AMOUNT) AS GROSS_EXTENDED_USD_AMOUNT,
SUM(EXTENDED_USD_AMOUNT) AS EXTENDED_USD_AMOUNT,
SUM(EXTENDED_ACCTD_USD_AMOUNT) AS EXTENDED_ACCTD_USD_AMOUNT,
SUM(REVENUE_USD_AMOUNT) AS REVENUE_USD_AMOUNT,
SUM(TAXABLE_USD_AMOUNT) AS TAXABLE_USD_AMOUNT,
SUM(INVOICED_QUANTITY) AS INVOICED_QUANTITY,
SUM(INVOICED_SYSTEM_QUANTITY) AS INVOICED_SYSTEM_QUANTITY,
SUM(INVOICED_CONTROLLER_QUANTITY) AS INVOICED_CONTROLLER_QUANTITY,
SUM(BOX_QUANTITY) AS BOX_QUANTITY,
SUM(ME_UNIT_LIST_PRICE*INVOICED_QUANTITY) AS ME_EXT_LIST_PRICE,
SUM(ME_ADJ_LIST_PRICE*INVOICED_QUANTITY) AS ME_EXT_ADJ_LIST_PRICE,
SUM(ME_CONFIG_LIST_PRICE*INVOICED_QUANTITY) AS ME_EXT_CONFIG_LIST_PRICE,
SUM(ME_UNIT_DISCOUNT_AMOUNT*INVOICED_QUANTITY) AS ME_EXT_DISCOUNT_AMT,
SUM(ME_EXT_SALE_PRICE*INVOICED_QUANTITY) AS ME_EXT_SALE_PRICE,
SUM(ME_EXT_STANDARD_COST*INVOICED_QUANTITY) AS ME_EXT_STANDARD_COST,
SUM(ME_EXT_BURDENED_COST*INVOICED_QUANTITY) AS ME_EXT_BURDENED_COST,
INVOICE.BK_SALES_REP_NUMBER AS BK_SALES_REP_NUMBER,
INVOICE.BK_PART_NUMBER AS BK_PART_NUMBER
FROM FACTS.INVOICE, DIMS.PRODUCT_TV, DIMS.CALENDAR, DIMS.FISCAL_MONTH
WHERE INVOICE.PRODUCT_AT_TRX_KEY = PRODUCT_TV.PRODUCT_KEY
AND SOURCE_TRANSACTION_DATE_KEY= CALENDAR.DATE_KEY
AND CALENDAR.FISCAL_MONTH_START_DATE = FISCAL_MONTH.BK_FISCAL_MONTH_START_DATE
AND (FISCAL_MONTH.FISCAL_MONTH_KEY IN (SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE_REVENUE IR,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(IR.EDW_CREATED_DATE >= (SYSDATE - 100) OR IR.EDW_UPDATED_DATE >= (SYSDATE - 100))
AND IR.REVENUE_RECOG_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE )
OR FISCAL_MONTH.FISCAL_MONTH_KEY IN (SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE INV,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(INV.EDW_CREATE_DATE >= (SYSDATE - 100) OR INV.EDW_UPDATE_DATE >= (SYSDATE - 100))
AND INV.SOURCE_TRANSACTION_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE)
OR FISCAL_MONTH.FISCAL_MONTH_KEY IN (SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE_REVENUE_EXT INEX,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(INEX.EDW_CREATED_DATE >= (SYSDATE - 100) OR INEX.EDW_UPDATED_DATE >= (SYSDATE - 100))
AND INEX.REVENUE_RECOG_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE))
GROUP BY
FISCAL_MONTH.FISCAL_MONTH_KEY ,
INVOICE.BK_SALES_REP_NUMBER,
INVOICE.BK_PART_NUMBER,
SALES_ORDER_KEY,
SALES_ORDER_LINE_KEY,
SALES_REP_AS_IS_KEY,
SALES_REP_AT_TRX_KEY,
AR_INVOICE_KEY,
AR_INVOICE_LINE_KEY,
SOURCE_TRANSACTION_DATE_KEY) INV_APPEND,
(SELECT SALES_ORDER_KEY AS SO_KEY, SALES_ORDER_LINE_KEY
AS SOL_KEY, COUNT(SALES_ORDER_LINE_KEY) FROM FACTS.SALES_ORDER_LINE_HOLD WHERE HOLD_END_DATE IS NULL GROUP BY
SALES_ORDER_KEY , SALES_ORDER_LINE_KEY)SOLH,
(SELECT SALES_ORDER_KEY AS SO_KEY, SALES_ORDER_LINE_KEY AS SOL_KEY, COUNT(SALES_ORDER_LINE_KEY) FROM FACTS.SALES_ORDER_LINE_RESERVE
WHERE RESERVE_OFF_DATE IS NULL GROUP BY SALES_ORDER_KEY , SALES_ORDER_LINE_KEY) SOLR
WHERE
(FM.FISCAL_MONTH_KEY IN (
SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE_REVENUE IR,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(IR.EDW_CREATED_DATE >= (SYSDATE - 100) OR IR.EDW_UPDATED_DATE >= (SYSDATE - 100))
AND IR.REVENUE_RECOG_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE)
OR FM.FISCAL_MONTH_KEY IN (SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE INV,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(INV.EDW_CREATE_DATE >= (SYSDATE - 100) OR INV.EDW_UPDATE_DATE >= (SYSDATE - 100))
AND INV.SOURCE_TRANSACTION_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE)
OR FM.FISCAL_MONTH_KEY IN (SELECT
FM.FISCAL_MONTH_KEY AS FISCAL_MONTH_KEY
FROM FACTS.INVOICE_REVENUE_EXT INEX,
DIMS.CALENDAR CAL,
DIMS.FISCAL_MONTH FM
WHERE
(INEX.EDW_CREATED_DATE >= (SYSDATE - 100) OR INEX.EDW_UPDATED_DATE >= (SYSDATE - 100))
AND INEX.REVENUE_RECOG_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE))
AND IR.REVENUE_RECOG_DATE_KEY = CAL.DATE_KEY
AND CAL.FISCAL_MONTH_START_DATE = FM.BK_FISCAL_MONTH_START_DATE
AND IR.SALES_ORDER_KEY = SOLR.SO_KEY (+)
AND IR.SALES_ORDER_LINE_KEY = SOLR.SOL_KEY (+)
AND IR.SALES_ORDER_KEY = SOLH.SO_KEY (+)
AND IR.SALES_ORDER_LINE_KEY = SOLH.SOL_KEY(+)
AND IR.SALES_ORDER_KEY = EC.SALES_ORDER_KEY(+)
AND IR.AR_INVOICE_KEY = ARI.AR_INVOICE_KEY
AND IR.SO_LINE_REVISION_NUMBER = IREX.SO_LINE_REVISION_NUMBER(+)
AND IR.SALES_REP_AT_TRX_KEY = IREX.SALES_REP_AT_TRX_KEY(+)
AND IR.SALES_REP_AS_IS_KEY = IREX.SALES_REP_AS_IS_KEY(+)
AND IR.REVENUE_RECOG_DATE_KEY = IREX.REVENUE_RECOG_DATE_KEY(+)
AND IR.AR_INVOICE_LINE_KEY = IREX.AR_INVOICE_LINE_KEY(+)
AND IR.AR_INVOICE_KEY = IREX.AR_INVOICE_KEY(+)
AND IR.SALES_ORDER_LINE_KEY = IREX.SALES_ORDER_LINE_KEY(+)
AND IR.SALES_ORDER_KEY = IREX.SALES_ORDER_KEY(+)
AND IR.SALES_ORDER_LINE_KEY = SOL.SALES_ORDER_LINE_KEY
AND IR.SALES_ORDER_KEY = INV_APPEND.SALES_ORDER_KEY(+)
AND IR.SALES_ORDER_LINE_KEY = INV_APPEND.SALES_ORDER_LINE_KEY(+)
AND IR.SALES_REP_AS_IS_KEY = INV_APPEND.SALES_REP_AS_IS_KEY(+)
AND IR.SALES_REP_AT_TRX_KEY = INV_APPEND.SALES_REP_AT_TRX_KEY(+)
AND IR.AR_INVOICE_KEY = INV_APPEND.AR_INVOICE_KEY(+)
AND IR.AR_INVOICE_LINE_KEY = INV_APPEND.AR_INVOICE_LINE_KEY(+)
AND IR.REVENUE_RECOG_DATE_KEY = INV_APPEND.SOURCE_TRANSACTION_DATE_KEY(+)
GROUP BY
FM.FISCAL_MONTH_KEY,
IR.SALES_REP_AT_TRX_KEY,
IR.SALES_REP_AS_IS_KEY,
IR.PRODUCT_AT_TRX_KEY,
IR.PRODUCT_AS_IS_KEY,
IR.BILLTO_CUSTOMER_AS_IS_KEY,
IR.BILLTO_CUSTOMER_AT_TRX_KEY,
IR.SHIPTO_CUSTOMER_AT_TRX_KEY,
IR.SHIPTO_CUSTOMER_AS_IS_KEY,
IR.SOLDTO_CUSTOMER_AT_TRX_KEY,
IR.SOLDTO_CUSTOMER_AS_IS_KEY,
IR.END_CUSTOMER_AT_TRX_KEY,
IR.END_CUSTOMER_AS_IS_KEY,
IR.AOO_CUSTOMER_AT_TRX_KEY,
IR.AOO_CUSTOMER_AS_IS_KEY,
IR.SOLDTO_PARTNER_AT_TRX_KEY,
IR.SOLDTO_PARTNER_AS_IS_KEY,
IR.VALUE_ADD_PARTNER_AT_TRX_KEY,
IR.VALUE_ADD_PARTNER_AS_IS_KEY,
IR.DISTRIBUTOR_AT_TRX_KEY,
IR.DISTRIBUTOR_AS_IS_KEY,
IR.INSTALL_AT_CUSTOMER_AT_TRX_KEY,
IR.INSTALL_AT_CUSTOMER_AS_IS_KEY,
IR.CORP_FLAG,
DECODE(SOLR.SO_KEY,NULL,'N','Y'),
DECODE(SOL.PRE_BUILD_FLAG,'Y', 'PREBUILD', DECODE(SOL.INTERNAL_FLAG,'Y','INTERNAL','EXTERNAL')),
IR.PATHWAY_KEY,
DECODE(ARI.AR_INVOICE_CREATE_SOURCE_NAME, 'RAXTRX', 'N', 'Y'),
DECODE(SOLH.SO_KEY, NULL,'N','Y'),
SOL.CANCELLED_FLAG,
EC.OPTY_END_CUSTOMER_ACCOUNT_NAME,
INV_APPEND.BK_SALES_REP_NUMBER,
INV_APPEND.BK_PART_NUMBER

[chulett]

Assuming the same number (and datatypes) of columns are being selected, you may simply be running out of room for the query, i.e. there is a maximum length to the query string that can be passed and it may be getting truncated.

First suggestion would be to try removing all of the CR formatting, essentially making the query "one long string" and see if then it works. Minimize the white space in the query as well.

[kandyshandy]

Raja, i did not look at your posting earlier. If i had, then we would have avoided this discussion at the first place.

Version 8x has this problem.. complex user defined SQL runs fine in TOAD but not in Oracle EE stage.

We have opened a case with IBM on this and they updated us that the patch is on the way.. mostly next week.

As you said, it will work fine in Server job and in PX job using DRS stage instead of Oracle EE stage.

So you may want to open a PMR with IBM.

[chulett]

Any idea why there's an issue with "complex" sql? What the issue actually is? Does "complex" equal "really really long" here?

[kandyshandy]

Yes Craig. Really really long and mutiple joins, sub queries etc. I started migrating existing jobs from 7.5.2 to 8.1 recently and i faced the same issue. I don't know who wrote the SQL (it was written by some X). So i ended up in doing R&D to find out where the issue is... After minutes of struggle, i found that the error was happening when i add any column after 24 columns in select. We opened a case with IBM and sent dsx from 7.5.2 and 8.1. IBM was able to simulate the issue in house and they are getting ready with the patch right now.

Meanwhile, I redesigned the job and made it run in 8.1 (with normal SELECT SQL)

[raja123]

Yes Craig. Really really long and mutiple joins, sub queries etc. I started migrating existing jobs from 7.5.2 to 8.1 recently and i faced the same issue. I don't know who wrote the SQL (it was written by some X). So i ended up in doing R&D to find out where the issue is... After minutes of struggle, i found that the error was happening when i add any column after 24 columns in select. We opened a case with IBM and sent dsx from 7.5.2 and 8.1. IBM was able to simulate the issue in house and they are getting ready with the patch right now.

Meanwhile, I redesigned the job and made it run in 8.1 (with normal SELECT SQL)

could you please explain me what do you mean by redesigning the job. did you reduce the number of columns in your select query. I have user defined queries which has more than 24 columns and running.

[kandyshandy]

Raja, issue at 25th column is specific to my environment. Pls don't take it for you. But Oracle EE stage in 8.1 has some issue for sure and IBM is working on it.

Redesign - Yes. I developed a new job with the same functionality. In my new job, we just had a small direct SELECT statement without any joins. I implemented the functionality using DS stages. Again, this is specific to my environment.

What i would prefer you to do is... study the job and see what it does.

Then you can think the best way to implement the same using DS stages rather than in one SQL. Also, you can post your requirements here and people can help you out.

[DSguru2B]

How much data are we talking here??? If its small enough, you can write a server job to process and stage the data into a flat file. Then let your parallel job pick up the flat file.

[vasa_dxx]

Another way to handle complex queries would be to create views in source DB & fire SELECT * FROM VIEW, so query would fetch the required fields.