0
Hello,
I am trying to perform a query in an Oracle database and is returning an error
ORA-00932: inconsistent data types: expected - obtained NCLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause:
*Action:
The query is the following:
SELECT DISTINCT SQL1.JOBNAME
, SQL1.COMPILATION
, SQL1.FOLDERPATH
, SQL2.JOBSHORTDESCRIPTION
, SQL2.JOBLONGDESCRIPTION
FROM (
SELECT DISTINCT JOBNAME
, FOLDERPATH
, MAX(COMPILATIONTIMESTAMP) AS COMPILATION
FROM JOBEXEC
WHERE PROJECTNAME = 'MDM_PRD'
AND FOLDERPATH NOT LIKE '%Temporario%'
AND JOBNAME NOT LIKE '%Copy%'
AND JOBNAME NOT LIKE '%Teste%'
GROUP BY JOBNAME
, FOLDERPATH
, COMPILATIONTIMESTAMP
) SQL1
INNER JOIN JOBEXEC SQL2 ON SQL1.JOBNAME = SQL2.JOBNAME
This database records the information as JOBNAME receives a compilation, creating several records with the same name in the JOBNAME field, for this it is necessary to give a DISTINCT in JOBNAME to bring me only one record, but when the JOBSHORTDESCRIPTION and JOBLONGDESCRIPTION fields are within select, returns the error reported above.
I need to build this query by pulling all information without repetitions, but still with the description, someone would help me in how I can rebuild this query?
A question: the columns
JOBSHORTDESCRIPTION
andJOBLONGDESCRIPTION
are supposed to store text only? Why declare them asNCLOB
?– João Martins
@Joãomartins, NCLOB is also for storing text, but it accepts different Charsets from the default database defined in nls_parameters
– Confundir
In fact these DB fields were set by default at the time of IBM tool installation, so it is not possible to make the change
– Carlos Henrique Cardoso