Re: Merge clause retrieves ORA-00923 FROM keyword not found where expected
Date: Wed, 22 Apr 2009 14:21:57 -0700 (PDT)
Message-ID: <37f995ba-3733-4423-afdd-ae16189c6837_at_o27g2000vbd.googlegroups.com>
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:21:57 CDT