Re: Merge clause retrieves ORA-00923 FROM keyword not found where expected
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