user defined query error
Moderators: chulett, rschirm, roy
user defined query error
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I copied sql statement from DS logs and passed it to DBA, they are able to run the same query in database.ray.wurlod wrote: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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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.
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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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)
Meanwhile, I redesigned the job and made it run in 8.1 (with normal SELECT SQL)
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
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 wrote: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)
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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.
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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!