Home » Developer & Programmer » Reports & Discoverer » ORA-00932 Error (oracle 10g,reports builder 6i)
ORA-00932 Error [message #430428] Tue, 10 November 2009 06:40 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 2027 times)
Re: ORA-00932 Error [message #430430 is a reply to message #430428] Tue, 10 November 2009 06:49 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Why not using to_char('Yor Date','YYYY') .?
Re: ORA-00932 Error [message #430434 is a reply to message #430430] Tue, 10 November 2009 07:07 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Still am getting the same error after changing the trunc to to_char.
Re: ORA-00932 Error [message #430435 is a reply to message #430428] Tue, 10 November 2009 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the datatype of pdate?
It should be a date but I'm guessing it's char.
Re: ORA-00932 Error [message #430438 is a reply to message #430435] Tue, 10 November 2009 07:16 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
[quote]seyed456 wrote on 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.


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 #430451 is a reply to message #430428] Tue, 10 November 2009 07:49 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Quote:
When am running the scripts in Toad its workign fine.

Then check the data type of report feild where you are calling these records.

Jak
Re: ORA-00932 Error [message #430452 is a reply to message #430438] Tue, 10 November 2009 07:54 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
When am running the scripts in Toad its workign fine.


i missed that point....


Then how you are passing the value there?

And AS mentioned above provide full length query and data types of those parameters you configured.
Sriram
Re: ORA-00932 Error [message #430453 is a reply to message #430450] Tue, 10 November 2009 08:06 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Hello Ram,

What i supposed to do know. I dont understand where am making mistake.Its been longtime since i tried all this.
Re: ORA-00932 Error [message #430454 is a reply to message #430435] Tue, 10 November 2009 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 10 November 2009 13:10
What's the datatype of pdate?


If it's not a date make it a date.
Re: ORA-00932 Error [message #430457 is a reply to message #430452] Tue, 10 November 2009 08:16 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

I sent u full code by email and also check where i needs to change..Please
Re: ORA-00932 Error [message #430458 is a reply to message #430454] Tue, 10 November 2009 08:16 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

cookiemonster wrote on Tue, 10 November 2009 08:08
cookiemonster wrote on Tue, 10 November 2009 13:10
What'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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 Go to previous messageGo to next message
ramoradba
Messages: 2456
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 #430537 is a reply to message #430530] Tue, 10 November 2009 23:03 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Along with this check your report field data type too.
Re: ORA-00932 Error [message #430544 is a reply to message #430428] Tue, 10 November 2009 23:41 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #430607 is a reply to message #430544] Wed, 11 November 2009 03:20 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Is it resolved?
Re: ORA-00932 Error [message #430630 is a reply to message #430428] Wed, 11 November 2009 04:44 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Nope.. Huntin..i Dont giveup..
Re: ORA-00932 Error [message #430633 is a reply to message #430630] Wed, 11 November 2009 05:15 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

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 #670102 is a reply to message #430428] Tue, 05 June 2018 13:04 Go to previous messageGo to next message
Satheeshkumar.P
Messages: 4
Registered: June 2018
Location: Namakkal
Junior Member
It's Easy While you create report builder based on Sql Query that time you cannot pass parameter in select statement.First of all you create (User parameter) in report_builder.After that you call Parameter in select statement.if (inconsistent datatypes: expected NUMBER got date ) Parameter inside change the datatype.I have Attached steps Below..
  • Attachment: Step 1.JPG
    (Size: 25.62KB, Downloaded 1759 times)
Re: ORA-00932 Error [message #670104 is a reply to message #670102] Tue, 05 June 2018 15:49 Go to previous message
Littlefoot
Messages: 21806
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.
Previous Topic: Reports Server seems to be running reports slowly (merged 2)
Next Topic: Unpredictable Report Performance
Goto Forum:
  


Current Time: Thu Mar 28 15:10:32 CDT 2024