Greetings for the day !
I have an Oracle SQL query using 5 tables and multiple joins. When I am putting that SQL in Custom SQL and run it. It is not getting through. Everytime is display error " Operation cancelled by User " after taking few minutes. It is suppose to return only 2 year of data which will have ~1Lakh records.
Can someone help me in modelling that SQL in power BI.
Below is the snap shot of SQL (putting only FROM and Where clause).
Pls help!
FROM "SCH"."TABLE1",
"SCH"."MATERIAL_MASTER",
SCH.ECC BDG,
SCH.ECC HIS,
"SCH"."PRODUCT_HIERARCHY_DIMENSION",
SCH.CUSTOMER_MASTER ,
( Select material_id as PHC_Material_ID,
PRODUCT_HIERARCHY_LEVEL7_CODE AS PRODUCT_HIERARCHY_CODE
FROM SCH.MATERIAL_MASTER , LPDW_DM_SALES.PRODUCT_HIERARCHY_DIMENSION
WHERE
PRODUCT_HIERARCHY_IDENTIFIER = PRODUCT_HIERARCHY_ID(+)
AND PRODUCT_HIERARCHY_LEVEL2_DESCR <> 'Old Tyco'
AND PRODUCT_HIERARCHY_LEVEL2_DESCR <> 'Vincotech') PHC ,
(SELECT "CODE_TYPE_SHORT_NAME",
"GENERIC_CDE",
"GENERIC_CDE_DESC"
FROM "LPDW_PURCH_EDW"."MASTER_CODES"
WHERE CODE_TYPE_SHORT_NAME = 'SALESGROUP'
OR CODE_TYPE_SHORT_NAME = 'DOCTYPE'
OR CODE_TYPE_SHORT_NAME = 'ITEMCAT'
OR CODE_TYPE_SHORT_NAME = 'BUYERASSOC'
OR CODE_TYPE_SHORT_NAME = 'STORAGELOC'
OR CODE_TYPE_SHORT_NAME = 'MRPGRP'
OR CODE_TYPE_SHORT_NAME = 'PRFTCTR'
OR CODE_TYPE_SHORT_NAME = 'ORIGGRP'
OR CODE_TYPE_SHORT_NAME = 'MATLTYP'
OR CODE_TYPE_SHORT_NAME = 'MRPCTRL'
OR CODE_TYPE_SHORT_NAME = 'PLANT') MC2
WHERE
SALES_STAT_CATEGORY_CDE=MC2.GENERIC_CDE(+) AND
PART_NBR = phc.PHC_material_id(+) AND
TRANSACTION_TYPE_IDENTIFIER <> 1
AND "TRANSACTION_DATE" BETWEEN '01-Jan-19' AND ((add_months('01-Sep-20', -1))-1)
AND LOCAL_CURRENCY_VALUE <> 0
AND
( ( (CASE WHEN TABLE1.TRANSACTION_TYPE_IDENTIFIER IN (2,3,6) THEN
(CASE WHEN (TABLE1.DISTRIBUTION_CHANNEL_CODE ='03' OR
TABLE1.BILLING_TYPE_CDE IN ('IG','IGS','IV','IVS','ZIVR'))
THEN 'I' ELSE 'T' END )
ELSE (CASE WHEN TABLE1.DISTRIBUTION_CHANNEL_CODE = '03'
THEN 'I' ELSE 'T' END )
END ) ) = 'T' )
AND DISTRIBUTION_CHANNEL_CODE IN ('01','02')
AND "PRODUCT_HIERARCHY_LEVEL2_CODE" = '13093'
AND "PART_NBR" = MATERIAL_ID
AND "PRODUCT_HIERARCHY_IDENTIFIER" = PRODUCT_HIERARCHY_ID
AND BDG.CURR_TO = 'USD'
AND BDG.CURR_FROM = SCH.TABLE1.LOCAL_CURRENCY_CDE
AND BDG.DAY_DATE = SCH.TABLE1.TRANSACTION_DATE
AND HIS.CURR_TO = 'USD'
AND HIS.CURR_FROM = SCH.TABLE1.LOCAL_CURRENCY_CDE
AND HIS.DAY_DATE = SCH.TABLE1.TRANSACTION_DATE
AND (TABLE1.SALES_STAT_CATEGORY_CDE='ZPOR'
AND TABLE1.ORGANIZATION_ID = '1296')
AND SCH.CUSTOMER_MASTER.CUSTOMER_ID=SCH."TABLE1"."SHIP_TO_CUSTOMER_ID"
