Home » SQL & PL/SQL » SQL & PL/SQL » view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - (oracle 11g)
view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656456] Thu, 06 October 2016 05:08 Go to next message
kadhir
Messages: 2
Registered: October 2016
Junior Member
I have modified a view by including a dummy numerical field Ftranscode
CREATE OR REPLACE VIEW V_ACC_ENTRY AS
SELECT
DISTINCT 1 SRNO, 
         T.SZ_PRODUCT_CODE,
         APP.SZ_SERVICING_BRANCH_CODE,
         A.SZ_FIELD2 TRANCH_NO,
         A.SZ_FIELD3,
         ' ' BSPL,
         A.SZ_FIELD4, 
         ' ' SZ_TOKEN_CODE,
         B.sz_glcode,
     0 F_TRANS_CODE --new field
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT DISTINCT SZ_EVENT,
                        SZ_APPLICATION_NO,
                        SZ_CR_AC_HEAD ACCT_HEAD,
                        SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
                        'CR' ACCTY,
                        S.SZ_FIELD2,
                        S.SZ_INSTRUMENT_NO,
                        S.sz_glcode
          FROM T_FIN_TRAN S
         WHERE S.SZ_TRANS_TYPE <> 'SUBPRODUCT'
         GROUP BY S.SZ_CR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO,
                  S.sz_glcode) B
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_CR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.SZ_EVENT = 'DISB'
UNION ALL
SELECT DISTINCT 0 SRNO,
                T.SZ_PRODUCT_CODE,
                APP.SZ_SERVICING_BRANCH_CODE,
                A.SZ_FIELD2 TRANCH_NO,
                A.SZ_FIELD3,
                ' ' BSPL,
                A.SZ_FIELD4,
                ' ' SZ_TOKEN_CODE,
                '' sz_glcode,
        0 F_TRANS_CODE --new field
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT DISTINCT SZ_EVENT,
                        SZ_APPLICATION_NO,
                        SZ_DR_AC_HEAD ACCT_HEAD,
                        SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
                        'DR' ACCTY,
                        S.SZ_FIELD2,
                        S.SZ_INSTRUMENT_NO
          FROM T_FIN_TRAN S
         GROUP BY S.SZ_DR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO) B
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_DR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.SZ_EVENT = 'DISB'
UNION ALL
SELECT DISTINCT 1 SRNO,
                T.SZ_PRODUCT_CODE,
                APP.SZ_SERVICING_BRANCH_CODE,
                A.SZ_FIELD2 TRANCH_NO,
                A.SZ_FIELD3,
                ' ' BSPL,
                A.SZ_FIELD4,
                B.SZ_TOKEN_CODE,
                B.SZ_GLCODE sz_glcode,
        B.F_TRANS_CODE--new field
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT SZ_EVENT,
               SZ_APPLICATION_NO,
               SZ_CR_AC_HEAD ACCT_HEAD,
               SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
               'CR' ACCTY,
               S.SZ_FIELD2,
               S.SZ_INSTRUMENT_NO,
               S.SZ_TOKEN_CODE,
               S.F_TRANS_CODE,
               S.SZ_GLCODE 
          FROM T_FIN_TRAN S
         WHERE S.SZ_TRANS_TYPE = 'SUBPRODUCT'
         GROUP BY S.SZ_CR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO,
                  S.SZ_TOKEN_CODE,
                  S.F_TRANS_CODE,
                  S.SZ_GLCODE) B  
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_CR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.F_TRANS_CODE = B.F_TRANS_CODE 
   AND A.SZ_EVENT = 'DISB'
 ORDER by SZ_INSTRUMENT_NO, SRNO, F_TRAN_AMOUNT DESC;

This View is getting compiled.

Already we have a synonym for this view in Another schema as SYN_V_ACC_ENTRY.

In another procedure this synonym is used in Cursor declaration
 CURSOR CUR_ACCENTRY(CP_TRANCHNO NUMBER) IS
    SELECT *
      FROM SYN_V_ACC_ENTRY V_ACC
     WHERE V_ACC.SZ_APPLICATION_NO = P_AGREEMENTNO
       AND V_ACC.TRANCH_NO = CP_TRANCHNO;

During run time, when procedure try to open this cursor Exception thrown as 'ORA-00932: inconsistent datatypes:'

if We comment the newly added field 'FTRANSCODE' procedure works fine.

What is wrong adding a field Ftranscode with Dummy numerical values.? is the SQL Behaviour and cursor behaviour is different?

Please Help me on this.
Re: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656457 is a reply to message #656456] Thu, 06 October 2016 05:20 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
What is data type of F_TRANS_CODE in T_FIN_TRAN? I mean the view shouldnt compile if there's an issue, but check anyway.

What happens if you do select * from the view?

Also check what happens if you explicitly reference the view because the cursor references SZ_APPLICATION_NO which I can't find in your view code as a projected column.

[Updated on: Thu, 06 October 2016 05:30]

Report message to a moderator

Re: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656458 is a reply to message #656457] Thu, 06 October 2016 05:37 Go to previous messageGo to next message
kadhir
Messages: 2
Registered: October 2016
Junior Member
F_TRANS_CODE is NUMBER(10). This View is getting compiled and shows the results.

Since this view has lot of fields, I have deleted some. Please see the original view.

CREATE OR REPLACE VIEW V_ACC_ENTRY AS
SELECT
DISTINCT b."SZ_EVENT",
         b."SZ_APPLICATION_NO",
         b."ACCT_HEAD" SZ_ACCT_CODE,
         b."TRANS_AMT" F_TRAN_AMOUNT,
         b."ACCTY",
         CRM_PKG_CORE_MASTER.FUN_GET_LOOKUP_DESC(A.SZ_ORG_CODE,
                                                 'LEGAL_ENTITY',
                                                 A.SZ_FIELD1) LEGAL_ENTITY,
         1 SRNO, 
         T.SZ_PRODUCT_CODE,
         APP.SZ_SERVICING_BRANCH_CODE,
         A.SZ_FIELD2 TRANCH_NO,
         A.SZ_FIELD3,
         'B' LOCAL_BOOKS,
         'B' GROUP_BOOKS,
         ' ' BSPL,
         A.SZ_NARRATION,
         A.SZ_INSTRUMENT_NO,
         A.DT_INSTRUMENT,
         A.SZ_FIELD4, ,
         ' ' SZ_TOKEN_CODE,
         B.sz_glcode, 
     0 F_TRANS_CODE 
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT DISTINCT SZ_EVENT,
                        SZ_APPLICATION_NO,
                        SZ_CR_AC_HEAD ACCT_HEAD,
                        SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
                        'CR' ACCTY,
                        S.SZ_FIELD2,
                        S.SZ_INSTRUMENT_NO,
                        S.sz_glcode
          FROM T_FIN_TRAN S
         WHERE S.SZ_TRANS_TYPE <> 'SUBPRODUCT'
         GROUP BY S.SZ_CR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO,
                  S.sz_glcode) B
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_CR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.SZ_EVENT = 'DISB'
UNION ALL
SELECT DISTINCT b."SZ_EVENT",
                b."SZ_APPLICATION_NO",
                b."ACCT_HEAD" SZ_ACCT_CODE,
                b."TRANS_AMT" F_TRAN_AMOUNT,
                b."ACCTY",
                CRM_PKG_CORE_MASTER.FUN_GET_LOOKUP_DESC(A.SZ_ORG_CODE,
                                                        'LEGAL_ENTITY',
                                                        A.SZ_FIELD1) LEGAL_ENTITY,
                0 SRNO,
                T.SZ_PRODUCT_CODE,
                APP.SZ_SERVICING_BRANCH_CODE,
                A.SZ_FIELD2 TRANCH_NO,
                A.SZ_FIELD3,
                'B' LOCAL_BOOKS,
                'B' GROUP_BOOKS,
                ' ' BSPL,
                A.SZ_NARRATION,
                A.SZ_INSTRUMENT_NO,
                A.DT_INSTRUMENT,
                A.SZ_FIELD4, 
                ' ' SZ_TOKEN_CODE,
                '' sz_glcode,
        0 F_TRANS_CODE 
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT DISTINCT SZ_EVENT,
                        SZ_APPLICATION_NO,
                        SZ_DR_AC_HEAD ACCT_HEAD,
                        SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
                        'DR' ACCTY,
                        S.SZ_FIELD2,
                        S.SZ_INSTRUMENT_NO
          FROM T_FIN_TRAN S
         GROUP BY S.SZ_DR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO) B
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_DR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.SZ_EVENT = 'DISB'
UNION ALL
SELECT DISTINCT b."SZ_EVENT",
                b."SZ_APPLICATION_NO",
                b."ACCT_HEAD" SZ_ACCT_CODE,
                b."TRANS_AMT" F_TRAN_AMOUNT,
                b."ACCTY",
                CRM_PKG_CORE_MASTER.FUN_GET_LOOKUP_DESC(A.SZ_ORG_CODE,
                                                        'LEGAL_ENTITY',
                                                        A.SZ_FIELD1) LEGAL_ENTITY,
                1 SRNO,
                T.SZ_PRODUCT_CODE,
                APP.SZ_SERVICING_BRANCH_CODE,
                A.SZ_FIELD2 TRANCH_NO,
                A.SZ_FIELD3,
                'B' LOCAL_BOOKS,
                'B' GROUP_BOOKS,
                ' ' BSPL,
                A.SZ_NARRATION,
                A.SZ_INSTRUMENT_NO,
                A.DT_INSTRUMENT,
                A.SZ_FIELD4,
                B.SZ_TOKEN_CODE,
                B.SZ_GLCODE sz_glcode,
        B.F_TRANS_CODE
  FROM T_FIN_TRAN A,
       T_LOAN_DETAILS T,
       T_APPLICATION APP,
       (SELECT SZ_EVENT,
               SZ_APPLICATION_NO,
               SZ_CR_AC_HEAD ACCT_HEAD,
               SUM(S.F_TRAN_AMOUNT) TRANS_AMT,
               'CR' ACCTY,
               S.SZ_FIELD2,
               S.SZ_INSTRUMENT_NO,
               S.SZ_TOKEN_CODE,
               S.F_TRANS_CODE,
               S.SZ_GLCODE 
          FROM T_FIN_TRAN S
         WHERE S.SZ_TRANS_TYPE = 'SUBPRODUCT'
         GROUP BY S.SZ_CR_AC_HEAD,
                  SZ_EVENT,
                  SZ_APPLICATION_NO,
                  S.SZ_FIELD2,
                  S.SZ_INSTRUMENT_NO,
                  S.SZ_TOKEN_CODE,
                  S.F_TRANS_CODE,
                  S.SZ_GLCODE) B   
 WHERE A.SZ_APPLICATION_NO = B.SZ_APPLICATION_NO
   AND A.SZ_EVENT = B.SZ_EVENT
   AND A.SZ_FIELD2 = B.SZ_FIELD2
   AND NVL(A.SZ_INSTRUMENT_NO, 0) = NVL(B.SZ_INSTRUMENT_NO, 0)
   AND A.SZ_CR_AC_HEAD = B.ACCT_HEAD
   AND A.SZ_APPLICATION_NO = APP.SZ_APPLICATION_NO
   AND A.SZ_APPLICATION_NO = T.SZ_APPLICATION_NO
   AND A.F_TRANS_CODE = B.F_TRANS_CODE 
   AND A.SZ_EVENT = 'DISB'
 ORDER by SZ_INSTRUMENT_NO, SRNO, F_TRAN_AMOUNT DESC
;
Re: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656460 is a reply to message #656458] Thu, 06 October 2016 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please post the full error stack of the error message
Re: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656462 is a reply to message #656460] Thu, 06 October 2016 11:55 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi All,

Not able to Understand.Is there having restriction for the number of columns on the view in this case due to which view is getting failed?

[Updated on: Thu, 06 October 2016 11:57]

Report message to a moderator

Re: view and Cursor behavior :Run time Exception:ORA-00932: inconsistent datatypes: expected - got - [message #656464 is a reply to message #656462] Thu, 06 October 2016 12:51 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Try putting the query that is setting up the column first. Change your UNION ALL to a simple UNION to get rid of duplicates and get rid of the distinct's

CREATE OR REPLACE VIEW V_acc_entry
AS
   SELECT 1 Srno,
          T.Sz_product_code,
          App.Sz_servicing_branch_code,
          A.Sz_field2 Tranch_no,
          A.Sz_field3,
          ' ' Bspl,
          A.Sz_field4,
          B.Sz_token_code,
          B.Sz_glcode Sz_glcode,
          B.F_trans_code  
     FROM T_fin_tran A,
          T_loan_details T,
          T_application App,
          (  SELECT Sz_event,
                    Sz_application_no,
                    Sz_cr_ac_head Acct_head,
                    SUM (S.F_tran_amount) Trans_amt,
                    'CR' Accty,
                    S.Sz_field2,
                    S.Sz_instrument_no,
                    S.Sz_token_code,
                    S.F_trans_code,
                    S.Sz_glcode
               FROM T_fin_tran S
              WHERE S.Sz_trans_type = 'SUBPRODUCT'
           GROUP BY S.Sz_cr_ac_head,
                    Sz_event,
                    Sz_application_no,
                    S.Sz_field2,
                    S.Sz_instrument_no,
                    S.Sz_token_code,
                    S.F_trans_code,
                    S.Sz_glcode) B
    WHERE     A.Sz_application_no = B.Sz_application_no
          AND A.Sz_event = B.Sz_event
          AND A.Sz_field2 = B.Sz_field2
          AND NVL (A.Sz_instrument_no, 0) = NVL (B.Sz_instrument_no, 0)
          AND A.Sz_cr_ac_head = B.Acct_head
          AND A.Sz_application_no = App.Sz_application_no
          AND A.Sz_application_no = T.Sz_application_no
          AND A.F_trans_code = B.F_trans_code
          AND A.Sz_event = 'DISB'
   UNION
   SELECT 1 Srno,
          T.Sz_product_code,
          App.Sz_servicing_branch_code,
          A.Sz_field2 Tranch_no,
          A.Sz_field3,
          ' ' Bspl,
          A.Sz_field4,
          ' ' Sz_token_code,
          B.Sz_glcode,
          0 F_trans_code    
     FROM T_fin_tran A,
          T_loan_details T,
          T_application App,
          (  SELECT DISTINCT Sz_event,
                             Sz_application_no,
                             Sz_cr_ac_head Acct_head,
                             SUM (S.F_tran_amount) Trans_amt,
                             'CR' Accty,
                             S.Sz_field2,
                             S.Sz_instrument_no,
                             S.Sz_glcode
               FROM T_fin_tran S
              WHERE S.Sz_trans_type <> 'SUBPRODUCT'
           GROUP BY S.Sz_cr_ac_head,
                    Sz_event,
                    Sz_application_no,
                    S.Sz_field2,
                    S.Sz_instrument_no,
                    S.Sz_glcode) B
    WHERE     A.Sz_application_no = B.Sz_application_no
          AND A.Sz_event = B.Sz_event
          AND A.Sz_field2 = B.Sz_field2
          AND NVL (A.Sz_instrument_no, 0) = NVL (B.Sz_instrument_no, 0)
          AND A.Sz_cr_ac_head = B.Acct_head
          AND A.Sz_application_no = App.Sz_application_no
          AND A.Sz_application_no = T.Sz_application_no
          AND A.Sz_event = 'DISB'
   UNION
   SELECT 0 Srno,
          T.Sz_product_code,
          App.Sz_servicing_branch_code,
          A.Sz_field2 Tranch_no,
          A.Sz_field3,
          ' ' Bspl,
          A.Sz_field4,
          ' ' Sz_token_code,
          '' Sz_glcode,
          0 F_trans_code    
     FROM T_fin_tran A,
          T_loan_details T,
          T_application App,
          (  SELECT DISTINCT Sz_event,
                             Sz_application_no,
                             Sz_dr_ac_head Acct_head,
                             SUM (S.F_tran_amount) Trans_amt,
                             'DR' Accty,
                             S.Sz_field2,
                             S.Sz_instrument_no
               FROM T_fin_tran S
           GROUP BY S.Sz_dr_ac_head,
                    Sz_event,
                    Sz_application_no,
                    S.Sz_field2,
                    S.Sz_instrument_no) B
    WHERE     A.Sz_application_no = B.Sz_application_no
          AND A.Sz_event = B.Sz_event
          AND A.Sz_field2 = B.Sz_field2
          AND NVL (A.Sz_instrument_no, 0) = NVL (B.Sz_instrument_no, 0)
          AND A.Sz_dr_ac_head = B.Acct_head
          AND A.Sz_application_no = App.Sz_application_no
          AND A.Sz_application_no = T.Sz_application_no
          AND A.Sz_event = 'DISB'
   ORDER BY Sz_instrument_no, Srno, F_tran_amount DESC;

[Updated on: Thu, 06 October 2016 12:53]

Report message to a moderator

Previous Topic: REGEXP_REPLACE
Next Topic: Count of Data in Each Subpartition
Goto Forum:
  


Current Time: Thu Mar 28 07:21:17 CDT 2024