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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 22 Apr 2009 22:21:25 +0200
Message-ID: <49ef7c3f$0$184$e4fe514c_at_news.xs4all.nl>

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 Received on Wed Apr 22 2009 - 15:21:25 CDT