7 Replies Latest reply on Apr 7, 2010 1:26 PM by 657011

    SQL Query

    657011
      Hi,

      Below is table content:
       
      KEY_V                    VALUE_V           JSFUNCTION_NAME_V
                
      MaxQuantityPerOrder     2000000             doNumberValidation(#MaxVoucherPerBox#,10000000)
      MaxVoucherPerBox     9000             doNumberValidation(#VouchersPerBatch#,#MaxQuantityPerOrder#)
      VouchersPerBatch     3000             doNumberValidation(1,#MaxVoucherPerBox#)
       
      I want to dynamically substitute #key# in JSFUNCTION_NAME_V column with key value.
      Below is expected result:
       
      KEY_V                    VALUE_V           JSFUNCTION_NAME_V
                
      MaxQuantityPerOrder     2000000             doNumberValidation(9000,10000000)
      MaxVoucherPerBox     9000             doNumberValidation(3000,2000000)
      VouchersPerBatch     3000             doNumberValidation(1,9000)
       
      Appreciate any help.
        • 1. Re: SQL Query
          Frank Kulash
          Hi,

          Welcome to the forum!

          Whenever you have a question, please post CREATE TABLE and INSERT statements for the sample data , so people can actually test their ideas. For example:
          CREATE TABLE     table_x
          (      key_v           VARCHAR2 (30)
          ,      value_v           VARCHAR2 (30)
          ,      jsfunction_name_v varchar2 (80)
          );
          
          INSERT INTO table_x (key_v, value_v,    jsfunction_name_v) VALUES          
          ('MaxQuantityPerOrder',         '2000000',     'doNumberValidation(#MaxVoucherPerBox#,10000000)');
          INSERT INTO table_x (key_v, value_v,    jsfunction_name_v) VALUES
          ('MaxVoucherPerBox',         '9000',     'doNumberValidation(#VouchersPerBatch#,#MaxQuantityPerOrder#)');
          INSERT INTO table_x (key_v, value_v,    jsfunction_name_v) VALUES
          ('VouchersPerBatch',         '3000',     'doNumberValidation(1,#MaxVoucherPerBox#)');
          It looks like the jsfunction_name_v has nothing to do with the other columns on the same row. That is, 'MaxQuantityPerOrder' (from the 1st row) should be replaced wuith '2000000' (from the frist row) in all jsfunction_name_vs, not just the one on the 1st row. In that case, you should really have two separate tables: one for key_v and value_v, and the other for jsfunction_name_v. That way, you could have any number of key_v-value_v pairs apply to any number of jsfunction_name_vs. The solution below uses the single table you posted, but does a CROSS JOIN to make it behave like two tables.

          I'm pretty sure that MODEL is the best way to get the results you want in pure SQL.
          I'm also sure there's a more elegant way to use MODEL, but this works:
          WITH     got_k_num         AS
          (
               SELECT  k.key_v, k.value_v, j.jsfunction_name_v
               ,     DENSE_RANK () OVER (ORDER BY k.key_v)     AS k_num
               ,     COUNT (DISTINCT k.key_v) OVER ()     AS max_k_num
               FROM           table_x     k
               CROSS JOIN    table_x     j     
          )
          ,     got_txt          AS
          (
               SELECT  txt
               ,     k_num
               ,     max_k_num
               FROM     got_k_num
               MODEL     PARTITION BY  (jsfunction_name_v)
                    DIMENSION BY  (k_num)
                    MEASURES      ( key_v
                                     , value_v
                                     , jsfunction_name_v     txt
                               , max_k_num
                          )
                    RULES  SEQUENTIAL ORDER
                    (
                        txt [any] = REPLACE ( NVL ( txt [CV() - 1]
                                                      , txt [CV()]
                                           )
                                        , '#' || key_v [CV()] || '#'
                                       , value_v [CV()]
                                       )
                    )
          )
          SELECT     txt
          FROM     got_txt
          WHERE     k_num     = max_k_num
          ;
          This assumes that key_v and jsfunction_name_v are unique. If not, the solution is slightly more complicated.
          • 2. Re: SQL Query
            657011
            Thanks Frank,
            But i want "KEY_V" along with "JSFUNCTION_NAME_V".
            fyi KEY_V and JSFUNCTION_NAME_V are unique.
            • 3. Re: SQL Query
              657011
              Key_V column is not present in output
              • 4. Re: SQL Query
                Frank Kulash
                Hi,
                user10194055 wrote:
                Key_V column is not present in output
                Sorry, I missed that. You did include key_v and value_v in your desired output.

                Here's one way to get them:
                WITH     got_k_num         AS
                (
                     SELECT  k.key_v                         AS replace_key
                     ,     k.value_v                    AS replace_value 
                     ,     j.key_v                         AS display_key
                     ,     j.value_v                    AS display_value
                     ,     j.jsfunction_name_v
                     ,     DENSE_RANK () OVER (ORDER BY k.key_v)     AS k_num
                     ,     COUNT (DISTINCT k.key_v) OVER ()     AS max_k_num
                     FROM           table_x     k
                     CROSS JOIN    table_x     j     
                )
                ,     got_txt          AS
                (
                     SELECT  display_key
                     ,     display_value
                     ,     txt
                     ,     k_num
                     ,     max_k_num
                     FROM     got_k_num
                     MODEL     PARTITION BY  (jsfunction_name_v)
                          DIMENSION BY  (k_num)
                          MEASURES      ( replace_key
                                           , replace_value
                                           , display_key
                                           , display_value
                                           , jsfunction_name_v     txt
                                     , max_k_num
                                )
                          RULES  SEQUENTIAL ORDER
                          (
                              txt [any] = REPLACE ( NVL ( txt [CV() - 1]
                                                            , txt [CV()]
                                                 )
                                              , '#' || replace_key [CV()] || '#'
                                             , replace_value [CV()]
                                             )
                          )
                )
                SELECT     display_key     AS key_v
                ,     display_value     AS value_v
                ,     txt
                FROM     got_txt
                WHERE     k_num     = max_k_num
                ;
                • 5. Re: SQL Query
                  657011
                  Thanks Frank that was perfect.
                  From my understanding point of view can you please explain the logic....
                  Actually i am bit confused with "Rule Clause".
                  • 6. Re: SQL Query
                    Frank Kulash
                    Hi,
                    Abdul wrote:
                    Thanks Frank that was perfect.
                    From my understanding point of view can you please explain the logic....
                    Actually i am bit confused with "Rule Clause".
                    In this problem, we need to do several REPLACE operations to the same string in serial, that is, one after another, each operating on the results of the last. That's not usually how SQL works. Normally, SQL operates in parallel, where we have no control over the order in which things get done. MODEL is a way to get around that. With MODEL, we can specify that a certain REPLACE will be done on the raw data, another REPLACE done on the results of the 2st, and 3rd REPLACE done on the results of the 2nd, and so on. That's what the RULES section does.
                    ...          RULES  SEQUENTIAL ORDER
                              (
                                  txt [any] = REPLACE ( NVL ( txt [CV() - 1]
                                                                , txt [CV()]
                                                     )
                                                  , '#' || replace_key [CV()] || '#'
                                                 , replace_value [CV()]
                                                 )
                              )
                    All of the columns in the MODEL MEASURES clause (in particular, txt, replace_key and replace_value) act like arrays. In a procedureal language, you might do those replace operations in a loop, like this:
                    FOR i IN i .. max_k_num
                    LOOP
                         IF  i = 1
                         THEN
                              txt (i) := REPLACE ( txt (i)
                                                  , '#' || replace_key (i) || '#'
                                           , replace_value (i)
                                           );
                         ELSE
                              txt (i) := REPLACE ( txt (i - 1)
                                                  , '#' || replace_key (i) || '#'
                                           , replace_value (i)
                                           );
                         END IF;
                    END LOOP;
                    Notice how the two branches of the IF statement are almost identical; the only difference is that the subscript of the 1st argument to REPLACE is (i) in the first branch, but (i - 1) in the second branch. The MODEL clause I posted is behaving very much like the loop above, only instead of having two nearly identical "txt :=" statements, I used only one, with a conditional rxpression (NVL) to use either (i) or (i - 1) as the subscript.

                    In this case, we're only care about the last entry in the txt "array", the value after all the REPLACEments have been done. That's what the condition
                    WHERE     k_num     = max_k_num
                    in the main query does; only the last version is displayed.
                    • 7. Re: SQL Query
                      657011
                      Thanks Frank for detail explanation
                      :)