SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 convert PLSQL to TSQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Starting Member

48 Posts

Posted - 03/03/2014 :  19:47:54  Show Profile  Reply with Quote
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_DT
FROM 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 DOC
WHERE WAR.CODE_COMBINATION_ID = COMB.CODE_COMBINATION_ID
AND WAR.SET_OF_EMP_ID = SOB.SET_OF_EMP_ID
AND SOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND WAR.PERIOD_NAME = GP.PERIOD_NAME
AND (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 NULL
AND WAR.PERIOD_YEAR >= 2014
AND WAR.LAST_UPDATE_DATE> sysdate-1
AND WAR.LAST_UPDATE_DATE<= sysdate
AND 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_TYPE
AND AT.TIME_SID=TO_NUMBER(TO_CHAR(WAR.LAST_UPDATE_DATE,'YYYYMMDD'))
AND COMB.SEGMENT1=COM.COMPANY_CD
AND WAR.SET_OF_EMP_ID=COM.SET_OF_EMP_ID
AND COMB.SEGMENT3=ACC.ACCOUNT_NO
AND WAR.SET_OF_EMP_ID=ACC.SET_OF_EMP_ID
AND COMB.SEGMENT2=RESP.RESP_CENTER_CD
AND WAR.SET_OF_EMP_ID=RESP.SET_OF_EMP_ID
AND 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_ID
AND DOC.ACCOUNT_NO=COMB.SEGMENT5
AND DOC.COMPANY_CD=COMB.SEGMENT7

Edited by - Blessed1978 on 03/03/2014 19:51:38
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000