This discussion is archived
11 Replies Latest reply: Jan 30, 2013 12:51 PM by bostonmacosx RSS

First Merge Attempt Failling -

bostonmacosx Newbie
Currently Being Moderated
APEX 4.1.1 Oracle 11g(well at least 10g)....

So to get better performance I'm trying a merge instead of a NOT EXIST statement with INSERT. Although I'm tearing my head out now...the SQL and error code are below.
BEGIN
 MERGE into SLA_TIMES SLTM
 USING (select SUBSTR(c001,'1',INSTR(c001||'.','.')-1) as c001,
c002,
(from_tz(cast(unixts_to_date(c003) AS TIMESTAMP),'GMT') AT LOCAL) as c003new,
(from_tz(cast(unixts_to_date(c004) AS TIMESTAMP),'GMT') AT LOCAL) as c004new,
c005,
c006,
c007,
c008,
c009,
c010,
c011,
c012,
c013,
c014,
c015,
c016,
c017,
c018,
c019,
c020,
c021,
c022,
c023,
c024,
c025,
c026,
c027,
c028,
c029,
c030
    from apex_collections
    where collection_name = v('P_COLLECTION_VAR') 
    and 
    (to_char(new_time(unixts_to_date(c003),'GMT','EST'),'MON-YYYY')=to_char(new_time(unixts_to_date(c004),'GMT','EST'),'MON-YYYY') ) ) mycol

ON (SLTM.NODE_NAME=mycol.c001 and SLTM.TEST_NAME=mycol.002)

WHEN NOT MATCHED THEN 
      INSERT  (SLTM.NODE_NAME,
SLTM.TEST_NAME,
SLTM.START_TIME,
SLTM.END_TIME,
SLTM.247_TOTAL_AVAIL,
SLTM.247_GREEN_AVAIL,
SLTM.247_GREEN_SECONDS,
SLTM.247_CLEAR_AVAIL,
SLTM.247_CLEAR_SECONDS,
SLTM.247_BLUE_AVAIL,
SLTM.247_BLUE_SECONDS,
SLTM.247_PURPLE_AVAIL,
SLTM.247_PURPLE_SECONDS,
SLTM.247_YELLOW_AVAIL,
SLTM.247_YELLOW_SECONDS,
SLTM.247_RED_AVAIL,
SLTM.247_RED_SECONDS,
SLTM.SLA_TOTAL_AVAIL,
SLTM.SLA_GREEN_AVAIL,
SLTM.SLA_GREEN_SECONDS,
SLTM.SLA_CLEAR_AVAIL,
SLTM.SLA_CLEAR_SECONDS,
SLTM.SLA_BLUE_AVAIL,
SLTM.SLA_BLUE_SECONDS,
SLTM.SLA_PURPLE_AVAIL,
SLTM.SLA_PURPLE_SECONDS,
SLTM.SLA_YELLOW_AVAIL,
SLTM.SLA_YELLOW_SECONDS,
SLTM.SLA_RED_AVAIL,
SLTM.SLA_RED_SECONDS
) VALUES (
mycol.c001, 
mycol.c002,
mycol.c003new,
mycol.c004new,
mycol.c005,
mycol.c006,
mycol.c007,
mycol.c008,
mycol.c009,
mycol.c010,
mycol.c011,
mycol.c012,
mycol.c013,
mycol.c014,
mycol.c015,
mycol.c016,
mycol.c017,
mycol.c018,
mycol.c019,
mycol.c020,
mycol.c021,
mycol.c022,
mycol.c023,
mycol.c024,
mycol.c025,
mycol.c026,
mycol.c027,
mycol.c028,
mycol.c029,
mycol.c030);    

APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v('P_COLLECTION_VAR'));

END;
and the error
1 error has occurred
ORA-06550: line 39, column 55: PL/SQL: ORA-00907: missing right parenthesis ORA-06550: line 2, column 2: PL/SQL: SQL Statement ignored
honestly after a few hours I just don't see it.
  • 1. Re: First Merge Attempt Failling -
    sb92075 Guru
    Currently Being Moderated
    bostonmacosx wrote:
    APEX 4.1.1 Oracle 11g(well at least 10g)....

    So to get better performance I'm trying a merge instead of a NOT EXIST statement with INSERT. Although I'm tearing my head out now...the SQL and error code are below.
    BEGIN
    MERGE into SLA_TIMES SLTM
    USING (select SUBSTR(c001,'1',INSTR(c001||'.','.')-1) as c001,
    c002,
    (from_tz(cast(unixts_to_date(c003) AS TIMESTAMP),'GMT') AT LOCAL) as c003new,
    (from_tz(cast(unixts_to_date(c004) AS TIMESTAMP),'GMT') AT LOCAL) as c004new,
    c005,
    c006,
    c007,
    c008,
    c009,
    c010,
    c011,
    c012,
    c013,
    c014,
    c015,
    c016,
    c017,
    c018,
    c019,
    c020,
    c021,
    c022,
    c023,
    c024,
    c025,
    c026,
    c027,
    c028,
    c029,
    c030
    from apex_collections
    where collection_name = v('P_COLLECTION_VAR') 
    and 
    (to_char(new_time(unixts_to_date(c003),'GMT','EST'),'MON-YYYY')=to_char(new_time(unixts_to_date(c004),'GMT','EST'),'MON-YYYY') ) ) mycol
    problem exist on line above starting with "AND"
  • 2. Re: First Merge Attempt Failling -
    bostonmacosx Newbie
    Currently Being Moderated
    but even if I remove that line I can get
    1 error has occurred
    ORA-06550: line 38, column 55: PL/SQL: ORA-00907: missing right parenthesis ORA-06550: line 3, column 2: PL/SQL: SQL Statement ignored
  • 3. Re: First Merge Attempt Failling -
    sb92075 Guru
    Currently Being Moderated
    bostonmacosx wrote:
    but even if I remove that line I can get
    1 error has occurred
    ORA-06550: line 38, column 55: PL/SQL: ORA-00907: missing right parenthesis ORA-06550: line 3, column 2: PL/SQL: SQL Statement ignored
    where collection_name = v('P_COLLECTION_VAR')
    what exactly is on the right side of the equal sign above?
  • 4. Re: First Merge Attempt Failling -
    bostonmacosx Newbie
    Currently Being Moderated
    C003 and C004 area UNIX timestamps which are converted into a local time.

    Just for the record I simplified down the query A LOT.
    BEGIN
     MERGE into SLA_TIMES SLA
     USING (SELECT c001,
    c002,
    c003,
    c004,
    c005,
    c006,
    c007,
    c008,
    c009,
    c010,
    c011,
    c012,
    c013,
    c014,
    c015,
    c016,
    c017,
    c018,
    c019,
    c020,
    c021,
    c022,
    c023,
    c024,
    c025,
    c026,
    c027,
    c028,
    c029,
    c030
    from apex_collections) SS
    
    ON (SLA.NODE_NAME=SS.c001)
    WHEN NOT MATCHED THEN
    SELECT 1 from DUAL
    WHEN MATCHED THEN
    SELECT 2 from DUAL
    ;    
    
    END;
    And still end up with
    1 error has occurred
    ORA-06550: line 37, column 24: PL/SQL: ORA-00905: missing keyword ORA-06550: line 3, column 2: PL/SQL: SQL Statement ignored
    Edited by: bostonmacosx on Jan 30, 2013 2:01 PM

    Edited by: bostonmacosx on Jan 30, 2013 2:01 PM
  • 5. Re: First Merge Attempt Failling -
    sb92075 Guru
    Currently Being Moderated
    ON (SLA.NODE_NAME=SS.c001)
    below instead?
    JOIN ON (SLA.NODE_NAME=SS.c001)
  • 6. Re: First Merge Attempt Failling -
    bostonmacosx Newbie
    Currently Being Moderated
    That isn't the merge syntax.

    FROM ORACLE:
    MERGE INTO bonuses D
       USING (SELECT employee_id, salary, department_id FROM employees
       WHERE department_id = 80) S
       ON (D.employee_id = S.employee_id)
       WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
         DELETE WHERE (S.salary > 8000)
       WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
         VALUES (S.employee_id, S.salary*.01)
         WHERE (S.salary <= 8000);
  • 7. Re: First Merge Attempt Failling -
    riedelme Expert
    Currently Being Moderated
    Oracle will gleefully lie about missing parens - often the cause will be something else missing or out of place, like a comma :(. Get the Merge working in your favorte database connection tool (sql*plus, sql*developer, toad, etc) before using it in Apex. Carefully format the SQL to make sure you're not missing a ')' after all

    'P_COLLECTION_VAR' does not look like a "proper" Apex variable name, which are usually prefixed with a number to indicate the page name so developers (me) can tell where they are defined. Is 'P_COLLECTION_VAR' the proper spelling?

    I don't envy you having to work with a large SQL in the unsersized Apex edit window but have no solution for that problem short of using an externa procedure to do the work (which has its own inconveniences) :(

    Good luck

    Edited by: riedelme on Jan 30, 2013 11:17 AM
  • 8. Re: First Merge Attempt Failling -
    SomeoneElse Guru
    Currently Being Moderated
    ON (SLTM.NODE_NAME=mycol.c001 and SLTM.TEST_NAME=mycol.002)
    -------------------------------------------------------^^^
    You probably wanted mycol.c002
  • 9. Re: First Merge Attempt Failling -
    bostonmacosx Newbie
    Currently Being Moderated
    SOLUTION:

    Another lovely SQL thing that I didn't know about but now do.

    Any COLUMN name that starts numerically has to be quoted in the SQL.

    Too me forever to find that simple piece of information.

    So in the future I hope someone can find this thread.

    YOU MUST QUOTE COLUMN NAMES WHICH BEGIN NUMERICALLY.
  • 10. Re: First Merge Attempt Failling -
    bostonmacosx Newbie
    Currently Being Moderated
    See the last thread to see the solution.

    You must use quotes when referencing column names that begin with a number.
  • 11. Re: First Merge Attempt Failling -
    SomeoneElse Guru
    Currently Being Moderated
    YOU MUST QUOTE COLUMN NAMES WHICH BEGIN NUMERICALLY.
    You must use double quotation marks for any name that doesn't follow normal object naming rules.

    A column named "002"?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points