SELECT
  STGBQ_ROWID,
  STGBQ_CLT_ID,
  STGBQ_CLT_BANK_ID,
  STGBQ_DT_JOUR, 
  STGBQ_DT_CPTA,
  STGBQ_MVT_OP,    
  STGBQ_DESC_OP,
  STGBQ_MT, 
  MAX (KEWD_MATCH),
  STGBQ_EXERID 
 /* MAX (MODPAIE_MATCH),
  MAX (PARTN_MATCH)*/
FROM  
 (

    SELECT
            STGBQ_ROWID,
            STGBQ_CLT_ID,
            STGBQ_CLT_BANK_ID,
            STGBQ_DT_JOUR,
            STGBQ_DT_CPTA,
            STGBQ_MVT_OP,      
            STGBQ_MT,
            STGBQ_DESC_OP,
            REGEXP_SUBSTR (STGBQ_DESC_OP,LIST_KEYWD) AS KEWD_MATCH, 
            REGEXP_SUBSTR (STGBQ_DESC_OP,'PRELEVEMENT|CHEQUE|CARTE|VIR') AS MODPAIE_MATCH,
            REGEXP_SUBSTR (STGBQ_DESC_OP,'RSI|URSSAF|IMPOT|VIR|BOUYGUES|FREE TELECOM|MMA') AS PARTN_MATCH,
           STGBQ_EXERID            
    FROM
        (
          SELECT 
            STGBQ_ROWID,
            STGBQ_CLT_ID,
            STGBQ_CLT_BANK_ID,
            STGBQ_DT_JOUR,
            STGBQ_DT_CPTA,
            'DEBIT' AS STGBQ_MVT_OP,      
            STGBQ_MT_DEBIT as STGBQ_MT,
            STGBQ_DESC_OP,
            STGBQ_EXER_ID   
          FROM   MYSQL_STG_CLT_BANQ
          WHERE STGBQ_MT_DEBIT IS NOT NULL AND STGBQ_CLT_ID=8
          
          UNION 
          
          SELECT 
            STGBQ_ROWID,
            STGBQ_CLT_ID,
            STGBQ_CLT_BANK_ID,
            STGBQ_DT_JOUR,
            STGBQ_DT_CPTA,
            'CREDIT' AS STGBQ_MVT_OP,      
            STGBQ_MT_CREDIT as STGBQ_MT,
            STGBQ_DESC_OP,
            STGBQ_EXER_ID   
          FROM   MYSQL_STG_CLT_BANQ
          WHERE STGBQ_MT_CREDIT IS NOT NULL AND STGBQ_CLT_ID=8
        ) TRN LEFT OUTER JOIN 
        (
          SELECT CLT_ID, CGTR_ID, GROUP_CONCAT( KEYWORD_VAL SEPARATOR ' | ' )  AS LIST_KEYWD
          FROM
          (
            SELECT CLT_ID, CGTR_ID, KEYWORD_VAL 
            FROM 
               (
              SELECT  8  as CLT_ID, CGTR_ID, KEYWORD_VAL
              FROM MYSQL_T_MAP_KEYWD_CATG 
              WHERE CLT_ID=0  AND TRSC_MAP_TYPE ='CATEGORIE'
               ) A
               
               UNION
               
               (
              SELECT CLT_ID, CGTR_ID, KEYWORD_VAL
              FROM MYSQL_T_MAP_KEYWD_CATG 
              WHERE CLT_ID<>0 AND CLT_ID= 8   AND TRSC_MAP_TYPE ='CATEGORIE'
               ) 
             
          ) C
          GROUP BY CLT_ID, CGTR_ID
        ) KEYW ON (TRN.STGBQ_CLT_ID=KEYW.CLT_ID)

)
GROUP BY
  STGBQ_ROWID,
  STGBQ_CLT_ID,
  STGBQ_CLT_BANK_ID,
  STGBQ_DT_JOUR, 
  STGBQ_DT_CPTA,
  STGBQ_MVT_OP,    
  STGBQ_MT, 
  STGBQ_DESC_OP,
  STGBQ_EXERID