DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Apr 2009
    Posts
    3

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

    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;

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Distinct()?

    The DISTINCT keyword is NOT A FUNCTION!!!!

    "DISTINCT(ESTADO)" is incorrect, remove it because you already have a GROUP BY aggregate on "ESTADO" -- and try again.

    Last edited by LKBrwn_DBA; 04-23-2009 at 08:28 AM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    Ok, DISTINCT is working, but anyway i remove it and still appears the same error

    Regards

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Question Mistery #1

    Did you post the exact procedure you are trying to execute?
    None of my tools gives syntax error.
    Check if you have some "hidden" characters (for example in unix ^M) in your code.


    PS: Based on you other PLS-00801 post, try removing all comments from the sql. Also try saving to a file and executing with start {script file}.sql (or @{script file}.sql ).

    What is your OS?
    .
    Last edited by LKBrwn_DBA; 04-23-2009 at 12:36 PM. Reason: PLS-00801 post...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width