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

From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Wed, 22 Apr 2009 14:19:01 -0700 (PDT)
Message-ID: <3267e7f4-f71f-445c-9075-96271816bdd6_at_q19g2000vbn.googlegroups.com>



On 22 abr, 15:21, Shakespeare <what..._at_xs4all.nl> wrote:
> Jorge Reyes schreef:
>
>
>
>
>
> > 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;
>
> Are you absolutely sure you merge statement is correct? My syntax
> checker can not find the corresponding left and right parentheses in
> that statement.... and it LOOKS like the "-- Cierra USING" is not in the
> correct position: the outer join has not ended there yet!
> But since I don't have the DDL of your tables, it's hard to check at
> this end.
>
> Shakespeare- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Hi thanks for your quickly response,

Im absolutely sure, look the problem is that i have to make a loop i mean the USING part of the MERGE needs 2 vars, this vars must be seted since the source table, like i say my problem is when i use this query like a whole block of PL/SQL like this

DECLARE
    /* Declaring vars */
BEGIN
    /* Setting the vars */

    /* Get start_time and end_time,

       it looks like: start = 2009-04-22 10:00:00 | end = 2009-04-22 13:00:00 */

    /* Then loop (from 2009-04-22 10:00:00 to 2009-04-22 13:00:00)

       and each iteration must increase 1 hour (10:00:00 to 11:00:00, 11:00:00 to 12:00:00 and

       12:00:00 to 13:00:00) */

    /* Merge whit vars */
END; Otherwise if i run just this It works

/* Merge with constants */

So please give me some advice, my goal is to make this process each 15 minutes, the source table may have records since previous hours so thats why the loop.

/* Setting the vars */
/* Get start_time */
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; /* Get limit of the first iteration */
vEND_DATETIME := vPMM_DATETIME + 1/24;

/* Get end_time */
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;

/* Then looping */
WHILE (vPMM_DATETIME <= vMAX_PMM_DATETIME) LOOP

    MERGE INTO OM_DB.CDRS_VALIDOS "X"
    USING (

        SELECT
            ....
        WHERE  A.START_TIME BETWEEN vPMM_DATETIME AND vEND_DATETIME
    )
    ON

        .....

    /* iteration increase 1 hour */
    vPMM_DATETIME := vPMM_DATETIME + 1/24;     vEND_DATETIME := vPMM_DATETIME + 1/24; END LOOP; Regards Received on Wed Apr 22 2009 - 16:19:01 CDT

Original text of this message