ORA-00932: inconsistent datatypes: expected DATE got NUMBER

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

You Asked

Hi Thomas,

Thanks a lot for wonderfull technical support found on this site. I always learn a lot from it.

For most of the applications, I am getting ORA-00932 errors in tkprofed files and even on OEM console while application runs.

Text from tkprofed file:
SELECT 1
FROM
INV_HDR WHERE CUST_COD = :B2 AND TRUNC(INV_DATE) = TRUNC(:B1 -1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2813 0.67 0.75 0 0 0 0
Fetch 2813 4.25 36.99 3907 136725 0 1949
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5626 4.92 37.74 3907 136725 0 1949

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 27 (SPPOST) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

As i have generated trace file using 10046 trace event, bind variables found in the trace file are

:B2=11687
:B1= 6/1/2006 17:57:47

even queries executed through sqlplus session returns errors:
[email protected]#>SELECT 1
FROM
INV_HDR WHERE CUST_COD = 11687 AND TRUNC(INV_DATE) = TRUNC('01-jun-2006 17:57:47') ;

2 3 INV_HDR WHERE CUST_COD = 11687 AND TRUNC(INV_DATE) = TRUNC('01-jun-2006 17:57:47')
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Structure of the table:

[email protected]#>desc INV_HDR
Name Null? Type
----------------------------------------- -------- ----------------------------
INV_NO NOT NULL VARCHAR2(18)
INV_DATE NOT NULL DATE
CUST_COD NOT NULL NUMBER(10)
ST_DT NOT NULL DATE
ED_DT NOT NULL DATE
BILL_SEQ NOT NULL VARCHAR2(8)
BILL_RGN NOT NULL VARCHAR2(5)
BILL_DUE NOT NULL DATE
BILL_AMT NOT NULL NUMBER(14)
BILL_AMT_WT NOT NULL NUMBER(14)
BILL_DTLS NOT NULL CHAR(1)
BILL_DT DATE
POD_DESC VARCHAR2(80)
BILL_POD CHAR(1)
PACK_COD VARCHAR2(7)
PROCESS_DATE DATE

I am facing this errors in almost all the applications for one month and due to this performance is degraded.
Tom please tell me what could be the cause for the same and what are the possible workarounds.
Detailed explanation with examples is desirable.

Warm Regards,

Neeraj Bhatia
@India

and Tom said...

never use explain plan with tkprof, the real plans are already in there!

stop using SYS, just stop that - don't do it, ever - not for testing, not for nothing. Just stop.

All bind variables in explain plan are consider VARCHARS. Doesn't matter how you define them in sqlplus - they are VARCHARS (you don't even *need* to define them to explain them, explain plan doesn't care)

And in your hard coded example, you used - a string, trunc() doesn't know what to expect - and it wasn't expecting a string, a string which it implicitly would try to conver to a number, to be compared to a date - hence the "expected date, got number"

ops$tkyte@ORA9IR2> SELECT 1
2 FROM
3 INV_HDR WHERE CUST_COD = 11687 AND TRUNC(INV_DATE) = TRUNC( to_date('01-jun-2006 17:57:47','dd-mon-yyyy hh24:mi:ss')) ;

no rows selected

Rating

(13 ratings)
Is this answer out of date? If it is, please let us know via a Comment the issue i was facing is -ORA-00932: inconsistent datatypes: expected DATE got NUMBER.
---Here is the trace
INSERT INTO RPM_FUTURE_RETAIL ( FUTURE_RETAIL_ID, ITEM, DEPT, CLASS, SUBCLASS,
ZONE_NODE_TYPE, LOCATION, ACTION_DATE, SELLING_RETAIL,
SELLING_RETAIL_CURRENCY, SELLING_UOM, CLEAR_RETAIL, CLEAR_RETAIL_CURRENCY,
CLEAR_UOM, SIMPLE_PROMO_RETAIL, SIMPLE_PROMO_RETAIL_CURRENCY,
SIMPLE_PROMO_UOM, COMPLEX_PROMO_RETAIL, COMPLEX_PROMO_RETAIL_CURRENCY,
COMPLEX_PROMO_UOM , LOCK_VERSION) SELECT RPM_FUTURE_RETAIL_SEQ.NEXTVAL,
S.ITEM, S.DEPT, S.CLASS, S.SUBCLASS, LOC.TYPE, S.LOC, :B2 -:B1 ,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM, 0 FROM
RPM_STAGE_ITEM_LOC_CLEAN S, (SELECT STORE AS LOCATION, CURRENCY_CODE, 0 AS
TYPE FROM STORE UNION ALL SELECT WH AS LOCATION, CURRENCY_CODE, 2 AS TYPE
FROM WH) LOC WHERE S.LOC = LOC.LOCATION AND NOT EXISTS (SELECT 'x' FROM
RPM_BULK_CC_PE_IL IL WHERE IL.BULK_CC_PE_ID IN ( SELECT DISTINCT NUMBER_2
FROM TABLE(CAST(:B3 AS OBJ_NUM_NUM_STR_TBL))) AND IL.ITEM = S.ITEM AND
IL.LOCATION = S.LOC)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 219.95 8023.95 669072 601120 6470889 375664
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 219.95 8023.95 669072 601120 6470889 375664

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103 (RETEK) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
375664 SEQUENCE RPM_FUTURE_RETAIL_SEQ (cr=3863 pr=8 pw=0 time=16193862 us)
375664 HASH JOIN ANTI (cr=3481 pr=7 pw=0 time=4145065 us)
375696 HASH JOIN (cr=3478 pr=7 pw=0 time=986185 us)
375696 TABLE ACCESS FULL RPM_STAGE_ITEM_LOC_CLEAN (cr=3466 pr=0 pw=0 time=69 us)
150 VIEW (cr=12 pr=7 pw=0 time=32314 us)
150 UNION-ALL (cr=12 pr=7 pw=0 time=32011 us)
148 VIEW index$_join$_004 (cr=6 pr=3 pw=0 time=31540 us)
148 HASH JOIN (cr=6 pr=3 pw=0 time=31387 us)
148 INDEX FAST FULL SCAN PK_STORE (cr=3 pr=1 pw=0 time=12494 us)(object id 78814)
148 INDEX FAST FULL SCAN STORE_I1 (cr=3 pr=2 pw=0 time=17547 us)(object id 78809)
2 VIEW index$_join$_005 (cr=6 pr=4 pw=0 time=98722 us)
2 HASH JOIN (cr=6 pr=4 pw=0 time=98716 us)
2 INDEX FAST FULL SCAN PK_WH (cr=3 pr=2 pw=0 time=65972 us)(object id 79441)
2 INDEX FAST FULL SCAN WH_I2 (cr=3 pr=2 pw=0 time=32422 us)(object id 79437)
32 VIEW VW_SQ_1 (cr=3 pr=0 pw=0 time=579 us)
32 HASH JOIN (cr=3 pr=0 pw=0 time=541 us)
32 TABLE ACCESS FULL RPM_BULK_CC_PE_IL (cr=3 pr=0 pw=0 time=128 us)
11 COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=37 us)

error during execute of EXPLAIN PLAN statement
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

parse error offset: 544
**********************************************************
to resolve the issue i specified the date format..look for the bind variable B2 (B2 is Date and B1 is Number)
Here is the modified Query
INSERT INTO RPM_FUTURE_RETAIL ( FUTURE_RETAIL_ID, ITEM, DEPT, CLASS, SUBCLASS,
ZONE_NODE_TYPE, LOCATION, ACTION_DATE, SELLING_RETAIL,
SELLING_RETAIL_CURRENCY, SELLING_UOM, CLEAR_RETAIL, CLEAR_RETAIL_CURRENCY,
CLEAR_UOM, SIMPLE_PROMO_RETAIL, SIMPLE_PROMO_RETAIL_CURRENCY,
SIMPLE_PROMO_UOM, COMPLEX_PROMO_RETAIL, COMPLEX_PROMO_RETAIL_CURRENCY,
COMPLEX_PROMO_UOM , LOCK_VERSION) SELECT RPM_FUTURE_RETAIL_SEQ.NEXTVAL,
S.ITEM, S.DEPT, S.CLASS, S.SUBCLASS, LOC.TYPE, S.LOC,TO_DATE(:B2, 'dd-mon-yyyy hh24:mi:ss')-:B1 ,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM,
S.SELLING_UNIT_RETAIL, LOC.CURRENCY_CODE, S.SELLING_UOM, 0 FROM
RPM_STAGE_ITEM_LOC_CLEAN S, (SELECT STORE AS LOCATION, CURRENCY_CODE, 0 AS
TYPE FROM STORE UNION ALL SELECT WH AS LOCATION, CURRENCY_CODE, 2 AS TYPE
FROM WH) LOC WHERE S.LOC = LOC.LOCATION AND NOT EXISTS (SELECT 'x' FROM
RPM_BULK_CC_PE_IL IL WHERE IL.BULK_CC_PE_ID IN ( SELECT DISTINCT NUMBER_2
FROM TABLE(CAST(:B3 AS OBJ_NUM_NUM_STR_TBL))) AND IL.ITEM = S.ITEM AND
IL.LOCATION = S.LOC)
******************************************************
Actually now i need to make this kind of change in a lot of places so i was wondering if their was a way i can resolve this by setting parameter NLS_LANG?
Thanks
Raghav

Hi Tom, I have a similar question hope u will answer that.
I have a table emp
with data as follows
SQL> select * from emp;
      EID ENAME                SALARY               HIREDATE
--------- -------------------- -------------------- ---------
 1 Paul         5000      06-SEP-06
 2 Ram         5000      24-AUG-06
 3 Tom          5000                 09-SEP-06
and when i am executing this below query i am getting error pls help
SQL> select eid,cursor(select eid,salary from emp e where e.eid = emp.eid) from emp;
ERROR:
ORA-00932: inconsistent datatypes
no rows selected

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo

If you want me to run a query and diagnose it, you must provide me a create table, insert into statements and a query in general, however....

ops$tkyte%ORA10GR2> create table emp ( eid number, ename varchar2(10), salary number, hiredate date );
Table created.
ops$tkyte%ORA10GR2> insert into emp values ( 1,'Paul',5000,'06-SEP-06' );
1 row created.
ops$tkyte%ORA10GR2> insert into emp values ( 2,'Ram',5000,'24-AUG-06' );
1 row created.
ops$tkyte%ORA10GR2> insert into emp values ( 3,'Tom',5000,'09-SEP-06' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select eid,cursor(select eid,salary from emp e where e.eid = emp.eid) from emp;
       EID CURSOR(SELECTEID,SAL
---------- --------------------
         1 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
       EID     SALARY
---------- ----------
         1       5000
         2 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
       EID     SALARY
---------- ----------
         2       5000
         3 CURSOR STATEMENT : 2
CURSOR STATEMENT : 2
       EID     SALARY
---------- ----------
         3       5000

I don't see what you see, so - post an example that helps us reproduce.
umm, man you do change the question. So we move from "we get an ora error" to "we probably are not getting an ora error but I would like the output formatted a bit differently"

funny thing, deptno - WHERE THE HECK did deptno come from all of a sudden.

Tell you what, when you phrase this as a specification, with a complete example, with creates and inserts and all - then I'll take a look. Pivoting and unpivoting (you are unpivoting) is very easy. Give me something concrete to work with and then we'll talk.

If you close the cursors, there will be a row source operation in the trace file (10g and before), in 11g - regardless of the cursor state, there will be a row source operation for that.

So, go into sqlplus...
turn on tracing
run a query
exit sql plus
trace it - and then get back to us.

read this
http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html
to understand why you don't want to use explain with tkprof.

and you know what is so so very funny to me? That note, it reads:

cause: In the absence of explicit data typecasting, TKPROF assumes that a
bind variable is a CHAR data type. If the SQL statement intends to use the bind
variable as a DATE data type, but TKPROF thought it was a CHAR, the SQL parser
will have a conflict in data types.

wait, hold on - I think I've heard that before... hmmm, where - oh yeah:

All bind variables in explain plan are consider VARCHARS. Doesn't matter how
you define them in sqlplus - they are VARCHARS (you don't even *need* to define
them to explain them, explain plan doesn't care)

And in your hard coded example, you used - a string, trunc() doesn't know what
to expect - and it wasn't expecting a string, a string which it implicitly
would try to conver to a number, to be compared to a date - hence the "expected
date, got number"

that was the original answer..... Interesting.

Hi Tom
i have tried this hard before asking here, serached your site also. I have this strange problem. (this is just a quick and dirty code to understand the reason of differences between 9i and 10g , so please ignore the coding part..i am trying to understand the difference between the 2 versions for the same code.).
in 9i database : 
drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
SQL> DECLARE
  2     TYPE t_cur IS REF CURSOR;
  3     emp_cv  t_cur;
   t_rec  t1%ROWTYPE;
  4    5     sql_stmt VARCHAR2(200);
  6     v_object_id   number := 1107 ;
  7     v_owner varchar2(100) ;
  8     v_object_name varchar2(100) ;
  9  BEGIN
 10     sql_stmt := 'SELECT object_name,owner  FROM t1 WHERE object_id = :j';
 11     OPEN emp_cv FOR sql_stmt USING v_object_id ;
 12     LOOP
 13       FETCH emp_cv INTO v_object_name;
 14       EXIT WHEN emp_cv%NOTFOUND;
 15       DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
 16                             v_object_name);
 17     END LOOP;
 18     CLOSE emp_cv;
 19  END;
 20  /
id: 1107 object_name: NLS_SESSION_PARAMETERS
PL/SQL procedure successfully completed. 
in 10g database (10g R2) 
if I ran the same code....
SQL> drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
Table dropped.
Table created.
SQL> DECLARE
  2     TYPE t_cur IS REF CURSOR;
   emp_cv  t_cur;
  3    4     t_rec  t1%ROWTYPE;
  5     sql_stmt VARCHAR2(200);
  6     v_object_id   number := 1107 ;
  7     v_owner varchar2(100) ;
  8     v_object_name varchar2(100) ;
  9  BEGIN
   sql_stmt := 'SELECT object_name,owner  FROM t1 WHERE object_id = :j';
 10   11     OPEN emp_cv FOR sql_stmt USING v_object_id ;
 12     LOOP
 13       FETCH emp_cv INTO v_object_name;
 14       EXIT WHEN emp_cv%NOTFOUND;
 15       DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
 16                             v_object_name);
 17     END LOOP;
 18     CLOSE emp_cv;
 19  END;
 20  /
DECLARE
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 13
so something has changed between 9i and 10g with respect to dynamic SQL.
the reason I asked this that some of our dynamic SQL codes has started failing in 10g Test environment, we are trying to upgrade from 9i to 10g (yet in small test environment).
- Thanks
you had a bug in your 9i code, we had a bug in our 9i code - we fixed ours in 10g - your select list and FETCH LIST must match, it was an error on our part in 9i correct in 10g.


if you want to select more columns than you actually fetch into, you'd have to use DBMS_SQL which has a procedural API to access the i'th column in a select list and doesn't care if you don't access the second column... 

Hi Tom
i have tried this hard before asking here, serached your site also. I have this strange problem. 
(this is just a quick and dirty code to understand the reason of differences between 9i and 10g , 
so please ignore the coding part..i am trying to understand the difference between the 2 versions 
for the same code.).
in 9i database : 
drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
SQL> DECLARE
  2     TYPE t_cur IS REF CURSOR;
  3     emp_cv  t_cur;
   t_rec  t1%ROWTYPE;
  4    5     sql_stmt VARCHAR2(200);
  6     v_object_id   number := 1107 ;
  7     v_owner varchar2(100) ;
  8     v_object_name varchar2(100) ;
  9  BEGIN
 10     sql_stmt := 'SELECT object_name,owner  FROM t1 WHERE object_id = :j';
 11     OPEN emp_cv FOR sql_stmt USING v_object_id ;
 12     LOOP
 13       FETCH emp_cv INTO v_object_name;
 14       EXIT WHEN emp_cv%NOTFOUND;
 15       DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
 16                             v_object_name);
 17     END LOOP;
 18     CLOSE emp_cv;
 19  END;
 20  /
id: 1107 object_name: NLS_SESSION_PARAMETERS
PL/SQL procedure successfully completed. 
in 10g database (10g R2) 
if I ran the same code....
SQL> drop table t1 ;
create table t1 as select * from all_objects where rownum < 1001 ;
Table dropped.
Table created.
SQL> DECLARE
  2     TYPE t_cur IS REF CURSOR;
   emp_cv  t_cur;
  3    4     t_rec  t1%ROWTYPE;
  5     sql_stmt VARCHAR2(200);
  6     v_object_id   number := 1107 ;
  7     v_owner varchar2(100) ;
  8     v_object_name varchar2(100) ;
  9  BEGIN
   sql_stmt := 'SELECT object_name,owner  FROM t1 WHERE object_id = :j';
 10   11     OPEN emp_cv FOR sql_stmt USING v_object_id ;
 12     LOOP
 13       FETCH emp_cv INTO v_object_name;
 14       EXIT WHEN emp_cv%NOTFOUND;
 15       DBMS_OUTPUT.PUT_LINE('id: ' ||v_object_id || ' object_name: ' ||
 16                             v_object_name);
 17     END LOOP;
 18     CLOSE emp_cv;
 19  END;
 20  /
DECLARE
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 13
so something has changed between 9i and 10g with respect to dynamic SQL.
the reason I asked this that some of our dynamic SQL codes has started failing in 10g Test 
environment, we are trying to upgrade from 9i to 10g (yet in small test environment).
- Thanks
look in the other two places you asked this same exact thing - one of them has your answer, the other says "look in the other place you asked this same exact thing"



you had a bug in your code (you selected 2 columns, fetched into 1 column).  We had a bug in our code that permitted this.  We fixed our bug - you need to fix yours now. 

Hi Tom,

yes it is a bug in our code, true.but the problem is ,we have many programs developed using dynamic SQL now we will have to go through each of them in order to be sure that they will work in 10g as well. i guess if I ask you is there any option, you will say know..but I will still ask if there is any other workaround - if not to fix then at least to find out such issues in the existing code. Hope is a good thing so I asked. 

also - where to find all such changes in 10g (from 9i ) .is there a document which has listed all such changes in 10g. kindly give the URL or name of that doc.

Thanks 

but if the select lists mismatch - you'll probably find some bugs in your code as you go.

and look at each dynamic sql one and ask yourself "wow, did it really need to be dynamic sql" - because in my experience, the answer in plsql is mostly "no, it did not"

This would not be considered a 'change', this was a bug fix, of which there are 'many' 

Hi All,

I totally agree with Tom.

Yes, the expected date got number, and it should be converted explicitly to the corresponded datatype of the selectivity predicate or you have to change the database parameter cursor_sharing form the force stat to at least similar, this is to avoid the bind variable implicit conversion.

   

Hi tom,
I am getting the same error while taking the explain plan for the query my query is as follows 

SELECT  cam.STRPREFERREDNAME AgentName,CAMSLIVE.Com_Rep_Get_Client_Addr_Fun(cam.strclientcd) AgentAddress,
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd)AgentTelephone,                     
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd, 'MOB')AgentMobile,
--SUBSTR(cap.strbankacctnbr,-5,5)AgentBankAccNbr,      
--Commented by Yogendra S on17-Mar-2009                     
cap.strbankacctnbr AgentBankAccNbr,         
--Added by Yogendra S on 16-Mar-2009      
rais.stragentcd AgentCode,CAMSLIVE.Com_Rep_Get_Desgndesc_Fun(cam.strdesgncd)AgentDesignation,
CAMSLIVE.Cm_Fn_Get_Agent_Preferred_Name(cam.stragencycd)AgencyName,CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 1)OfficeCode,                     
CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 2)OfficeName,             
CAMSLIVE.Com_Rep_Get_Addr_Fun(cam.strbranchcd)BranchAddr,
CAMSLIVE.Com_Rep_Get_Client_Contact_Fun(cam.strclientcd, 'OFF')OfficeTelephone, 
ccm.strNewICNbr PANNo,rais.stritemdesc  Item,rais.nitemseq,rais.strspcd SPCode,rais.strdesgncd DesgnLevel,                     
rais.strlaname InsuredName,rais.strpolnbr PolicyNo,rais.npolyearinforce InforceYear,
rais.strpolstatus PolicyStatus,rais.strrop ROPFlag,rais.strpmtmode PaymentMode,rais.DTDUE,
SUM(rais.dmodalprm) over(PARTITION BY rais.stragentcd,rais.dtTo, 
rais.nitemseq,rais.strpolnbr,rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, 
rais.nitemseq,rais.strpolnbr,rais.stritemdesc) ModalPremium, 
-- added rais.stritemdesc in partition by Saurabh on 03122009                     
rais.dtTo PaymentCycleDate,rais.drate PercRate,rais.strmisc,
SUM(rais.dfycamt) over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,rais.stritemdesc)FYC_RYC, 
-- added rais.stritemdesc in partition by Saurabh on 03122009                     
SUM(rais.dfypamt) over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,rais.stritemdesc) FYP_RYP, 
-- added rais.stritemdesc in partition by Saurabh on 03122009                     
--SUM(DECODE(rais.NITEMSEQ, 1,Com_Rep_Get_Open_Bal_Fun(cam.STRAGENTCD, rais.DTTO,1), rais.ddramt)) 
--over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq, rais.strpolnbr)DebitToAgent,
--SUM(DECODE(rais.NITEMSEQ, 1,Com_Rep_Get_OpBal_New_Fun(cam.STRAGENTCD, :bv_payment_from_date ,:bv_payment_to_date,1),rais.ddramt)) 
--over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo,rais.nitemseq, rais.strpolnbr) DebitToAgent,
SUM(DECODE(rais.NITEMSEQ, 1, CAMSLIVE.Com_Rep_Get_OpBal_New_Fun(cam.STRAGENTCD, :bv_payment_from_date ,:bv_payment_to_date ,1),rais.ddramt)) 
over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq, rais.strpolnbr,rais.stritemdesc)DebitToAgent, 
-- added rais.stritemdesc in partition by Saurabh on 03122009                     
--SUM(rais.dcramt) over(PARTITION BY rais.stragentcd,rais.dtTo,rais.nitemseq,rais.strpolnbr ORDER BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr)CreditToAgent,                     
DECODE(rais.nitemseq, 103,(SUM(rais.dcramt) over(PARTITION BY rais.stragentcd,rais.dtTo, rais.nitemseq,rais.strpolnbr,
rais.stritemdesc,rais.strmisc,rais.strmisc ORDER BY rais.stragentcd,rais.dtTo, 
rais.nitemseq,rais.strpolnbr,rais.stritemdesc,rais.strmisc)),
(SUM(rais.dcramt) over (PARTITION BY rais.stragentcd,rais.dtTo, 
rais.nitemseq,rais.strpolnbr,rais.stritemdesc ORDER BY rais.stragentcd,rais.dtTo, 
rais.nitemseq,rais.strpolnbr,rais.stritemdesc))) CreditToAgent,
-- added rais.stritemdesc in partition by Saurabh on 03122009                     
CAMSLIVE.Com_Rep_Get_Comp_Name_Fun CompanyName,CAMSLIVE.Com_Rep_Get_Comp_Regnbr_Fun CompRegNbr,
CAMSLIVE.Com_Rep_Get_Comp_Addr_Fun CompanyAddr,
(SELECT 'For any query,Visit us at ' || ccompm.strcompportal FROM CAMSLIVE.COM_COMPANY_M ccompm WHERE  ccompm.STRCOMPCD = '111')CompanyPortal,
cam.CCHANNELTYPE FROM CAMSLIVE.REP_AGT_INCOME_STMT rais,CAMSLIVE.CHM_AGENT_M  cam,
CAMSLIVE.CHM_AGENT_PAYMENT cap,CAMSLIVE.COM_CLIENT_M  ccm WHERE   rais.stragentcd = NVL(:bv_agent_code,rais.stragentcd)AND rais.nisagtorba = 1          
--AND( (rais.NITEMSEQ=1 AND rais.DTfrom =  TRUNC(:vd_paymentfrom) AND rais.dtto= TRUNC(:vd_paymentto)) or rais.NITEMSEQ !=1)          
AND (rais.NITEMSEQ  !=1 OR (rais.NITEMSEQ=1 AND rais.dtto =  TRUNC(:bv_payment_to_date)))AND TRUNC(rais.DTTO)BETWEEN TRUNC(:bv_payment_from_date) AND TRUNC(:bv_payment_to_date)
AND rais.nitalicuser = NVL(:pi_User_Type, rais.nitalicuser)AND NVL(rais.nbenefittype,-9) <>4 
--Added by ManojP#1 to exclude PDA  
AND  (:bv_office_disp_cd IS NULL OR CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd, 1)=UPPER(:bv_office_disp_cd))                  
AND   rais.stragentcd = cam.stragentcd AND((EXISTS (SELECT 1 FROM CAMSLIVE.COM_USER_M WHERE UPPER(STRUSERID) = UPPER(:pi_userid)
AND STRUSERTYPE = 1))OR(EXISTS(SELECT 1 FROM CAMSLIVE.COM_USER_BRANCH_MAP_M WHERE UPPER(STRUSERID) = UPPER(:pi_userid)                          
AND STRAGNYBRCHDISPCD = CAMSLIVE.Com_Rep_Get_Office_Fun(cam.strbranchcd,1))))
AND cam.stragentcd = cap.stragentcd AND   ccm.strclientcd = cam.strclientcd

and this is the perticular line where i am getting the error

TRUNC(rais.DTTO)BETWEEN TRUNC(:bv_payment_from_date) AND TRUNC(:bv_payment_to_date)
AND rais.nitalicuser

plz provide some solution.
 

  Hi thomas,
    when Enter this code i got this error.please provide solution..........for this query
  1  SELECT ENAME,JOB,SAL EMPSAL,
  2  CONNECT_BY_ROOT(SAL) BOSSSAL,
  3  CONNECT_BY_ROOT(SAL)-SAL DIFF
  4  FROM EMP
  5  START WITH ENAME='KING'
  6* CONNECT BY PRIOR EMPNO=MGR
SQL> /
SELECT ENAME,JOB,SAL EMPSAL,
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got -

    Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.