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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 22 Apr 2009 23:24:09 +0200
Message-ID: <49ef8af3$0$188$e4fe514c_at_news.xs4all.nl>

Shakespeare schreef:

> 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

Tested your merge statement on parentheses; seems to be correct anyway. Can't find what's wrong here....

Shakespeare Received on Wed Apr 22 2009 - 16:24:09 CDT