Query error - Inconsistent NCLOB data

Asked

Viewed 534 times

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?

Estrutura da tabela JOBEXEC

  • A question: the columns JOBSHORTDESCRIPTION and JOBLONGDESCRIPTION are supposed to store text only? Why declare them as NCLOB?

  • @Joãomartins, NCLOB is also for storing text, but it accepts different Charsets from the default database defined in nls_parameters

  • 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

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.