Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Yak Posting Veteran

97 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  
 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.04 seconds. Powered By: Snitz Forums 2000