3 Replies Latest reply on Nov 10, 2006 9:55 AM by 545131

    dynamic sql - built

    545080
      i have a table with say - rule_component

      which has two columns

      1.db_table
      2.from_clause

      The two columns contains tables for example

      db_table
      ------------
      emp
      dept
      bonus
      emp
      def

      from_clause
      ----------------

      emp,dept,bonus,dept,emp,abc


      My requirement

      i want to find the duplicate tables in both the from_clause and db_table column
      and populate in my from clause which are unique to both the columns


      say example

      select ......

      from emp,dept,bonus,abc,def
        • 1. Re: dynamic sql - built
          523648
          use where clause
          • 2. Re: dynamic sql - built
            castorp
            i want to find the duplicate tables in both the
            from_clause and db_table column
            and populate in my from clause which are unique to
            both the columns
            Check this:

            http://forums.oracle.com/forums/search.jspa?threadID=&q=find+duplicates&objID=c84&dateRange=last90days&userID=&numResults=25
            • 3. Re: dynamic sql - built
              545131
              SQL> descr sswms_rule_components;
              Name Null? Type
              ----------------------------------------- -------- ---------------------
              RULE_COMPONENT_ID NOT NULL NUMBER
              RULE_COMPONENT_CODE NOT NULL VARCHAR2(30)
              RULE_COMPONENT_NAME NOT NULL VARCHAR2(100)
              ENABLED_FLAG NOT NULL VARCHAR2(1)
              DB_TABLE VARCHAR2 (100)
              DB_COLUMN VARCHAR2 (100)
              DB_FUNCTION VARCHAR2(100)
              WHERE_CLAUSE VARCHAR2 (2000)
              FROM_CLAUSE VARCHAR2 (2000)


              The table contains the following data


              db_table
              --------------------------------
              sswms_shipment_lines
              wsh_carrier_ship_method --- the data inside the db_table column is a table


              db_column
              --------------------------------
              Ship to
              Carrier_id


              Where_clause
              -----------------------------
              Oe_order_headers_all.header_id = sswms_shipment_lines.order_header_id



              From_clause

              -------------------------

              Oe_order_headers_all, sswms_shipment_lines -- the data inside the From_clause is a table


              Now my requirement is to build a dynamic sql in forms 6i.when I click the build sql it should update the sql

              So --- I have to write a procedure --

              Select db_table || . || db_column || ‘’ || group_key

              ---group_key is an alias

              From db_table, From_clause

              --- Here the logic should be

              1. I should remove the commas from the “FROM_CLAUSE” and check for duplicate values
              2. i should check for duplicate values for the “DB_TABLE “ column
              3. I should compare both the FROM_CLAUSE and DB_TABLE for DUPLICATE VALUES
              4. After doing this I should add the result to the “FROM” in the select statement



              where where_clause


              hope this is clear.

              regards

              sudharshan