|
Blessed1978
Yak Posting Veteran
97 Posts |
 Posted - 2014-03-03 : 19:47:54
|
| I HAVE THE FOLLOWING PLSQL(oracle) CODE I converted to TSQL(sql) however the querry is taking a very long time to pull results so I want to get another perspective. here is the PLSQL . so this code should be converted to TSQL SELECT DOC.FIN_BAL_DOC_SID, COM.COMPANY_SID, ACC.CHART_OF_ACCNT_SID, AT.FISCAL_PERIOD_SID, RESP.RESP_CENTER_SID, 0 FIN_ACTV_CLS_1_SID, 0 FIN_ACTV_CLS_2_SID, 0 FIN_ACTV_CLS_3_SID, 0 FIN_ACTV_CLS_4_SID, SOB.CURRENCY_CODE LOCAT_CURRENCY_CD, DECODE(SUBSTR(WAR.PERIOD_NAME,1,3),'DEC',1,0) YEAR_END_IND, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0)+(NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.PERIOD_NET_CR,0))) CLOSING_BAL_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,WAR.PERIOD_NET_CR) CREDIT_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,WAR.PERIOD_NET_DR) DEBIT_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.PERIOD_NET_CR,0)) NET_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0)) OPENING_BAL_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0)) YEAR_OPEN_BAL_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.BEGIN_BALANCE_CR,0)+NVL(WAR.PERIOD_NET_CR,0)) YTD_CREDIT_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.BEGIN_BALANCE_DR,0)+NVL(WAR.PERIOD_NET_DR,0)) YTD_DEBIT_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',0,NVL(WAR.BEGIN_BALANCE_DR,0)+NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.BEGIN_BALANCE_CR,0)+NVL(WAR.PERIOD_NET_CR,0)) YTD_NET_AMT, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0)+(NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.PERIOD_NET_CR,0)),0) SU_CLSNG_BAL_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',WAR.PERIOD_NET_CR,0) SU_CREDIT_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',WAR.PERIOD_NET_DR,0) SU_DEBIT_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0),0) SU_OPNG_BAL_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.PERIOD_NET_CR,0),0) SU_NET_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.PROJECT_TO_DATE_DR,0)-NVL(WAR.PROJECT_TO_DATE_CR,0),0) SU_Y_OPN_BAL_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.BEGIN_BALANCE_CR,0)+NVL(WAR.PERIOD_NET_CR,0),0) SU_YTD_CR_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.BEGIN_BALANCE_DR,0)+NVL(WAR.PERIOD_NET_DR,0),0) SU_YTD_DR_QTY, DECODE(WAR.CURRENCY_CODE,'STAT',NVL(WAR.BEGIN_BALANCE_DR,0)+NVL(WAR.PERIOD_NET_DR,0)-NVL(WAR.BEGIN_BALANCE_CR,0)+NVL(WAR.PERIOD_NET_CR,0),0) SU_YTD_NET_QTY, SYSDATE CREATED_DT, SYSDATE CHANGED_DTFROM DR.GL_BALANCES BAL, DR.GL_CODE_COMBINATIONS COMB, DR.GL_SETS_OF_BOOKS SOB, DR.GL_PERIODS GP, DR.GL_PERIOD_TYPES PT, APPS.COMPANY COM, APPS.CHART_OF_ACCOUNT ACC, APPS.RESP_CENTER RESP, APPS.ALL_TIME AT, APPS.FIN_BALANCE_DOC DOCWHERE WAR.CODE_COMBINATION_ID = COMB.CODE_COMBINATION_IDAND WAR.SET_OF_EMP_ID = SOB.SET_OF_EMP_IDAND SOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAMEAND WAR.PERIOD_NAME = GP.PERIOD_NAMEAND (WAR.CURRENCY_CODE = SOB.CURRENCY_CODE OR WAR.CURRENCY_CODE = 'USD' OR WAR.CURRENCY_CODE = 'STAT')AND WAR.ACTUAL_FLAG ='A'AND WAR.TEMPLATE_ID IS NULLAND WAR.PERIOD_YEAR >= 2014AND WAR.LAST_UPDATE_DATE> sysdate-1AND WAR.LAST_UPDATE_DATE<= sysdateAND WAR.SET_OF_EMP_ID = '1'AND substr(COMB.SEGMENT1,1,1) not in ('0','1','2','3','4','6','7','9')AND PT.PERIOD_TYPE=WAR.PERIOD_TYPEAND AT.TIME_SID=TO_NUMBER(TO_CHAR(WAR.LAST_UPDATE_DATE,'YYYYMMDD'))AND COMB.SEGMENT1=COM.COMPANY_CDAND WAR.SET_OF_EMP_ID=COM.SET_OF_EMP_IDAND COMB.SEGMENT3=ACC.ACCOUNT_NOAND WAR.SET_OF_EMP_ID=ACC.SET_OF_EMP_IDAND COMB.SEGMENT2=RESP.RESP_CENTER_CDAND WAR.SET_OF_EMP_ID=RESP.SET_OF_EMP_IDAND DOC.FIN_BAL_DOC_SID=WAR.ACTUAL_FLAG||'*'||WAR.SET_OF_EMP_ID||'*'||WAR.PERIOD_TYPE||'*'|| WAR.PERIOD_NAME||'*'||WAR.CURRENCY_CODE||'*'||WAR.CODE_COMBINATION_IDAND DOC.ACCOUNT_NO=COMB.SEGMENT5AND DOC.COMPANY_CD=COMB.SEGMENT7 |
|
convert PLSQL to TSQL