Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    1

    Unanswered: Calculating in views - Help Needed!!

    Hi,
    I am trying to create a view as per the following script:
    Code:
    CREATE OR REPLACE VIEW ETL.MTB_ICRP041_49_52_VW
    (AMOUNT, QUANTITY, CANCEL_CODE, REBILL_CODE, INV_PRICE,
     FACTOR, CUSIP, SEC_NO, SECURITY_DESC, SEC_TYPE,
     BOND_TYPE, NEW_SEC_TYPE, IVN_DESC, CONTROL_ACCT, IVN_GROUP_DESC,
     CUR_VAL, CUR_QTY, ACCT_NO, T_OR_S, IVN_ACCT_GRP,
     AGE, TODAYS_MKT_PRICE, YST_MKT_PRICE, CUR_P_L, CUR_UR_P_L,
     YST_P_L, YST_UR_P_L, MKT_VALUE, TRAN_CYMD, MOODYS,
     SANDP, BALANCE, TODAYS_AVG_COST_PRICE, PCT_CHG_PL, PCT_CHG_TRADE_PRICE,
     PCT_CHG_COST_PRICE, PCT_CHG_MKT_PRICE, TODAYS_AVG_TRADE_PRICE, PREVIOUS_AVG_COST_PRICE, TODAYS_REALIZED,
     TODAYS_UNREALIZED, PRICING_DATE)
    AS
    SELECT
    RD.AMOUNT                                                  AMOUNT,
    RD.QUANTITY                                                QUANTITY,
    RD.CANCEL_CODE                                             CANCEL_CODE,
    RD.REBILL_CODE                                             REBILL_CODE,
    RD.INV_PRICE                                               INV_PRICE,
    'SEC_FCT.FACTOR  - revisit'                                 FACTOR,
    ILV1.CUSIP                                                  CUSIP,
    ILV1.SEC_NO                                                 SEC_NO,
    ILV1.SECURITY_DESC											SECURITY_DESC,
    ILV1.SEC_TYPE												SEC_TYPE,
    ILV1.BOND_TYPE												BOND_TYPE,
    ILV1.NEW_SEC_TYPE											NEW_SEC_TYPE,
    ILV1.IVN_DESC												IVN_DESC,
    ILV1.CONTROL_ACCT											CONTROL_ACCT,
    ILV1.IVN_GROUP_DESC											IVN_GROUP_DESC,
    ILV1.CUR_VAL												CUR_VAL,
    ILV1.CUR_QTY												CUR_QTY,
    ILV1.ACCT_NO												ACCT_NO,
    ILV1.T_OR_S													T_OR_S,
    ILV1.IVN_ACCT_GRP											IVN_ACCT_GRP,
    ILV1.AGE													AGE,
    ILV1.TODAYS_MKT_PRICE										TODAYS_MKT_PRICE,
    ILV1.YST_MKT_PRICE											YST_MKT_PRICE,
    ILV1.CUR_P_L												CUR_P_L,
    ILV1.CUR_UR_P_L												CUR_UR_P_L,
    ILV1.YST_P_L												YST_P_L,
    ILV1.YST_UR_P_L												YST_UR_P_L,
    ILV1.MKT_VALUE												MKT_VALUE,
    ILV1.TRAN_CYMD												TRAN_CYMD,
    ILV1.MOODYS													MOODYS,
    ILV1.SANDP													SANDP,
    ILV1.BALANCE												BALANCE,
    ILV1.TODAYS_AVG_COST_PRICE									TODAYS_AVG_COST_PRICE,
    ILV1.PCT_CHG_PL												PCT_CHG_PL,
    ILV1.PCT_CHG_TRADE_PRICE									PCT_CHG_TRADE_PRICE,
    ILV1.PCT_CHG_COST_PRICE										PCT_CHG_COST_PRICE,
    ILV1.PCT_CHG_MKT_PRICE										PCT_CHG_MKT_PRICE,
    ILV1.TODAYS_AVG_TRADE_PRICE									TODAYS_AVG_TRADE_PRICE,
    ILV1.PREVIOUS_AVG_COST_PRICE								PREVIOUS_AVG_COST_PRICE,
    ILV1.TODAYS_REALIZED										TODAYS_REALIZED,
    ILV1.TODAYS_UNREALIZED										TODAYS_UNREALIZED,
    ILV1.PRICING_DATE											PRICING_DATE
    FROM RIT_DTL RD, (SELECT  	S.CUSIP	      			CUSIP,
    									S.SEC_NO      			SEC_NO,
    									S.SECURITY_DESC1 ||' '||S.SECURITY_DESC2||' '||S.SECURITY_DESC3||' '||XST.SEC_DESC4||' '||XST.SEC_DESC5||' '||XST.SEC_DESC6 SECURITY_DESC,
    									S.SEC_TYPE    			SEC_TYPE,
    									S.BOND_TYPE   			BOND_TYPE,
    									S.NEW_SEC_TYPE 			NEW_SEC_TYPE,
    									I.IVN_ACCT_DESC 		IVN_DESC,
    									I.CONTROL_ACCT 			CONTROL_ACCT,
    									I.IVN_ACCT_DESC 		IVN_GROUP_DESC,
    									I.INV_ACCT_NO 			INV_ACCT_NO,
    									RP.CUR_VAL   			CUR_VAL,
    									RP.CUR_QTY   			CUR_QTY,
    									RP.ACCT_NO   			ACCT_NO,
    									RP.T_OR_S    			T_OR_S,
    									I.IVN_ACCT_GRP 			IVN_ACCT_GRP,
    									'AGE  - revisit'  		AGE,
    									RP.MKT_PRICE    		TODAYS_MKT_PRICE,
    									RP.MKT_PRICE   	    YST_MKT_PRICE,
    									RP.CUR_P_L      		CUR_P_L,
    									RP.CUR_UR_P_L   		CUR_UR_P_L,
    									RP.YST_P_L      		YST_P_L,
    									RP.YST_UR_P_L   		YST_UR_P_L,
    									RP.MKT_VALUE    		MKT_VALUE,
    									RP.TRAN_CYMD    		TRAN_CYMD,
    									S.MOODYS         		MOODYS,
    									S.SANDP          		SANDP,
    									'BALANCE'        		BALANCE,
    									RP.CUR_VAL/RP.CUR_QTY TODAYS_AVG_COST_PRICE,
    								--	'TODAYS_AVG_COST_PRICE' TODAYS_AVG_COST_PRICE,
    								     (ILV1.TODAYS_AVG_COST_PRICE-RPT.MKT_PRICE) AS TEMPVAR1,
    								 ILV1.TEMPVAR1/(RP.CUR_VAL/RP.CUR_QTY)  PCT_CHG_PL,
    								--	'PCT_CHG_PL'            PCT_CHG_PL,
    									'PCT_CHG_TRADE_PRICE'   PCT_CHG_TRADE_PRICE,
    									'PCT_CHG_COST_PRICE'    PCT_CHG_COST_PRICE,
    									'PCT_CHG_MKT_PRICE'     PCT_CHG_MKT_PRICE,
    									'TODAYS_AVG_TRADE_PRICE'TODAYS_AVG_TRADE_PRICE,
    									'PREVIOUS_AVG_COST_PRICE'PREVIOUS_AVG_COST_PRICE,
    									'TODAYS_REALIZED'       TODAYS_REALIZED,
    									'TODAYS_UNREALIZED'     TODAYS_UNREALIZED,
    									'PRICING_DATE'          PRICING_DATE
    						FROM
    						SEC	S,
    						IVN	I,
    						RIT_POS	RP,
    						XREF_SEC_TRANS XST
    						WHERE
    						RP.ACCT_NO = I.INV_ACCT_NO
    						AND RP.SEC_NO = S.SEC_NO
    						AND S.SEC_NO = XST.SEC_NO
    						) ILV1
    WHERE
    RD.ACCT_NO = ILV1.INV_ACCT_NO
    AND RD.SEC_NO = ILV1.SEC_NO;
    For the column - PCT_CHG_PL the Calculation is (TODAYS_AVG_COST_PRICE-TODAYS_MKT_PRICE)/TODAYS_AVG_COST_PRICE
    Where TODAYS_AVG_COST_PRICE=RP.CUR_VAL/RP.CUR_QTY
    The script is giving an error for PCT_CHG_PL .

    Please Help..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A view cannot reference itself within its own definition, which is what you are tring to do in the inline view ILV1 here:
    Code:
    (SELECT ...
            (ILV1.TODAYS_AVG_COST_PRICE-RPT.MKT_PRICE) AS TEMPVAR1,
    	ILV1.TEMPVAR1/(RP.CUR_VAL/RP.CUR_QTY)  PCT_CHG_PL,
            ...
    ) ILV1
    Instead you should do this:
    Code:
    (SELECT ...
    	((RP.CUR_VAL/RP.CUR_QTY)-RPT.MKT_PRICE)/(RP.CUR_VAL/RP.CUR_QTY)  PCT_CHG_PL,
            ...
    ) ILV1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •