1 2 3 Previous Next 37 Replies Latest reply: May 4, 2012 12:09 AM by user13047999 RSS

    PLSQL help

    user13047999
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

      I have a table like this

      CREATE TABLE "RULES_TABLE"
      (     "RULESET_ID" NUMBER,
           "QUEUE_ID" NUMBER,
           "ATTR_NAME" VARCHAR2(12 BYTE),
           "ATTR_VALUE" VARCHAR2(200 BYTE)
      );


      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (7,3,'PRODUCT','P1');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (7,3,'CUSTOMER','TEST CUSTOMER');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (7,3,'STATE','VA');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (7,3,'CUSTOMER','UNKNOWN');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (5,2,'CUSTOMER','UNKNOWN');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (5,2,'CUSTOMER','C1');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (5,2,'PRODUCT','P1');
      Insert into RULES_TABLE (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (5,2,'STATE','VA');
      commit;

      I want to insert below ruleset into above table. but before I insert, I need to check if there is already ruleset with same attribute names and values.
      if so, then reject else insert into rules_table

      rules_table_temp

      create table rules_table_temp as select * from rules_table where 1=2;

      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'PRODUCT','UNKNOWN');
      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'PRODUCT','P1');
      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'CUSTOMER','C1');
      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'CUSTOMER','TEST CUSTOMER');
      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'STATE','VA');
      Insert into RULES_TABLE_TEMP (RULESET_ID,QUEUE_ID,ATTR_NAME,ATTR_VALUE) values (10,4,'STATE','NJ');
      commit;


      for ex: for the above ruleset 10 , I have formed all possible combinations and checking as below

      ruleset_id queue_id product     customer state     Action
      10     4     P1     C1          VA ----> ruleset 5 is already available in ruleset_table with same attributes and values hence reject
      10     4     P1     UNKNOWN     VA ---->     ruleset 5 is already available with same attributes and values hence reject
      10     4     P1     C1          NJ ---->     this rule is not available hence insert it rules_table
      10     4     P1     UNKNOWN     NJ ---->     this rule is not available hence insert it rules_table
      10     4     UNKNOWN C1          VA ---->     ruleset 5 is already available with same attributes and values hence reject
      10     4     UNKNOWN UNKNOWN     VA ---->     this rule is not available hence insert it rules_table
      10     4     UNKNOWN C1          NJ ---->     this rule is not available hence insert it rules_table
      10     4     UNKNOWN UNKNOWN     NJ ---->     this rule is not available hence insert it rules_table

      since below record is not available in the ruleset_table.
      10     4     P1     C1          NJ
      I need to insert this record into rulese_table in the below format
      10 4 product P1
      10 4 customer C1
      10 4 state NJ

      The no of attributes are not fixed. I need to do it dynamically

      could you please suggest if it is doable

      Edited by: user13047999 on Apr 12, 2012 8:41 AM
        • 1. Re: PLSQL help
          908002
          why not creating uniqye constraint . unique index on the specific attribute and value
          • 2. Re: PLSQL help
            Stew Ashton
            This would be easier in 11G with PIVOT and UNPIVOT, but here goes:
            insert into rules_table
            WITH oldPRODS AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE PRODUCT FROM RULES_TABLE WHERE ATTR_NAME = 'PRODUCT'
            ), oldCUSTS AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE CUSTOMER FROM RULES_TABLE WHERE ATTR_NAME = 'CUSTOMER'
            ), oldSTATES AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE STATE FROM RULES_TABLE WHERE ATTR_NAME = 'STATE'
            ), newPRODS AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE PRODUCT FROM rules_table_temp WHERE ATTR_NAME = 'PRODUCT'
            ), newCUSTS AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE CUSTOMER FROM rules_table_temp WHERE ATTR_NAME = 'CUSTOMER'
            ), newSTATES AS (
              SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE STATE FROM RULES_TABLE_TEMP WHERE ATTR_NAME = 'STATE'
            ), addcombos as (
              SELECT p.ruleset_id, p.queue_id, product, customer, state FROM newPRODS P, newCUSTS C, newSTATES S
              WHERE (P.RULESET_ID, P.QUEUE_ID) = ((C.RULESET_ID, C.QUEUE_ID))
              AND (P.RULESET_ID, P.QUEUE_ID) = ((S.RULESET_ID, S.QUEUE_ID))
              and (product, customer, state) not in (
                SELECT product, customer, state FROM oldPRODS P, oldCUSTS C, oldSTATES S
                WHERE (P.RULESET_ID, P.QUEUE_ID) = ((C.RULESET_ID, C.QUEUE_ID))
                AND (P.RULESET_ID, P.QUEUE_ID) = ((S.RULESET_ID, S.QUEUE_ID))
              )
            )
            SELECT distinct RULESET_ID, QUEUE_ID,
            DECODE(RN,1,'PRODUCT',2,'CUSTOMER','STATE') ATTR_NAME,
            decode(rn,1,PRODUCT,2,CUSTOMER,STATE) attr_value
            FROM ADDCOMBOS,
            (select level rn from dual connect by level <=3);
            Edited by: Stew Ashton on Apr 12, 2012 11:03 PM

            P.S. I feel sorry for you having to deal with this data model.

            Edited by: Stew Ashton on Apr 12, 2012 11:04 PM

            P.P.S. I didn't do this in PL/SQL, sorry!
            • 3. Re: PLSQL help
              user13047999
              This is for ruleset validation. if we enter a new ruleset, it will check whether any ruleset is available with same attributes and values. if it is matching partially (some rules are avialable), then we need to insert mismatch rules
              • 4. Re: PLSQL help
                Stew Ashton
                So did you test my solution?
                • 5. Re: PLSQL help
                  user13047999
                  yes stew. you query is giving correct result. thank you.

                  I am not sure how to implement this in PL/SQL since the attributes are dynamic. could you please check the below query

                  select * from rules_table where (queue_id,ruleset_id) in (
                  select queue_id,ruleset_id from rules_table a
                  where 1=1
                  and exists (select 'X' from rules_table b where b.attr_name='CUSTOMER' and a.queue_id=b.queue_id and a.ruleset_id=b.ruleset_id and attr_value
                  in (select attr_value from rules_table_temp where attr_name='CUSTOMER'))
                  and exists (select 'X' from rules_table b where b.attr_name='STATE' and a.queue_id=b.queue_id and a.ruleset_id=b.ruleset_id and attr_value
                  in (select attr_value from rules_table_temp where attr_name='STATE'))
                  and exists (select 'X' from rules_table b where b.attr_name='PRODUCT' and a.queue_id=b.queue_id and a.ruleset_id=b.ruleset_id and attr_value
                  in (select attr_value from rules_table_temp where attr_name='PRODUCT'))
                  )

                  it will give available rulesets with given attribute names and values. If I provide the output of this query to user, tell them that some rules are already available.so modify ruleset accordingly. please let me know your thoughts.
                  • 6. Re: PLSQL help
                    Stew Ashton
                    Okay, I get it now. My first INSERT was not strict enough.

                    You want to tell the user what is wrong with the ruleset he/she intends to add. Here is my suggestion:
                    WITH oldPRODS AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE PRODUCT FROM RULES_TABLE WHERE ATTR_NAME = 'PRODUCT'
                      and attr_value in (select attr_value from rules_table_temp where ATTR_NAME = 'PRODUCT')
                    ), oldCUSTS AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE CUSTOMER FROM RULES_TABLE WHERE ATTR_NAME = 'CUSTOMER'
                      and attr_value in (select attr_value from rules_table_temp where ATTR_NAME = 'CUSTOMER')
                    ), oldSTATES AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE STATE FROM RULES_TABLE WHERE ATTR_NAME = 'STATE'
                      and attr_value in (select attr_value from rules_table_temp where ATTR_NAME = 'STATE')
                    ), newPRODS AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE PRODUCT FROM rules_table_temp WHERE ATTR_NAME = 'PRODUCT'
                    ), newCUSTS AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE CUSTOMER FROM rules_table_temp WHERE ATTR_NAME = 'CUSTOMER'
                    ), newSTATES AS (
                      SELECT RULESET_ID, QUEUE_ID, ATTR_VALUE STATE FROM RULES_TABLE_TEMP WHERE ATTR_NAME = 'STATE'
                    )
                    SELECT p.ruleset_id, p.queue_id, product, customer, state FROM newPRODS P, newCUSTS C, newSTATES S
                    WHERE (P.RULESET_ID, P.QUEUE_ID) = ((C.RULESET_ID, C.QUEUE_ID))
                    AND (P.RULESET_ID, P.QUEUE_ID) = ((S.RULESET_ID, S.QUEUE_ID))
                    and (product, customer, state) in (
                      SELECT product, customer, state FROM oldPRODS P, oldCUSTS C, oldSTATES S
                      WHERE (P.RULESET_ID, P.QUEUE_ID) = ((C.RULESET_ID, C.QUEUE_ID))
                      AND (P.RULESET_ID, P.QUEUE_ID) = ((S.RULESET_ID, S.QUEUE_ID))
                    );
                    
                    RULESET_ID QUEUE_ID PRODUCT      CUSTOMER       STATE
                    ---------- -------- ------------ -------------- -----
                            10        4 P1           TEST CUSTOMER  VA    
                            10        4 P1           C1             VA
                    The user can deduce that if he removes product 'P1' or he removes state 'VA' he will have no conflict; otherwise he will have to remove both customers.
                    • 7. Re: PLSQL help
                      user13047999
                      Thank you stew. this is the output that I wanted to display to the user so that user can modify accordingly.

                      I need to generate this query dynamically based on attributes provided. user will be entering rules from UI. the plan is insert all rules into temp table as give below and then compare with actual rules table and display matching records.
                      for ex, rules_temp table contains the following records
                      11 5 PRODUCT P2
                      11 5 CUSTOMER C2
                      11 5 CUSTOMER C1
                      11 5 STATE VA
                      11 5 EMAIL TEST@EMAIL.COM

                      I need to generate the query dynamically with the above attributes.

                      Please suggest the best way to do this
                      • 8. Re: PLSQL help
                        Stew Ashton
                        Okay, this can be done, but I need to know if I have understood the requirement.

                        1) In the case you just presented (ruleset 11, queue 5), there are 4 distinct attribute names. There are no existing rules with the 4 distinct attribute names, so there are no matches, right?

                        2) Now suppose it's the other way around: suppose there is already a rule with these 4 names, and someone wants to add a rule with only 3 out of the 4 names. Is this a "match" or not?

                        In other words, if the new ruleset is a strict subset of an existing ruleset, is that a "match" or not?

                        Also, I see a problem with the "temporary" table. If this is a true client/server application where each end user has his own connection to the database, then you can use a true GLOBAL TEMPORARY TABLE with no problem.

                        If this is an n-tier application with multiple users and generic connections to the database, then you need a permanent table and you need to determine which ruleset belongs to which user -- or the application can just keep the ruleset in a user context, you don't really need to store this data in any table at all!

                        For the moment I'm going to assume that a "match" means:
                        - the "old" ruleset has exactly the same attribute names as the "new" ruleset, neither more nor less.
                        - the name/value pairs match in the way I showed previously.

                        Please respond to make sure we are on the same wavelength.

                        Best regards, Stew

                        Edited by: Stew Ashton on Apr 14, 2012 10:23 AM

                        Edited by: Stew Ashton on Apr 14, 2012 11:17 AM
                        (changed definition of what a "match" means)
                        • 9. Re: PLSQL help
                          Stew Ashton
                          Okay, here's a whole new approach that will work with any number of name / value pairs, and that does not require dynamic SQL:
                          with NEW_RULESET AS (
                            SELECT ATTR_NAME, ATTR_VALUE,
                            count(distinct attr_name) over() tot_name_new,
                            POWER(2, -1+DENSE_RANK() OVER(ORDER BY ATTR_NAME)) NAME_RANK,
                            power(2, count(distinct attr_name) over()) - 1 sum_rank_new
                            FROM RULES_TABLE_TEMP
                          )
                          SELECT RULESET_ID, QUEUE_ID, ATTR_NAME, ATTR_VALUE
                          from (
                            SELECT O.*,
                            TOT_NAME_NEW,
                            count(distinct o.attr_name) over(partition by o.ruleset_id, o.queue_id) tot_name_old,
                            NAME_RANK,
                            SUM(distinct N.NAME_RANK) OVER(PARTITION BY O.RULESET_ID, O.QUEUE_ID) SUM_RANK_OLD,
                            sum_rank_new FROM RULES_TABLE O
                            LEFT JOIN NEW_RULESET N ON(O.ATTR_NAME, O.ATTR_VALUE) = ((N.ATTR_NAME, N.ATTR_VALUE))
                          ) 
                          WHERE TOT_NAME_NEW = TOT_NAME_OLD AND SUM_RANK_NEW = SUM_RANK_OLD
                          ORDER BY 3,4,1,2;
                          
                          RULESET_ID QUEUE_ID ATTR_NAME    ATTR_VALUE         
                          ---------- -------- ------------ --------------------
                                   5        2 CUSTOMER     C1                   
                                   7        3 CUSTOMER     TEST CUSTOMER        
                                   5        2 PRODUCT      P1                   
                                   7        3 PRODUCT      P1                   
                                   5        2 STATE        VA                   
                                   7        3 STATE        VA
                          TOT_NAME is the number of distinct attribute names in the set. The old and new sets must have the same number of distinct attributes.

                          NAME_RANK assigns a unique number from 1,2,4,8,16, etc. to each attribute name in the new ruleset.

                          SUM_RANK is the sum of NAME_RANK for the entire set, but only for those name / value pairs found in the new ruleset.
                          SUM_RANK_OLD is the sum of the distinct NAME_RANK values, in case there is more than one match for some attribute name.
                          The old and new sets must have the same SUM_RANK: this means we found at least one name / value match for each name in the new ruleset.

                          This solution determines equality of groups of records, without having to pivot rows to columns. Might come in handy some other time...

                          Best regards, Stew

                          Edited by: Stew Ashton on Apr 14, 2012 5:05 PM
                          (changed sum(name_rank... to sum(DISTINCT name-rank... This covers the case when there is more than one match for the same attribute name.)
                          • 10. Re: PLSQL help
                            user13047999
                            1) In the case you just presented (ruleset 11, queue 5), there are 4 distinct attribute names. There are no existing rules with the 4 distinct attribute names, so there are no matches, right?
                            yes
                            2) Now suppose it's the other way around: suppose there is already a rule with these 4 names, and someone wants to add a rule with only 3 out of the 4 names. Is this a "match" or not?
                            no match

                            PFor the moment I'm going to assume that a "match" means:
                            - the "old" ruleset has exactly the same attribute names as the "new" ruleset, neither more nor less.
                            - the name/value pairs match in the way I showed previously.

                            this is correct

                            I think only one user will be allowed to enter rules data from UI. as soon as user enters new rule set, we need to perform rule set validation and if there is any match with an existing rules then display those matching rules on screen

                            Edited by: user13047999 on Apr 14, 2012 12:02 PM
                            • 11. Re: PLSQL help
                              user13047999
                              Thanks stew. please give me some time to understand this logic. I will get back to you asap
                              • 12. Re: PLSQL help
                                930698
                                may be you should try to run form without the quotations...
                                • 13. Re: PLSQL help
                                  user13047999
                                  Stew, I understood your query and it is returning correct output. thank you very much.

                                  we thought of displaying output in the below format so that user can adjust new rule set accordingly.

                                  RULESET_ID QUEUE_ID PRODUCT CUSTOMER STATE
                                  ---------- -------- ------------ -------------- -----
                                  10 4 P1 TEST CUSTOMER VA
                                  10 4 P1 C1 VA

                                  could you please modify the query to display query output in this format.
                                  • 14. Re: PLSQL help
                                    Stew Ashton
                                    I don't understand why you want to output the NEW ruleset_id and queue_id. I would prefer to see the OLD ones.

                                    Suppose you
                                    insert into rules_table values(5,2,'STATE', 'NJ');
                                    insert into rules_table values(5,2,'PRODUCT', 'UNKNOWN');
                                    What do you want to see then?
                                    1 2 3 Previous Next