Merge clause retrieves ORA-00923 FROM keyword not found where expected

From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Wed, 22 Apr 2009 09:33:53 -0700 (PDT)
Message-ID: <00140705-8dd6-4f2c-a1bf-db26cab70139_at_f19g2000yqh.googlegroups.com>

Hi everyone,

Using the MERGE clause i want to be able to update/insert records each 15 minutes, so i probe each segment of this code and its working, in fact the segment with the MERGE works perfect replacing the vPMM_DATETIME and vEND_DATETIME variables for constants, but (and its driving me crazy!!!) when i probe this huge query, i get the error:

Error: ORA-00923: FROM keyword not found where expected ORA-06512: at line 63, Batch 1 Line 1 Col 1

Line 63 is the MERGE INTO OM_DB.CDRS_VALIDOS "X", so whats happening??? because each segment of the code works if i probe one by one!!, please some help!!!

Code:

DECLARE

    vPMM_DATETIME        TIMESTAMP(3);
    vMAX_PMM_DATETIME    TIMESTAMP(3);
    vEND_DATETIME        TIMESTAMP(3);
    v_rows_processed     INT;
    count_violated       INT;

BEGIN
    dbms_output.enable(40000);
    dbms_output.put_line('Inicio de procedimiento: ' || TO_CHAR
(sysdate, 'YYYY-MM-DD HH24:MI:SS'));

• Valida si existe informacion en la tabla de origen --

  ---

  count_violated := 0; SELECT COUNT(START_TIME) INTO count_violated FROM OM_DB.CDRS_PRUEBA; IF (count_violated=0) THEN dbms_output.put_line('No hay informacion en la tabla de origen'); ELSE SELECT TO_DATE(TO_CHAR(MIN(START_TIME),'YYYY-MM-DD HH24') || ': 00:00','YYYY-MM-DD HH24:MI:SS') INTO vPMM_DATETIME FROM OM_DB.CDRS_PRUEBA;

        vEND_DATETIME := vPMM_DATETIME + 1/24;

        SELECT TO_DATE(TO_CHAR(MAX(START_TIME),'YYYY-MM-DD HH24') || ': 59:59','YYYY-MM-DD HH24:MI:SS')

        INTO   vMAX_PMM_DATETIME
        FROM   om_db.CDRS_PRUEBA;

        WHILE (vPMM_DATETIME <= vMAX_PMM_DATETIME) LOOP
            MERGE INTO OM_DB.CDRS_VALIDOS "X"
            USING (
                SELECT
                    DISTINCT(ESTADO) AS "ESTADO",
                    MUNICIPIO,
                    CALL_SOURCE_REGID,
                    SUM(EXITOSOS) AS "EXITOSOS",
                    SUM(NO_EXITOSOS) AS "NO_EXITOSOS",
                    SUM(NO_CONECT) AS "NO_CONECT",
                    PMM_DATETIME
                FROM (
                    SELECT
                        COALESCE(V_EXITOSOS.ESTADO,
V_NO_EXITOSOS.ESTADO, V_NO_CONECT.ESTADO) AS "ESTADO",
                        COALESCE(V_EXITOSOS.MUNICIPIO,
V_NO_EXITOSOS.MUNICIPIO, V_NO_CONECT.MUNICIPIO) AS "MUNICIPIO",
                        COALESCE(V_EXITOSOS.CALL_SOURCE_REGID,

V_NO_EXITOSOS.CALL_SOURCE_REGID, V_NO_CONECT.CALL_SOURCE_REGID) AS "CALL_SOURCE_REGID",

                        COALESCE(V_EXITOSOS.EXITOSOS,0) AS "EXITOSOS",
                        COALESCE(V_NO_EXITOSOS.NO_EXITOSOS,0) AS
"NO_EXITOSOS",
                        COALESCE(V_NO_CONECT.NO_CONECT,0) AS
"NO_CONECT",
                        '2009-03-17 17:00:00' AS "PMM_DATETIME"
                    FROM
                    (
                        SELECT
                            DISTINCT(ESTADO) AS "ESTADO",
                            MUNICIPIO,
                            CALL_SOURCE_REGID,
                            COUNT(CALL_SOURCE_REGID) AS "EXITOSOS"
                        FROM (
                            SELECT
                                A.CALL_SOURCE_REGID,
                                B.ESTADO,
                                B.MUNICIPIO
                            FROM   OM_DB.CDRS_PRUEBA A, OM_DB.COFETEL
B
                            WHERE  A.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
                            AND    TO_NUMBER


(A.CALLED_PARTY_ON_DEST_PART3) between B.NIR_INICIAL AND B.NIR_FINAL

                            AND    A.CALL_DURATION_INT >= 5
                            AND    B.MODALIDAD IN ('CPP','FIJO','MPP')
                        )
                        GROUP BY
                            ESTADO,
                            MUNICIPIO,
                            CALL_SOURCE_REGID
                    ) "V_EXITOSOS"
                    FULL OUTER JOIN
                    (
                        SELECT
                            DISTINCT(ESTADO) AS "ESTADO",
                            MUNICIPIO,
                            CALL_SOURCE_REGID,
                            COUNT(CALL_SOURCE_REGID) AS "NO_EXITOSOS"
                        FROM (
                            SELECT
                                C.CALL_SOURCE_REGID,
                                D.ESTADO,
                                D.MUNICIPIO
                            FROM   OM_DB.CDRS_PRUEBA C, OM_DB.COFETEL
D
                            WHERE  C.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
                            AND    TO_NUMBER


(C.CALLED_PARTY_ON_DEST_PART3) between D.NIR_INICIAL AND D.NIR_FINAL

                            AND    C.CALL_DURATION_INT >= 1 AND
C.CALL_DURATION_INT < 5
                            AND    D.MODALIDAD IN ('CPP','FIJO','MPP')
                        )
                        GROUP BY
                            ESTADO,
                            MUNICIPIO,
                            CALL_SOURCE_REGID
                    ) "V_NO_EXITOSOS"
                    ON
                        V_EXITOSOS.ESTADO = V_NO_EXITOSOS.ESTADO
                        AND V_EXITOSOS.MUNICIPIO =
V_NO_EXITOSOS.MUNICIPIO
                        AND V_EXITOSOS.CALL_SOURCE_REGID =
V_NO_EXITOSOS.CALL_SOURCE_REGID
                    FULL OUTER JOIN
                    (
                        SELECT
                            DISTINCT(ESTADO) AS "ESTADO",
                            MUNICIPIO,
                            CALL_SOURCE_REGID,
                            COUNT(CALL_SOURCE_REGID) AS "NO_CONECT"
                        FROM (
                            SELECT
                                C.CALL_SOURCE_REGID,
                                D.ESTADO,
                                D.MUNICIPIO
                            FROM   OM_DB.CDRS_PRUEBA C, OM_DB.COFETEL
D
                            WHERE  C.START_TIME BETWEEN vPMM_DATETIME
AND vEND_DATETIME
                            AND    TO_NUMBER


(C.CALLED_PARTY_ON_DEST_PART3) between D.NIR_INICIAL AND D.NIR_FINAL

                            AND    C.CALL_DURATION_INT = 0
                            AND    D.MODALIDAD IN ('CPP','FIJO','MPP')
                        )
                        GROUP BY
                            ESTADO,
                            MUNICIPIO,
                            CALL_SOURCE_REGID
                    ) "V_NO_CONECT"
                    ON
                        V_NO_CONECT.ESTADO = V_EXITOSOS.ESTADO
                        AND V_NO_CONECT.MUNICIPIO =
V_EXITOSOS.MUNICIPIO
                        AND V_NO_CONECT.CALL_SOURCE_REGID =
V_EXITOSOS.CALL_SOURCE_REGID
                )
                GROUP BY
                    PMM_DATETIME,
                    CALL_SOURCE_REGID,
                    ESTADO,
                    MUNICIPIO
            ) "Y" -- Cierra USING
            ON (
                X.PMM_DATETIME          = Y.PMM_DATETIME
                AND X.CALL_SOURCE_REGID = Y.CALL_SOURCE_REGID
                AND X.ESTADO            = Y.ESTADO
                AND X.MUNICIPIO         = Y.MUNICIPIO
            )
            WHEN MATCHED THEN UPDATE SET
                X.EXITOSOS    = X.EXITOSOS    + Y.EXITOSOS,
                X.NO_EXITOSOS = X.NO_EXITOSOS + Y.NO_EXITOSOS,
                X.NO_CONECT   = X.NO_CONECT   + Y.NO_CONECT
            WHEN NOT MATCHED THEN INSERT (X.ESTADO, X.MUNICIPIO,

X.CALL_SOURCE_REGID, X.EXITOSOS, X.NO_EXITOSOS, X.NO_CONECT, X.PMM_DATETIME)

                VALUES (Y.ESTADO, Y.MUNICIPIO, Y.CALL_SOURCE_REGID,

Y.EXITOSOS, Y.NO_EXITOSOS, Y.NO_CONECT, Y.PMM_DATETIME);

            vPMM_DATETIME := vPMM_DATETIME + 1/24;
            vEND_DATETIME := vPMM_DATETIME + 1/24;
        END LOOP;
        COMMIT;

    END IF;     dbms_output.put_line('Fin de procedimiento: ' || TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
END; Received on Wed Apr 22 2009 - 11:33:53 CDT