Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2069

Optimze add LIMIT clause to SELECT within a Virtual Procedure using Oracle

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Done
    • 7.4.1, 7.7
    • 8.1, 7.7.2
    • Query Engine
    • None
    • Hide

      Execute the following virtual procedure. Supporting information can be found in the referenced ticket. If consolidation of artifacts is needed, please let me know and I will attach.

      CREATE VIRTUAL PROCEDURE
      BEGIN
      DECLARE biginteger VARIABLES.t_pk;
      VARIABLES.t_pk = (SELECT <table>.<seq-gen>.<seq> FROM <table>.<seq-gen);
      SELECT VARIABLES.t_pk AS result;
      END

      Show
      Execute the following virtual procedure. Supporting information can be found in the referenced ticket. If consolidation of artifacts is needed, please let me know and I will attach. CREATE VIRTUAL PROCEDURE BEGIN DECLARE biginteger VARIABLES.t_pk; VARIABLES.t_pk = (SELECT <table>.<seq-gen>.<seq> FROM <table>.<seq-gen); SELECT VARIABLES.t_pk AS result; END

    Description

      LIMIT clause is added to Statement being optimized which fails the operation on Oracle

      OPTIMIZE:
      CREATE VIRTUAL PROCEDURE
      BEGIN
      DECLARE biginteger VARIABLES.t_pk;
      VARIABLES.t_pk = (SELECT <table>.seq_gen.cpe_seq FROM <table>.seq_gen);
      SELECT VARIABLES.t_pk AS result;
      END

      ####################################################
      PROCEDURE COMMAND: CREATE VIRTUAL PROCEDURE
      BEGIN
      DECLARE biginteger VARIABLES.t_pk;
      VARIABLES.t_pk = (SELECT <table>.seq_gen.cpe_seq FROM <table>.seq_gen LIMIT 2);
      SELECT VARIABLES.t_pk AS result;
      END

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            rhn-support-jolee Johnathon Lee
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: