Query working Oracle 10G not working in 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query working Oracle 10G not working in 9i

Hybrid View

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Query working Oracle 10G not working in 9i

    I have created a view in Oracle and compiled. The view got compiled in both Oracle 10G and Oracle 9i but when i tried to query the view it gives ORA-00911 Error in Oracle 9i whereas it worked fine in Oracle 10G. can anyone help me in this regard. i've pasted the view script below. thanks in advance

    CREATE OR REPLACE VIEW V_SEC_HOLDINGS_ARV
    (MODULE, FUND_CODE, IND_CODE, IND_NAME, ISM_CODE,
    ISM_NAME, EQ_ISM_NAME, BETA, HLDQTY, MKTVAL,
    MKTPRICE, OS_HLDQTY, OS_MKTVAL, SALQTY, BOOKCOST,
    BOOKVAL, PLVALUE)
    AS
    SELECT 'DEBT' MODULE, FUNDCODE FUND_CODE, -2 IND_CODE, 'Debt' IND_NAME,
    -2 ISM_CODE, 'Debt' ISM_NAME, '' EQ_ISM_NAME, 0 AS BETA,
    SUM (QTY) HLDQTY, SUM (MVAL) MKTVAL, 0 MKTPRICE,
    0 OS_HLDQTY, 0 OS_MKTVAL, 0 SALQTY,0 BOOKCOST,
    0 BOOKVAL, 0 PLValue
    FROM (SELECT QDP_FUNDCODE FUNDCODE, SUM (QDP_HLDQTY) QTY,
    SUM (QDP_HLDQTY * QDP_MKTPRICE) MVAL
    FROM QUANTIS_DAILY_POSITIONS
    WHERE QDP_ASONDATE = TRUNC (SYSDATE)
    AND QDP_MODULE IN ('DEBT', 'FD')
    AND QDP_HLDQTY > 0
    GROUP BY QDP_FUNDCODE
    UNION
    SELECT DS_FUND_ID FUNDCODE, SUM (DS_QUANTITY) QTY,
    SUM (DS_DEALVALUE) MVAL
    FROM PRIMARYDEALS
    WHERE DS_RECORD_STATUS = 1
    AND DS_APPROVAL_FLAG = 2
    AND DS_REVERSAL_FLAG = 'N'
    AND DS_TRADEDATE <= TRUNC (SYSDATE)
    AND (DS_SECURITY_CODE, DS_FUND_ID, DS_DEALNO) NOT IN (
    SELECT DISTINCT PA_SECID, PA_FUNDID, PA_DEALNO
    FROM PRIMARY_ALLOTMENT
    WHERE PA_RECORD_STATUS = 1
    AND PA_APPROVAL_FLAG = 2)
    GROUP BY DS_FUND_ID
    UNION
    SELECT RT_FUND_ID FUNDCODE, SUM (RT_QUANTITY) QTY,
    SUM (RT_TOTALPRICE) MVAL
    FROM REPOTRADES
    WHERE RT_TRADEDATE <= TRUNC (SYSDATE)
    AND RT_REVERSAL_DATE > TRUNC (SYSDATE)
    AND RT_FUND_TYPE = 'FUND'
    AND RT_RECORD_STATUS = 1
    AND RT_APPROVAL_FLAG = 2
    AND RT_REVERSAL_FLAG = 'N'
    GROUP BY RT_FUND_ID
    UNION
    SELECT CBLO_FUNDID FUNDCODE,
    SUM (DECODE (CBLO_TRANSTYPE,
    'BUY', CBLO_FACEVALUE / 100,
    'SELL', (CBLO_FACEVALUE * -1) / 100
    )
    ) QTY,
    SUM (DECODE (CBLO_TRANSTYPE,
    'BUY', CBLO_FACEVALUE,
    'SELL', CBLO_FACEVALUE * -1
    )
    ) MVAL
    FROM CBLO_TRADES
    WHERE CBLO_FUNDID <> 1
    AND CBLO_RECORD_STATUS = 1
    AND CBLO_APPROVAL_FLAG = 2
    AND CBLO_REVERSAL_FLAG = 'N'
    AND CBLO_TRADEDATE <= TRUNC (SYSDATE)
    AND CBLO_MATURITY > TRUNC (SYSDATE)
    GROUP BY CBLO_FUNDID)
    GROUP BY FUNDCODE
    UNION
    SELECT 'MUTUAL FUND' MODULE, QDP_FUNDCODE FUNDCODE, -3 IND_CODE,
    'Mutual Fund' IND_NAME, -3 ISM_CODE, 'Mutual Fund' ISM_NAME,
    '' EQ_ISM_NAME, 0 AS BETA, SUM (QDP_HLDQTY) HLDQTY,
    SUM (QDP_HLDQTY * QDP_MKTPRICE) MKTVAL, 0 MKTPRICE,
    0 OS_HLDQTY, 0 OS_MKTVAL, 0 SALQTY, 0 BOOKCOST,
    0 BOOKVAL, 0 PLValue
    FROM QUANTIS_DAILY_POSITIONS
    WHERE QDP_ASONDATE = TRUNC (SYSDATE)
    AND QDP_MODULE = 'MFUNITS'
    AND QDP_HLDQTY > 0
    GROUP BY QDP_FUNDCODE
    union
    SELECT QDP_MODULE MODULE, QDP_FUNDCODE FUND_CODE,
    NVL (IND_CODE, -1) IND_CODE,
    DECODE (IND_CODE,
    NULL, 'Index Derivatives',
    INITCAP (IND_NAME)
    ) IND_NAME,
    NVL (ISM_CODE, -1) ISM_CODE, INITCAP (ISM_NAME) ISM_NAME,
    DECODE (QDP_MODULE, 'EQUITY', ISM_NAME, QDP_ISMNAME) EQ_ISM_NAME,
    DECODE (QDP_MODULE, 'EQUITY', NVL (EQB_BETA_DATA, 0), 0) BETA,
    QDP_HLDQTY HLDQTY, (QDP_HLDQTY * QDP_MKTPRICE) MKTVAL,
    QDP_MKTPRICE MKTPRICE, QDP_OS_HLDQTY OS_HLDQTY,
    (QDP_OS_HLDQTY * QDP_MKTPRICE) OS_MKTVAL, QDP_SALEABLE_QTY SALQTY,
    QDP_BOOKCOST BOOKCOST, (QDP_HLDQTY * QDP_BOOKCOST) BOOKVAL,
    (QDP_HLDQTY * (QDP_MKTPRICE - QDP_BOOKCOST)) PLValue
    FROM QUANTIS_DAILY_POSITIONS,
    (SELECT EQ_ISM_CODE ISM_CODE, EQ_ISM_SHORT_NAME ISM_NAME,
    EQ_ISM_CPM_CODE CMP_CODE
    FROM EQ_INSTRUMENT_MASTER
    WHERE EQ_ISM_RECORD_STATUS = 1
    UNION ALL
    SELECT BENCHMARK_CODE ISM_CODE, BNC_SHORTNAME, -1 AS CMP_CODE
    FROM BENCHMARK
    WHERE BNC_RECORD_STATUS = 1) UL,
    (SELECT CPM_CODE, CPM_NAME, CPM_INDUSTRYCODE
    FROM COMPANY_MASTER
    WHERE CPM_RECORD_STATUS = 1) CM,
    (SELECT IND_CODE, IND_NAME
    FROM INDUSTRY_MASTER
    WHERE IND_RECORD_STATUS = 1) IM,
    (SELECT EQB_ASONDATE, EQB_ISMCODE, EQB_BETA_DATA
    FROM EQ_SCRIP_BETA OEB
    WHERE EQB_RECORD_STATUS = 1
    AND EQB_ASONDATE =
    (SELECT MAX (EQB_ASONDATE)
    FROM EQ_SCRIP_BETA
    WHERE EQB_RECORD_STATUS = 1
    AND EQB_ISMCODE = OEB.EQB_ISMCODE)) ESB
    WHERE QDP_ASONDATE = TRUNC (SYSDATE)
    AND QDP_MODULE IN ('EQUITY', 'FUTURES', 'OPTIONS')
    AND QDP_ISMCODE = UL.ISM_CODE(+)
    AND UL.CMP_CODE = CM.CPM_CODE(+)
    AND CM.CPM_INDUSTRYCODE = IM.IND_CODE(+)
    AND QDP_ISMCODE = ESB.EQB_ISMCODE(+);

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Post whole error stack.
    Also describe both Ora10g and Ora9i databases including character sets.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    thnx a lot PAVB for your prompt reply. i have given the requested details below,

    ORacle 9i - Parameters

    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 9.2.0.1.0

    Oracle 10G Parameters

    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 10.2.0.1.0

    I hope this information hold good for you. when i was trying to query the specific view i got the following Errors, ORA-00903: invalid table name or ORA-00911: invalid character. i was completely clueless why this happens. Please help me in this regard. thanks in advance

Posting Permissions

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


Click Here to Expand Forum to Full Width