Home » Developer & Programmer » Reports & Discoverer » ORA-00932 Error (oracle 10g,reports builder 6i)
| ORA-00932 Error [message #430428] |
Tue, 10 November 2009 06:40  |
|
|
Am getting an error in the report builder..While creating a new report.
When am running the scripts in Toad its workign fine.
Error :
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
WHEN sls.docdt==> BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
Can you tell me where i have to make changes .I tried with the to_date still hopeless.
For more information please find the attached image
-
Attachment: Untitled.jpg
(Size: 11.98KB, Downloaded 2536 times)
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00932 Error [message #430438 is a reply to message #430435] |
Tue, 10 November 2009 07:16  |
|
|
Thanks.
I tried to change the pdate datatype to date also still remains the same,.
The following codes i have tried so far but still facing the problem in report builder.
Please suggest what wud i do.
CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TO_CHAR (:pdate,
'YYYY'
),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
CASE
WHEN sls.docdt BETWEEN TO_CHAR (:pdate, 'YEAR')
AND :pdate
THEN slsd.amt1
ELSE 0
END
CASE
WHEN sls.docdt BETWEEN TO_DATE('01/01/'|| TO_CHAR(TO_DATE('30/09/2008','DD/MM/RRRR'),'RRRR'),'DD/MM/RRRR')
AND TO_DATE('30/09/2008','DD/MM/RRRR')
THEN slsd.amt1
ELSE 0
END
CASE
WHEN sls.docdt BETWEEN TO_DATE('01/01/'|| TO_CHAR(TO_DATE('30/09/2009','DD/MM/RRRR'),'RRRR'),'DD/MM/RRRR')
AND TO_DATE('30/09/2009','DD/MM/RRRR')
THEN slsd.amt1
ELSE 0
END
Though i tried Truncate also still no hopes/
CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
[Updated on: Tue, 10 November 2009 07:19] Report message to a moderator |
|
|
|
| Re: ORA-00932 Error [message #430450 is a reply to message #430438] |
Tue, 10 November 2009 07:46 |
 |
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
[quote]seyed456 wrote on Tue, 10 November 2009 07:16Thanks.
I tried to change the pdate datatype to date also still remains the same,.
The following codes i have tried so far but still facing the problem in report builder.
Please suggest what wud i do.
insted of posting a bit of query Why dont you post your query and the defined parametere and there format..here
Why because In report builder It will give you the correct error and but shows you the incorrect line some times...
Sriram.
[Updated on: Tue, 10 November 2009 07:48] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00932 Error [message #430458 is a reply to message #430454] |
Tue, 10 November 2009 08:16 |
|
|
cookiemonster wrote on Tue, 10 November 2009 08:08cookiemonster wrote on Tue, 10 November 2009 13:10What's the datatype of pdate?
If it's not a date make it a date.
I did still remains same..
|
|
|
|
| Re: ORA-00932 Error [message #430460 is a reply to message #430428] |
Tue, 10 November 2009 08:19 |
|
|
Full Query
/* Formatted on 2009/11/10 16:59 (Formatter Plus v4.8.8) */
SELECT sdep.NAME,
SUM
(DECODE
(prefc.class_code,
'AA', (CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
)
)
) "2008data1L",
SUM
(DECODE (prefc.class_code,
'AA', (CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
)
)
) "2009data1L",
SUM
(DECODE
(prefc.class_code,
'AB', (CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
)
)
) "2008_200ml",
SUM
(DECODE (prefc.class_code,
'AB', (CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
)
)
) "2009_200ml",
SUM
(DECODE
(prefc.class_code,
'AC', (CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
)
)
) "2008_500ml",
SUM
(DECODE (prefc.class_code,
'AC', (CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
)
)
) "2009_500ml",
SUM
(DECODE
(prefc.refcode,
'A', (CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
)
)
) "2008_totaluht",
SUM
(DECODE (prefc.refcode,
'A', (CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
)
)
) "2009_totaluht",
SUM
(DECODE
(prefc.refcode,
'P', (CASE
WHEN sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate,
'YYYY'),
-12
)
AND ADD_MONTHS (:pdate, -12)
THEN slsd.amt1
ELSE 0
END
)
)
) "2008_tomatopaste",
SUM
(DECODE (prefc.refcode,
'P', (CASE
WHEN sls.docdt BETWEEN TRUNC (:pdate, 'YEAR') AND :pdate
THEN slsd.amt1
ELSE 0
END
)
)
) "2009_tomatopaste",
sreg.NAME
FROM sls_head sls,
sls_detail slsd,
prodfle pf,
prodref pref,
prodref_classification prefc,
customer c,
-- ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno = slsd.docno
AND sls.depot = slsd.depot
AND sls.docdt = slsd.docdt
AND sls.tc = slsd.tc
AND sls.depot = sdep.depot
AND sls.docdt BETWEEN ADD_MONTHS (TRUNC (:pdate, 'YYYY'), -12) AND :pdate
AND slsd.prodcd = pf.prodcd
--AND pf.refcode =pref.refcode
AND pf.class_code = prefc.class_code
AND c.depot = sls.depot
AND c.cusno = sls.cusno
--AND c.custype =ct.code
AND sdep.rgncode = sreg.rgncode
--AND prefc.refcode IN ('A')
--and prefc.class_code in ('AA','AB','AC')
AND sls.depot NOT IN (82, 83, 85)
GROUP BY sdep.NAME, sreg.NAME
-- case when prefc.class_code in ('AA','AB','AC') then prefc.description else null end
--ORDER BY depot
|
|
|
|
| Re: ORA-00932 Error [message #430530 is a reply to message #430460] |
Tue, 10 November 2009 22:29 |
|
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
As per your query,
We dont know youe table structure and we dont know which format string (Input mask you are using for that :pdate if it is date)....
And in my previous post i asked you to show us the data types you are using....
But you did n`t provide any info...
And you said
Quote:If i give 31/sep/09 in the parameter
then it find sum(amt between 1stjan08 and 31stsep08),
sum(amt between 1stjan09 and 31stsep09)
Are these dates.?
Sriram.
[Updated on: Tue, 10 November 2009 22:34] Report message to a moderator |
|
|
|
|
|
| Re: ORA-00932 Error [message #430544 is a reply to message #430428] |
Tue, 10 November 2009 23:41 |
|
|
CREATE TABLE SLS_HEAD
(
DEPOT NUMBER(2),
ACTCDE VARCHAR2(1 BYTE) DEFAULT ' ',
DOCNO NUMBER(10),
DOCDT DATE,
TC NUMBER(2),
CUSNO NUMBER(8),
SMANCD NUMBER(4),
DISTCD NUMBER(2),
INVCDSCP NUMBER(7,3),
INVCDSCV NUMBER(13,3),
INVQDSCV NUMBER(13,3))
CREATE TABLE SLS_DETAIL
(
DEPOT NUMBER(2),
DOCNO NUMBER(10),
DOCDT DATE,
TC NUMBER(2),
PRODCD VARCHAR2(6 BYTE),
QTY1 NUMBER(12),
AMT1 NUMBER(13,3),
CD_SVAL NUMBER(13,3))
Sorry for the late response.
And I dint mention all the columns since am facing problem in docdt.
|
|
|
|
|
|
|
|
| Re: ORA-00932 Error [message #430633 is a reply to message #430630] |
Wed, 11 November 2009 05:15 |
|
|
keep it up... have a quick look what is the data type for the report (on RDF) feild data type and size you are using if there is any ambuguity for dates and formats.
Jak
|
|
|
|
|
|
| Re: ORA-00932 Error [message #670104 is a reply to message #670102] |
Tue, 05 June 2018 15:49 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
|
Replying to almost a decade old question, eh? Anyway: as far as I can tell, you can use a parameter while writing report's query. The Builder will inform you that a new user parameter has been created; no need in creating the parameter first. Though, yes - you should modify its datatype, if necessary.
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 22:23:37 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
