obrienj
asked on
ORA-00932: inconsistent datatypes
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??
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
could your substring your clob? and only return the first 4000 characters?
MAX
(CASE
WHEN TRIM (SUBSTR(b.sfieldname,1,400 0)) = 'Comments'
THEN b.bDescription
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
MAX
(CASE
WHEN TRIM (SUBSTR(b.sfieldname,1,400
THEN b.bDescription
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
ooops, I substr'd the wrong field
MAX
(CASE
WHEN TRIM (b.sfieldname) = 'Comments'
THEN SUBSTR(b.bDescription,1,40 00)
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
MAX
(CASE
WHEN TRIM (b.sfieldname) = 'Comments'
THEN SUBSTR(b.bDescription,1,40
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
ASKER
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.
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.
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
MAX(any-lob) is illegal
ASKER
I tried that piece of code above and got another error:
ORA-00932: inconsistent datatypes: expected CLOB got CHAR
ORA-00932: inconsistent datatypes: expected CLOB got CHAR
which one? I posted 2 times, the first was a mistake
ASKER
The second one.... its highlighting bDescription and giving the error mentioned above.
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.bDescrip tion,4000, 1)
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
is it the substr? do you need to use dbms_lob.substr?
MAX
(CASE
WHEN TRIM (b.sfieldname) = 'Comments'
THEN dbms_lob.substr(b.bDescrip
ELSE TO_CHAR(NULL) -- or use ''
END
) AS Comments
ASKER
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).
??
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).
??
ASKER
Can someone not help me with this???
It seems pretty standard. I just want to select the CLOB into the view.
It seems pretty standard. I just want to select the CLOB into the view.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
glad I could help
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