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 |
|
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 #656458 is a reply to message #656457] |
Thu, 06 October 2016 05:37 |
|
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 #656464 is a reply to message #656462] |
Thu, 06 October 2016 12:51 |
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
|
|
|
Goto Forum:
Current Time: Sun May 19 09:58:57 CDT 2024
|