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;

Jorge Reyes schreef:
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

On 22 abr, 15:21, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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

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

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

Shakespeare