erreur “ORA-00932: incompatible types de données: - got CLOB” sélectionnez joindre plusieurs tables

je suis d'erreur lorsque j'utilise yz table qui a param champ avec le type de données==> CLOB . Et c'est ma requête:

WITH t AS
     (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,yz.param
            ,MAX(y.seq) AS seq2
            ,MAX(y.extern_order_status) AS extern
      FROM   t_order_demand x
      JOIN   t_order_log y
      ON     x.order_id = y.order_id
      JOIN   p_catalog_status z
      ON     z.status_code_sc = y.extern_order_status
  JOIN   t_order_demand_eai yz
      ON     yz.order_id = y.order_id
      AND    y.order_id =1294
      GROUP  BY x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,y.nd1,z.status_resume,yz.param)
    SELECT *
    FROM   t
    WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)

et c'est l'erreur:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

quelqu'un peut m'aider à corriger cette erreur? merci...

OriginalL'auteur Nike Angreni | 2014-11-17

  1. 2

    enfin j'ai eu la réponse juste ajouter dbms_lob.substr(yz.param,4000,1) de sélectionner et regrouper par. 

    WITH t AS
             (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,dbms_lob.substr(yz.param,4000,1)
                    ,MAX(y.seq) AS seq2
                    ,MAX(y.extern_order_status) AS extern
              FROM   t_order_demand x
              JOIN   t_order_log y
              ON     x.order_id = y.order_id
              JOIN   p_catalog_status z
              ON     z.status_code_sc = y.extern_order_status
          JOIN   t_order_demand_eai yz
              ON     yz.order_id = x.order_id
              AND    y.order_id =1290
              GROUP  BY x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,y.nd1,z.status_resume,dbms_lob.substr(yz.param,4000,1))
            SELECT *
            FROM   t
            WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)

    merci pour votre réponse


    FIXE

    OriginalL'auteur Nike Angreni

  2. 0
    WITH t AS
     (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,yz.param
            ,MAX(y.seq) AS seq2
            ,MAX(y.extern_order_status) AS extern
      FROM   t_order_demand x
      JOIN   t_order_log y
      ON     x.order_id = y.order_id
      JOIN   p_catalog_status z
      ON     z.status_code_sc = y.extern_order_status
  JOIN   t_order_demand_eai yz
      ON     yz.order_id = y.order_id
      AND    y.order_id =1294
      GROUP  BY x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,y.nd1,z.status_resume)
    SELECT *
    FROM   t
    WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)




lob fields can not be used as a group by
    si je n'ai pas l'ajouter dans le groupe, j'en ai une autre erreur ORA-00979: pas un GROUPE PAR l'expression 00979. 00000 - "pas un GROUPE PAR l'expression"
    supprimer yz.param，au Lieu d'utiliser une sous-requête
    merci pour suggérer @sunysen

    OriginalL'auteur sunysen