Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00402: alias required in SELECT list of cursor to avoid duplicate column (Oracle 11G)
| PLS-00402: alias required in SELECT list of cursor to avoid duplicate column [message #599544] |
Fri, 25 October 2013 13:21  |
 |
kumsenthil
Messages: 4
Registered: October 2013
Location: Pittsburgh
|
Junior Member |
|
|
Hi All,
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error. Can anyone tell me what could be the issue?
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
LLP.INCOME LLP_INCOME,LLP.INT_PURCHASED LLP_INT_PURCHASED,LLP.LOT_NUMBER LLP_LOT_NUMBER,LLP.MARKET_VALUE LLP_MARKET_VALUE,
LLP.PAR_OR_SHARES LLP_PAR_OR_SHARES,LLP.POSITION_ID LLP_POSITION_ID,LLP.PRICE LLP_PRICE,LLP.SECURITY_ALIAS LLP_SECURITY_ALIAS,
LLP.LOT_LEVEL_POSITION LLP_LOT_LEVEL_POSITION,LLP.PUR_YLD LLP_PUR_YLD,LLP.TRADE_DATE LLP_TRADE_DATE,LLP.SETTLEMENT_DATE LLP_SETTLEMENT_DATE,
LLP.LOCAL_MARKET_VALUE LLP_LOCAL_MARKET_VALUE,LLP.ACCRUED_INCOME_LOCAL LLP_ACCRUED_INCOME_LOCAL,LLP.CURR_FX_RATE LLP_CURR_FX_RATE,
LLP.TRADE_FX_RATE LLP_TRADE_FX_RATE,LLP.LOCAL_UNREALIZED_GL LLP_LOCAL_UNREALIZED_GL,LLP.BASE_UNREALIZED_GL LLP_BASE_UNREALIZED_GL,
LLP.LONG_SHORT_IND LLP_LONG_SHORT_IND,LLP.PRE_TAX_ADJ_YLD LLP_PRE_TAX_ADJ_YLD,LLP.GAAP_TRADE_DATE LLP_GAAP_TRADE_DATE,LLP.TAX_STATUS LLP_TAX_STATUS,
LLP.INT_RECEIVED LLP_INT_RECEIVED,LLP.INCOME_DUE LLP_INCOME_DUE,LLP.PR_YR_ACCR_INCOME LLP_PR_YR_ACCR_INCOME,LLP.PURCHASE_QUANTITY LLP_PURCHASE_QUANTITY,
LLP.ORIG_FACE LLP_ORIG_FACE,LLP.MARKET_VALUE_INCOME LLP_MARKET_VALUE_INCOME,LLP.UPDATE_SOURCE LLP_UPDATE_SOURCE,LLP.UPDATE_DATE LLP_UPDATE_DATE,
LLP.ORIG_LOT_NUMBER LLP_ORIG_LOT_NUMBER,LLP.CASH_EQUIVALENTS LLP_CASH_EQUIVALENTS,LLP.REC_PAR_OR_SHARES LLP_REC_PAR_OR_SHARES,
LLP.REC_EP_ACCRUED LLP_REC_EP_ACCRUED, LLP.PAY_EP_ACCRUED LLP_PAY_EP_ACCRUED,LLP.DELTA_ADJ_QTY LLP_DELTA_ADJ_QTY,LLP.OPEN_MKT_PRICE LLP_OPEN_MKT_PRICE,
LLP.UND_PRICE LLP_UND_PRICE, LLP.EXPO_PRICE LLP_EXPO_PRICE,LLP.DLTA LLP_DLTA,LLP.UNREALIZED_CURR_GL LLP_UNREALIZED_CURR_GL,
LLP.PURCHASE_PRINCIPAL_LOCAL LLP_PURCHASE_PRINCIPAL_LOCAL, LLP.PURCHASE_PRINCIPAL_BASE LLP_PURCHASE_PRINCIPAL_BASE,LLP.INDX_WEIGHT LLP_INDX_WEIGHT,
LLP.STATE_COST_VALUE LLP_STATE_COST_VALUE,LLP.ORIGINAL_COST_AMOUNT LLP_ORIGINAL_COST_AMOUNT,LLP.LOCAL_TAX_COST_VALUE LLP_LOCAL_TAX_COST_VALUE,
LLP.PURCH_ACCRUED_INTEREST_AMT LLP_PURCH_ACCRUED_INTEREST_AMT, LLP.AMORTIZED_COST LLP_AMORTIZED_COST,LLP.FED_TAX_COST_VALUE LLP_FED_TAX_COST_VALUE,
LLP.INTEREST_SOLD LLP_INTEREST_SOLD,LLP.AMORTIZATION_LTD_LOCAL LLP_AMORTIZATION_LTD_LOCAL, LLP.AMORTIZATION_LTD_BASE LLP_AMORTIZATION_LTD_BASE,
LLP.MARKET_VALUE_INCOME_LOCAL LLP_MARKET_VALUE_INCOME_LOCAL,LLP.BOOK_VALUE_LOCAL LLP_BOOK_VALUE_LOCAL,LLP.ACCRUED_INCOME LLP_ACCRUED_INCOME,
LLP.INTEREST_EARNED LLP_INTEREST_EARNED,LLP.SETTLE_QUANTITY LLP_SETTLE_QUANTITY,LLP.TIP_LTD_INCOME_LOCAL LLP_TIP_LTD_INCOME_LOCAL,
LLP.TIP_LTD_INCOME LLP_TIP_LTD_INCOME,LLP.UNREALIZED_SEC_GL LLP_UNREALIZED_SEC_GL,LLP.OID_LTD_INCOME_LOCAL LLP_OID_LTD_INCOME_LOCAL,
LLP.OID_LTD_INCOME LLP_OID_LTD_INCOME, LLP.ORIG_TRADE_PRICE LLP_ORIG_TRADE_PRICE,LLP.LAST_SETTLEMENT_DATE LLP_LAST_SETTLEMENT_DATE,
LLP.PERFORMANCE_VALUE LLP_PERFORMANCE_VALUE, LLP.PERFORMANCE_PRICE LLP_PERFORMANCE_PRICE,LLP.NOTIONAL_COST_LOCAL LLP_NOTIONAL_COST_LOCAL,
LLP.NOTIONAL_COST LLP_NOTIONAL_COST, LLP.NOTIONAL_MARKET_VALUE_LOCAL LLP_NOT_MARK_VAL_LOCAL,LLP.NOTIONAL_MARKET_VALUE LLP_NOT_MARK_VAL,
LLP.NOTIONAL_LOCAL_UNREALIZED_GL LLP_NOT_LOC_UNRGL, LLP.NOTIONAL_UNREALIZED_GL LLP_NOT_UNRGL,LLP.NOTIONAL_UNREALIZED_CURR_GL LLP_NOT_UNRGL_CURR,
LLP.NOTIONAL_UNREALIZED_SEC_GL LLP_NOTIONAL_UNREALIZED_SEC_GL, LLP.TRADE_YIELD LLP_TRADE_YIELD,LLP.INTEREST_SOLD_BASE LLP_INTEREST_SOLD_BASE,
LLP.PENDING_TRADE_ID LLP_PENDING_TRADE_ID,LLP.ORIG_EVENT_ID LLP_ORIG_EVENT_ID, LLP.ADJUSTED_SHARES LLP_ADJUSTED_SHARES,LLP.MONTHLY_ACCOUNTING_DATE LLP_MONTHLY_ACCOUNTING_DATE,
LLP.ORIG_ACCT_DATE LLP_ORIG_ACCT_DATE, LLP.ORIG_MONTHLY_ACCT_DATE LLP_ORIG_MONTHLY_ACCT_DATE,LLP.STAR_QUANTITY LLP_STAR_QUANTITY,
LLP.STAR_MARKET_VALUE_LOCAL LLP_STAR_MARK_VAL_LOC,LLP.STAR_MARKET_VALUE LLP_STAR_MAR_VAL,LLP.SETTLEMENT_DATE_POSITION_LOCAL LLP_SETT_DT_POS_LOC,
LLP.SETTLEMENT_DATE_POSITION_COST LLP_SET_DT_POS_COST,LLP.SETTLED_LOCAL_GAAP_COST_VALUE LLP_SET_LOC_GAAP_COSTVAL,
LLP.SETTLED_GAAP_COST_VALUE LLP_SETTLED_GAAP_COST_VALUE,LLP.SETTLED_LOCAL_MARKET_VALUE LLP_SETTLED_LOCAL_MARKET_VALUE,LLP.SETTLED_MARKET_VALUE LLP_SETTLED_MARKET_VALUE,
PCL.POSITION_LOT_ID LLP_POSITION_LOT_ID,PCL.GAAP_BOOK_VALUE LLP_GAAP_BOOK_VALUE,PCL.GAAP_UNIT_BOOK_VALUE LLP_GAAP_UNIT_BOOK_VALUE,PCL.GAAP_COST_VALUE LLP_GAAP_COST_VALUE,
PCL.GAAP_UNIT_COST LLP_GAAP_UNIT_COST ,PCL.STAT_BOOK_VALUE LLP_STAT_BOOK_VALUE,PCL.STAT_UNIT_BOOK_VALUE LLP_STAT_UNIT_BOOK_VALUE,
PCL.STAT_COST_VALUE LLP_STAT_COST_VALUE,PCL.STAT_UNIT_COST LLP_STAT_UNIT_COST ,PCL.TAX_BOOK_VALUE LLP_TAX_BOOK_VALUE,PCL.TAX_UNIT_BOOK_VALUE LLP_TAX_UNIT_BOOK_VALUE ,
PCL.TAX_COST_VALUE LLP_TAX_COST_VALUE,PCL.TAX_UNIT_COST LLP_TAX_UNIT_COST,PCL.LOCAL_GAAP_BOOK_VALUE LLP_LOCAL_GAAP_BOOK_VALUE,
PCL.LOCAL_GAAP_UNIT_BOOK_VALUE LLP_LOCAL_GAAP_UNIT_BOOK_VALUE, PCL.LOCAL_GAAP_COST_VALUE LLP_LOCAL_GAAP_COST_VALUE,PCL.LOCAL_GAAP_UNIT_COST LLP_LOCAL_GAAP_UNIT_COST,
PCL.LOCAL_STAT_BOOK_VALUE LLP_LOCAL_STAT_BOOK_VALUE,PCL.LOCAL_STAT_UNIT_BOOK_VALUE LLP_LOCAL_STAT_UNIT_BOOK_VALUE,PCL.LOCAL_STAT_COST_VALUE LLP_LOCAL_STAT_COST_VALUE,
PCL.LOCAL_STAT_UNIT_COST LLP_LOCAL_STAT_UNIT_COST, PCL.LOCAL_TAX_UNIT_BOOK_VALUE LLP_LOCAL_TAX_UNIT_BOOK_VALUE,PCL.LOCAL_TAX_COST_VALUE LLP_LOCAL_TAX_COST_VALUE,
PCL.LOCAL_TAX_UNIT_COST LLP_LOCAL_TAX_UNIT_COST, PCL.LOCAL_GAAP_MARKET_VALUE LLP_LOCAL_GAAP_MARKET_VALUE,PCL.LOCAL_STAT_MARKET_VALUE LLP_LOCAL_STAT_MARKET_VALUE,
PCL.BASE_GAAP_MARKET_VALUE LLP_BASE_GAAP_MARKET_VALUE, PCL.BASE_STAT_MARKET_VALUE LLP_BASE_STAT_MARKET_VALUE,PCL.ORIG_ACQ_COST_LOCAL LLP_ORIG_ACQ_COST_LOCAL,
PCL.ORIG_ACQ_COST_BASE LLP_ORIG_ACQ_COST_BASE, PCL.ACCR_LTD_LOCAL LLP_ACCR_LTD_LOCAL,PCL.DEFERRED_INCOME_TOTAL_PTD_L LLP_DEF_INC_TOT_PTD_L,
PCL.DEFERRED_INCOME_TOTAL_PTD_B LLP_DEF_INC_TOT_PTD_B, PCL.ORIG_TRADED_INTEREST_BASE LLP_ORIG_TRADED_INT_BASE,
PCL.OID_PTD_LOCAL LLP_OID_PTD_LOCAL,PCL.OID_PTD_BASE LLP_OID_PTD_BASE,PCL.AMORT_PTD_LOCAL AMORT_PTD_LOCAL, PCL.ACCR_LTD_BASE LLP_ACCR_LTD_BASE,
PCL.ORIG_ACCR_PTD_BASE LLP_ORIG_ACCR_PTD_BASE,PCL.AMORT_PTD_BASE LLP_AMORT_PTD_BASE,PCL.ORIG_SOLD_INTEREST_BASE LLP_ORIG_SOLD_INTEREST_BASE,
PCL.TIPS_PTD_LOCAL LLP_TIPS_PTD_LOCAL,PCL.TIPS_PTD_BASE LLP_TIPS_PTD_BASE,PCL.PAY_REC_IND LLP_LLP_PAY_REC_IND,
PCL.IMPAIRMENT_LTD_LOCAL LLP_IMPAIRMENT_LTD_LOCAL, PCL.IMPAIRMENT_LTD_BASE LLP_IMPAIRMENT_LTD_BASE,PCL.NON_CREDIT_LOSS_L LLP_NON_CREDIT_LOSS_L,
PCL.NON_CREDIT_LOSS_B LLP_NONCRED_LOSS_B, PCL.CREDIT_LOSS_L LLP_CREDITLOSS_L,PCL.CREDIT_LOSS_B LLP_CREDITLOSS_B,PCL.NON_CREDIT_LOSS_AMORT_LTD_L LLP_NON_CREDLOSS_AMORT_LTD_L,
PCL.NON_CREDIT_LOSS_AMORT_LTD_B LLP_NON_CREDLOSS_AMRT_LTD_B,traderec1.pendingqtylot pendingqtylot
FROM HOLDINGDBO.POSITION P, HOLDINGDBO.LOT_LEVEL_POSITION LLP,
HOLDINGDBO.POSITION_COST_LOT PCL,
(select ca.position_id, sum(ca.trans_quantity) as pendingqtylot
from cashdbo.cash_activity ca
where ca.position_id=in_position_id
and ca.entity_id = in_entity_id --can use in_position_id??
and ca.src_intfc_inst = my_csd_src_inst
and ca.effective_date <= to_date(in_effective_date,'YYYYMMDD')
and ca.ORIG_CONT_SETTLE_DT <= to_date(in_effective_date,'YYYYMMDD')
and not exists (select * from estar.estar_settled_cash_activity esca
where esca.portfolio_acct = ca.entity_id
and esca.asset_id = ca.security_alias
and esca.target_evt_id = ca.star_tag25
)
group by ca.position_id
) traderec1
WHERE P.entity_id = in_entity_id --may need something else
AND P.position_id = llp.position_id(+)
AND P.position_id = pcl.position_id
AND P.src_intfc_inst = my_csd_src_inst
AND LLP.lot_level_position = pcl.POSITION_LOT_ID(+)
AND P.effective_date = to_date(in_effective_date,'YYYYMMDD')
AND traderec1.position_id(+) = llp.position_id
ORDER BY p.entity_id, p.src_intfc_inst, llp.security_alias, llp.long_short_ind, p.position_id, p.effective_date
)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 18:48:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
