?
Solved

ORA-00932: inconsistent datatypes

Posted on 2009-05-13
14
Medium Priority
?
2,044 Views
Last Modified: 2013-12-07
Below is a view I have in our system.  I am trying to modify it to load one extra column in the view.  
This column is of different data type, it is a CLOB rather than a string or number.  But I can't find out why this is a problem for the code?  

The part added is:
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'Comments'
                   THEN b.bDescription
                ELSE NULL
             END
            ) AS Comments    

It's basically the exact same code, but I'm returning the CLOB field b.bDescription instead of the text field b.sFieldTarget.  
I get the error message:
ORA-00932: inconsistent datatypes: expected - got CLOB

Can someone explain what the problem is, and how I might get around it??
SELECT   TRIM (a.sdmtreportid) AS sdmtreportid, TRIM (a.stitle) AS descr,
         TRIM (a.stag) AS LOOP,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Link1'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS link1,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Link2'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS link2,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Link3'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS link3,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Display'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS display,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Template'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS TEMPLATE,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Compound'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS compound,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Type'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS TYPE,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Low Scale'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS lowscale,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_High Scale'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS highscale,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Eng Units'
                   THEN SUBSTR (TRIM (b.sfieldtarget), 1, 20)
                ELSE NULL
             END
            ) AS engunits,
         MAX (CASE
                 WHEN TRIM (b.sfieldname) = 'L_Alternate input 0%'
                    THEN TRIM (b.sfieldtarget)
                 ELSE NULL
              END
             ) AS altin0,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Alternate input 25%'
                   THEN TRIM (b.sfieldtarget)
                ELSE NULL
             END
            ) AS altin25,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Alternate input 50%'
                   THEN TRIM (b.sfieldtarget)
                ELSE NULL
             END
            ) AS altin50,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Alternate input 75%'
                   THEN TRIM (b.sfieldtarget)
                ELSE NULL
             END
            ) AS altin75,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Alternate input 100%'
                   THEN TRIM (b.sfieldtarget)
                ELSE NULL
             END
            ) AS altin100,
         MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'L_Alternate input Units'
                   THEN TRIM (b.sfieldtarget)
                ELSE NULL
             END
            ) AS altinunits,   
         MAX 
            (CASE
                WHEN TRIM (b.sfieldname) = 'Comments'
                   THEN b.bDescription
                ELSE NULL
             END
            ) AS Comments                                  
    FROM fcrobj a LEFT OUTER JOIN fcrfield b
         ON (a.lobjid = b.lobjid)
       AND (a.lobjclaid = b.lobjclaid)
       AND (a.sdmtreportid = b.sdmtreportid)
   WHERE TRIM (a.sclassname) = 'L_Loop' OR 
          TRIM (sfieldname) LIKE 'L_%_FBM'
GROUP BY a.sdmtreportid, a.stag, a.stitle

Open in new window

0
Comment
Question by:obrienj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24376605
two problems....

the result of a CASE must return the same type
if one WHEN returns varchar2 then they all must return varchar2
if one WHEN returns clob then they all must return clob

your ELSE is an untyped NULL so it will default to varchar2(4000)

but another problem is the MAX aggregate can not be applied to a CLOB.
that's the main source of the error

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24376627
could your substring your clob?  and only return the first 4000 characters?

MAX
            (CASE
                WHEN TRIM (SUBSTR(b.sfieldname,1,4000)) = 'Comments'
                   THEN b.bDescription
                ELSE TO_CHAR(NULL)  -- or use ''  
             END
            ) AS Comments    
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24376637
ooops, I substr'd  the wrong field


MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'Comments'
                   THEN SUBSTR(b.bDescription,1,4000)
                ELSE TO_CHAR(NULL)  -- or use ''  
             END
            ) AS Comments    
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:obrienj
ID: 24376731
yes, I had a feeling the max wouldn't work, but still had the problem when I removed it.  I'll try put an empty clob into the field see if that works.

hmm, your second option would normally not be a possible solution, but this may be the exception.  The data is been loaded onto a report with only a little space, so it is a stipulation that it be small.  I'll have to test it with the restriction.  
I'll try the other options first.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24376777
empty clob won't work either.  it's not the size of the clob, its the type.  CLOB, BLOB, BFILE, LONG may not be used in aggregates

MAX(any-lob) is illegal
0
 

Author Comment

by:obrienj
ID: 24376814
I tried that piece of code above and got another error:

ORA-00932: inconsistent datatypes: expected CLOB got CHAR
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24376923
which one?  I posted 2 times, the first was a mistake
0
 

Author Comment

by:obrienj
ID: 24376937
The second one.... its highlighting bDescription and giving the error mentioned above.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24377045
that doesn't make sense.

is it the substr?  do you need to use dbms_lob.substr?

MAX
            (CASE
                WHEN TRIM (b.sfieldname) = 'Comments'
                   THEN dbms_lob.substr(b.bDescription,4000,1)
                ELSE TO_CHAR(NULL)  -- or use ''  
             END
            ) AS Comments    

0
 

Author Comment

by:obrienj
ID: 24377131
that worked all right... thanks.  It's getting a bit messy though, I would much rather do it correctly.
I'll come back to that, worst case scenario.

There must be an easy way to load the CLOB field within that view.  

Basically, any way of having a column called Comments that loads the bDescription field into it when the sFieldName = Comments (plus the other stipulations that are in the query already).
??
0
 

Author Comment

by:obrienj
ID: 24382742
Can someone not help me with this???  
It seems pretty standard.  I just want to select the CLOB into the view.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24383785
the problem is you are doing aggregation and you can't aggregate a clob.  It's not "messy" to convert it to a varchar2, or even if it is, it's necessary because of syntax.

I'm guessing you're doing the aggregation in order to achieve a pivot.
If you don't want to convert to varchar2, then remove the column completely from the pivot.
then wrap that query in an inline view and select the clob as a subquery or join outside the inline view.
That, in my opinion, would be much more messy and probably less efficient too since you'll have to double query your table.
0
 

Author Closing Comment

by:obrienj
ID: 31581057
Cheers, I have managed to do it with that method.  I may still go with the varchar conversion method, but at least I have the option now. Thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24384359
glad I could help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question