11 Replies Latest reply: Jan 30, 2013 12:57 PM by SomeoneElse RSS

    First Merge Attempt Failling -

    bostonmacosx
      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
          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
            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
              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
                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
                  ON (SLA.NODE_NAME=SS.c001)
                  below instead?
                  JOIN ON (SLA.NODE_NAME=SS.c001)
                  • 6. Re: First Merge Attempt Failling -
                    bostonmacosx
                    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
                      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
                        ON (SLTM.NODE_NAME=mycol.c001 and SLTM.TEST_NAME=mycol.002)
                        -------------------------------------------------------^^^
                        You probably wanted mycol.c002
                        • 9. Re: First Merge Attempt Failling -
                          bostonmacosx
                          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
                            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
                              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"?