Hi All,
i am quite new to SQL and am struggling to identify the issue contained in the below script that is preventing it from running for the current date range required (11-Mar-2024 to 18-Mar-2024). This is resulting in an 'ORA-01476' error. I have been able to run the same script successfully for other date ranges. Please see the full script below. Any help is greatly appreciated.
Many thanks,
Michael
---new
select distinct
ct.current_region_type "REGION",
ee.USER_CHAR36 "PERF_ADMIN",
x.xref_account_id as "FMC NAME"
, smd.user_group_desc4 as "FMC_ISSUER_NAME"
, smd.user_group_desc3 as "FMC_ISSUE_NAME"
, trim(sm.security_type) as "SEC TYPE"
, rtrim(ca.entity_id) as "LOCAL ID"
, rtrim(xcus.xref_security_id) as "CUSIP"
, rtrim(xis.xref_security_id) as "ISIN"
, rtrim(xcad.xref_security_id) as "CADIS"
, rtrim(xsed.xref_security_id) as "SEDOL"
, rtrim(xtkr.xref_security_id) as "TICKER"
, rtrim(xfmc.xref_security_id) as "FMCID"
, ca.security_alias
, ca.comments1
, ca.comments4 as "Trans Type"
, t.user_char3 as "operator_name"
, e.base_currency as "BASE"
, sm.currency_code as "LOCAL"
, ca.trans_quantity as "QUANTITY"
, ca.price
, t.fx_rate as "Trade FX RATE"
, ca.trade_date as "TrdDt"
, ca.effective_date as "EffDt"
, ca.cancel_status as "CXL STATUS"
, ca.orig_trans_num
, ca.update_date as "Cash Activity Update Date"
, psr.psr_user_float1 as "BMV Base"
, psr.psr_user_float2 as "EMV Base"
, psr.psr_user_float3 as "Neg Flows Base"
, psr.psr_user_float4 as "Pos Flows Base"
, psr.psr_user_float5 as "Tot Flows Base"
, psr.psr_user_float25 as "BMV Lcl"
, psr.psr_user_float26 as "EMV Lcl"
, psr.psr_user_float27 as "Neg Flows Lcl"
, psr.psr_user_float28 as "Pos Flows Lcl"
, psr.psr_user_float29 as "Tot Flows Lcl"
, psr.psr_user_float8 as "Gof Base"
, psr.psr_user_float32 as "GoF Lcl"
, ((1+psr.psr_user_float8/100)/(1+psr.psr_user_float32/100)-1)*100 as "SEC_CURRENCY_RETURN"
,pd_t.share_par_value
,pd_t.Notional_cost_local as "Sec Market Price_Local"
,pd_t.Notional_cost as "Sec Market Price_Base"
, psr.psr_user_float1 + psr.psr_user_float4 as "SEC_DYNAMIC_BASE_MARKET_VALUE"
, psrf.psr_user_float1 + psrf.psr_user_float4 as "PORT_DYNAMIC_BASE_MARKET_VALUE"
, (psr.psr_user_float1 + psr.psr_user_float4) / (psrf.psr_user_float1 + psrf.psr_user_float4 ) as "SECURITY_DYNAMIC_WEIGHT"
, round(psr.psr_user_float8, 8) as "SECURITY_RETURN_BASE"
, round(((psr.psr_user_float1 + psr.psr_user_float4) / (psrf.psr_user_float1 + psrf.psr_user_float4 )) * psr.psr_user_float8, 8) as "SECURITY_CONTRIBUTION_BASE %"
,pd_tminus1.Notional_cost_local as "Price_Local ED-1"
,pd_t.Notional_cost_local as "Price_Local"
,pd_tplus1.Notional_cost_local as "Price_Local ED+1"
,pd_tplus2.Notional_cost_local as "Price_Local ED+2"
,pd_tplus3.Notional_cost_local as "Price_Local ED+3"
,pd_tplus4.Notional_cost_local as "Price_Local ED+4"
,pd_tplus5.Notional_cost_local as "Price_Local ED+5"
,pd_tminus1.Notional_cost as "Price_Base ED-1"
,pd_t.Notional_cost as "Price_Base"
,pd_tplus1.Notional_cost as "Price_Base ED+1"
,pd_tplus2.Notional_cost as "Price_Base ED+2"
,pd_tplus3.Notional_cost as "Price_Base ED+3"
,pd_tplus4.Notional_cost as "Price_Base ED+4"
,pd_tplus5.Notional_cost as "Price_Base ED+5"
,pd_tminus1.share_par_value as "Share/Par ED-1"
,pd_t.share_par_value as "Share/Par"
,pd_tplus1.share_par_value "Share/Par ED+1"
,pd_tplus2.share_par_value "Share/Par ED+2"
,pd_tplus3.share_par_value "Share/Par ED+3"
,pd_tplus4.share_par_value "Share/Par ED+4"
,pd_tplus5.share_par_value "Share/Par ED+5"
, psrtminus1.psr_user_float32 as "Gross Local t-1"
, psr.psr_user_float32 as "Gross Local"
, psrtplus1.psr_user_float32 as "Gross Local t+1"
, psrtplus2.psr_user_float32 as "Gross Local t+2"
, psrtplus3.psr_user_float32 as "Gross Local t+3"
, psrtplus4.psr_user_float32 as "Gross Local t+4"
, psrtplus5.psr_user_float32 as "Gross Local t+5"
, psrtminus1.psr_user_float8 as "Gross Base t-1"
, psr.psr_user_float8 as "Gross Base"
, psrtplus1.psr_user_float8 as "Gross Base t+1"
, psrtplus2.psr_user_float8 as "Gross Base t+2"
, psrtplus3.psr_user_float8 as "Gross Base t+3"
, psrtplus4.psr_user_float8 as "Gross Base t+4"
, psrtplus5.psr_user_float8 as "Gross Base t+5"
, psrtminus1.psr_user_float26 as "EMV Local t-1"
, psr.psr_user_float26 as "EMV Local"
, psrtplus1.psr_user_float26 as "EMV Local t+1"
, psrtplus2.psr_user_float26 as "EMV Local t+2"
, psrtplus3.psr_user_float26 as "EMV Local t+3"
, psrtplus4.psr_user_float26 as "EMV Local t+4"
, psrtplus5.psr_user_float26 as "EMV Local t+5"
, psrtminus1.psr_user_float2 as "EMV Base t-1"
, psr.psr_user_float2 as "EMV Base"
, psrtplus1.psr_user_float2 as "EMV Base t+1"
, psrtplus2.psr_user_float2 as "EMV Base t+2"
, psrtplus3.psr_user_float2 as "EMV Base t+3"
, psrtplus4.psr_user_float2 as "EMV Base t+4"
, psrtplus5.psr_user_float2 as "EMV Base t+5"
,pd_tminus1.PRICE_RETURN as "Position FX Rate ED-1"
,pd_t.PRICE_RETURN as "Position FX Rate"
,pd_t.MGT_FEE_ACCR_MTD as "Clean MV Base"
,pd_t.COUPON_RECEIVABLE as "Accrued Income Base"
,pd_t.NOTIONAL_UNREALIZED_GL as "Gross MV Base"
,pd_tminus1.MGT_FEE_ACCR_MTD as "Clean MV Base ED-1"
,pd_tminus1.COUPON_RECEIVABLE as "Accrued Income Base ED-1"
,pd_tminus1.NOTIONAL_UNREALIZED_GL as "Gross MV Base ED-1"
,pd_t.MGT_FEE_ACCR_DAILY as "Clean MV Local"
,pd_t.COUPON_RECEIVABLE_LOCAL as "Accrued Income Local"
,pd_t.NOTIONAL_LOCAL_UNREALIZED_GL as "Gross MV Local"
,pd_tminus1.NOTIONAL_LOCAL_UNREALIZED_GL as "Gross MV Local ED-1"
,pd_tminus1.MGT_FEE_ACCR_DAILY as "Clean MV Local ED-1"
,pd_tminus1.COUPON_RECEIVABLE_LOCAL as "Accrued Income Local ED-1"
, pd_t.effective_date as "Pos EffDt"
, ca.curr_fx_rate as "bookcost_local"
, ps.update_date as "Perf Updt"
, psrf.psr_user_float1 as "ED BMV Port"
from cashdbo.cash_activity ca
inner join pace_masterdbo.gdaxna_fmcpos_ent_ctl_vw ct on ct.entity_id = ca.entity_id
inner join rulesdbo.entity_xreference x on ca.entity_id = x.entity_id
and x.xref_account_id_type = 'FMC'
inner join securitydbo.security_master sm on ca.security_alias = sm.security_alias
inner join rulesdbo.entity e on ca.entity_id = e.entity_id
left outer join rulesdbo.entity_extension ee on e.entity_id = ee.entity_id
LEFT join tradesdbo.trade t on t.entity_id = ca.entity_id and t.security_alias = ca.security_alias and t.transaction_id = nvl(ca.linked_event_id, ca.trans_num)
inner join pace_masterdbo.interfaces i2 on t.src_intfc_inst = i2.instance and i2.short_desc in ('IDD')
left outer join securitydbo.xreference xcus on ca.security_alias = xcus.security_alias
and xcus.xref_type = 'CUSIP'
left outer join securitydbo.xreference xis on ca.security_alias = xis.security_alias
and xis.xref_type = 'ISIN'
left outer join securitydbo.xreference xcad on ca.security_alias = xcad.security_alias
and xcad.xref_type = 'CADIS_ID'
left outer join securitydbo.xreference xsed on ca.security_alias = xsed.security_alias
and xsed.xref_type = 'SEDOL'
left outer join securitydbo.xreference xtkr on ca.security_alias = xtkr.security_alias
and xtkr.xref_type = 'TICKER'
left outer join securitydbo.xreference xfmc on ca.security_alias = xfmc.security_alias
and xfmc.xref_type = 'FMC_ID'
inner join securitydbo.security_master_detail smd on sm.security_alias = smd.security_alias
LEFT join performdbo.perf_summary ps on ca.entity_id = ps.entity_id
and ca.effective_date = ps.end_effective_date
and ps.dictionary_id = '0'
and ps.src_intfc_inst = '4'
and ps.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psr on ps.perf_sum_inst = psr.perf_sum_inst
and ca.security_alias = psr.security_alias
LEFT join performdbo.perf_summary pstminus1 on ca.entity_id = pstminus1.entity_id
and ca.effective_date -1 = pstminus1.end_effective_date
and pstminus1.dictionary_id = '0'
and pstminus1.src_intfc_inst = '4'
and pstminus1.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtminus1 on pstminus1.perf_sum_inst = psrtminus1.perf_sum_inst
and ca.security_alias = psrtminus1.security_alias
LEFT join performdbo.perf_summary pstplus1 on ca.entity_id = pstplus1.entity_id
and ca.effective_date +1 = pstplus1.end_effective_date
and pstplus1.dictionary_id = '0'
and pstplus1.src_intfc_inst = '4'
and pstplus1.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtplus1 on pstplus1.perf_sum_inst = psrtplus1.perf_sum_inst
and ca.security_alias = psrtplus1.security_alias
LEFT join performdbo.perf_summary pstplus2 on ca.entity_id = pstplus2.entity_id
and ca.effective_date +2 = pstplus2.end_effective_date
and pstplus2.dictionary_id = '0'
and pstplus2.src_intfc_inst = '4'
and pstplus2.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtplus2 on pstplus2.perf_sum_inst = psrtplus2.perf_sum_inst
and ca.security_alias = psrtplus2.security_alias
LEFT join performdbo.perf_summary pstplus3 on ca.entity_id = pstplus3.entity_id
and ca.effective_date +3 = pstplus3.end_effective_date
and pstplus3.dictionary_id = '0'
and pstplus3.src_intfc_inst = '4'
and pstplus3.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtplus3 on pstplus3.perf_sum_inst = psrtplus3.perf_sum_inst
and ca.security_alias = psrtplus3.security_alias
LEFT join performdbo.perf_summary pstplus4 on ca.entity_id = pstplus4.entity_id
and ca.effective_date +4 = pstplus4.end_effective_date
and pstplus4.dictionary_id = '0'
and pstplus4.src_intfc_inst = '4'
and pstplus4.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtplus4 on pstplus4.perf_sum_inst = psrtplus4.perf_sum_inst
and ca.security_alias = psrtplus4.security_alias
LEFT join performdbo.perf_summary pstplus5 on ca.entity_id = pstplus5.entity_id
and ca.effective_date +5 = pstplus5.end_effective_date
and pstplus5.dictionary_id = '0'
and pstplus5.src_intfc_inst = '4'
and pstplus5.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrtplus5 on pstplus5.perf_sum_inst = psrtplus5.perf_sum_inst
and ca.security_alias = psrtplus5.security_alias
left outer join holdingdbo.position p_t on ca.entity_id = p_t.entity_id
and p_t.src_intfc_inst = '104'
and ca.effective_date = p_t.effective_date
left outer join holdingdbo.position_detail pd_t on p_t.position_id = pd_t.position_id
and ca.security_alias = pd_t.security_alias
left outer join holdingdbo.position p_tminus1 on ca.entity_id = p_tminus1.entity_id
and p_tminus1.src_intfc_inst = '104'
and ca.effective_date -1 = p_tminus1.effective_date
left outer join holdingdbo.position p_tplus1 on ca.entity_id = p_tplus1.entity_id
and p_tplus1.src_intfc_inst = '104'
and ca.effective_date + 1 = p_tplus1.effective_date
left outer join holdingdbo.position p_tplus2 on ca.entity_id = p_tplus2.entity_id
and p_tplus2.src_intfc_inst = '104'
and ca.effective_date + 2 = p_tplus2.effective_date
left outer join holdingdbo.position p_tplus3 on ca.entity_id = p_tplus3.entity_id
and p_tplus3.src_intfc_inst = '104'
and ca.effective_date + 3 = p_tplus3.effective_date
left outer join holdingdbo.position p_tplus4 on ca.entity_id = p_tplus4.entity_id
and p_tplus4.src_intfc_inst = '104'
and ca.effective_date + 4 = p_tplus4.effective_date
left outer join holdingdbo.position p_tplus5 on ca.entity_id = p_tplus5.entity_id
and p_tplus5.src_intfc_inst = '104'
and ca.effective_date + 5 = p_tplus5.effective_date
left outer join holdingdbo.position_detail pd_tminus1 on p_tminus1.position_id = pd_tminus1.position_id
and ca.security_alias = pd_tminus1.security_alias
left outer join holdingdbo.position_detail pd_tplus1 on p_tplus1.position_id = pd_tplus1.position_id
and ca.security_alias = pd_tplus1.security_alias
left outer join holdingdbo.position_detail pd_tplus2 on p_tplus2.position_id = pd_tplus2.position_id
and ca.security_alias = pd_tplus2.security_alias
left outer join holdingdbo.position_detail pd_tplus3 on p_tplus3.position_id = pd_tplus3.position_id
and ca.security_alias = pd_tplus3.security_alias
left outer join holdingdbo.position_detail pd_tplus4 on p_tplus4.position_id = pd_tplus4.position_id
and ca.security_alias = pd_tplus4.security_alias
left outer join holdingdbo.position_detail pd_tplus5 on p_tplus5.position_id = pd_tplus5.position_id
and ca.security_alias = pd_tplus5.security_alias
LEFT join performdbo.perf_summary psf on ca.entity_id = psf.entity_id
and ca.effective_date = psf.end_effective_date
and psf.dictionary_id = '88'
and psf.src_intfc_inst = '4'
and psf.perf_freq_code = 'D'
LEFT join performdbo.perf_sec_returns psrf on psf.perf_sum_inst = psrf.perf_sum_inst
and psrf.perf_rollup_returns_id = '1'
where ca.effective_date between '11-Mar-2024' and '18-Mar-2024'
--and ca.entity_id in '59118'
and rtrim(ca.trans_type) not like '%$%'
and ca.comments4 in ('BCA-IN FRACTION',
'BCA-OUT FRACTION',
'BONUS NEW LINE',
'BONUS NEW LINE SHORT',
'BONUS NEWLINE-NO BCA',
'BONUS-BCA',
'BONUS-BCASHORT',
'BONUS-NO BCASHORT',
'BONUS-SAME',
'BONUS-SAMESHORT',
'BUYFRAC-CASH',
'CALLEDBOND',
'CALLEDBONDSHORT',
'CALLPAYMENT',
'CALLPAYMENTSHORT',
'CALLTRANS-IN',
'CALLTRANS-INSHORT',
'CALLTRANS-OUT',
'CALLTRANS-OUTSHORT',
'CAPDIST',
'CAPDISTSHORT',
'DRIP',
'DRIPSHORT',
'EXCHANGE-IN',
'EXCHANGE-INSHORT',
'EXCHANGE-OUT',
'EXCHANGE-OUTSHORT',
'LAPSEBUYOPT',
'LAPSERTS',
'LAPSERTSSHORT',
'LAPSESELLOPT',
'MATURITY',
'PARIPASSU-IN',
'PARIPASSU-INSHORT',
'PARIPASSU-OUT',
'PARIPASSU-OUTSHORT',
'RESTRUCTURE-BCA',
'RESTRUCTURE-BCASHORT',
'RESTRUCTURE-IN',
'RESTRUCTURE-INSHORT',
'RIGHTSISSUE',
'RIGHTSISSUE SHORT',
'RIGHTSISSUE-BCA',
'RIGHTSISSUE-BCASHORT',
'RIGHTSISSUE-NO BCA',
'RIGHTS-NO BCASHORT',
'SELLFRAC-CASH',
'SHAREX-IN',
'SHAREX-INSHORT',
'SHAREX-OUT',
'SHAREX-OUTSHORT',
'SPLIT/CONSL-OUTSHORT',
'SPLIT/CONSOL-IN',
'SPLIT/CONSOL-INSHORT',
'SPLIT/CONSOL-OUT',
'STOCKDIVIDEND',
'STOCKDIVIDEND-NOBC',
'STOCKDIVIDEND-SHORT',
'STOCKDIV-NOBCSHORT',
'SURRFORCASH',
'SURRFORCASHSHORT',
'USCONSOL',
'USCONSOLSHORT',
'USSPLIT',
'USSPLITSHORT',
'WARRANTEXPIRY',
'WARRANTEXPIRY-SHORT',
'LAPSERTS-NOCASH',
'LAPSERTSSHORT-NOCASH')