12 Replies Latest reply on Jul 5, 2020 1:27 PM by Sai Amarnadh

    Oracle plsql function for delta in Values

    Sai Amarnadh

      Hello All,

       

      I have a small requirement.

       

      I have a table with 6 different values in a particular column. I need to return the values ONLY if the difference between any 2 values is more than 20.

       

      Any idea if there is any standard function, instead of writing custom plsql function to do the same?

        • 1. Re: Oracle plsql function for delta in Values
          L. Fernigrini

          Yes, you can calculate the max difference and compare against it. The WITH clause at the beginning just emulates your table, since you did not posted any sample data. I just included two examples with less values:

           

           

          WITH vData AS(

              SELECT 1 AS ID, 10 AS Value FROM DUAL

              UNION ALL

              SELECT 2 AS ID, 20 AS Value FROM DUAL

              UNION ALL

              SELECT 3 AS ID, 30 AS Value FROM DUAL

              UNION ALL

              SELECT 4 AS ID, 40 AS Value FROM DUAL

          )

          SELECT ID, Value

          FROM

          (

               SELECT d.*, MAX(Value) OVER() -  MIN(Value) OVER() AS Diff

               FROM vData d

          ) x

          WHERE x.Diff > 20;

          --

          WITH vData AS(

              SELECT 1 AS ID, 10 AS Value FROM DUAL

              UNION ALL

              SELECT 2 AS ID, 20 AS Value FROM DUAL

              UNION ALL

              SELECT 3 AS ID, 30 AS Value FROM DUAL

          )

          SELECT ID, Value

          FROM

          (

               SELECT d.*, MAX(Value) OVER() -  MIN(Value) OVER() AS Diff

               FROM vData d

          ) x

          WHERE x.Diff > 20;

          --

           

           

          Results:

          • 2. Re: Oracle plsql function for delta in Values
            L. Fernigrini

            If you want to try something with 6 rows, just use this 2 with clauses to emulate 2 scenarios:

             

            --

            WITH vData AS(

                SELECT 1 AS ID, 10 AS Value FROM DUAL

                UNION ALL

                SELECT 2 AS ID, 20 AS Value FROM DUAL

                UNION ALL

                SELECT 3 AS ID, 30 AS Value FROM DUAL

                UNION ALL

                SELECT 4 AS ID, 40 AS Value FROM DUAL

                UNION ALL

                SELECT 5 AS ID, 50 AS Value FROM DUAL

                UNION ALL

                SELECT 6 AS ID, 60 AS Value FROM DUAL

            )

             

             

             

             

            --

            WITH vData AS(

                SELECT 1 AS ID, 1 AS Value FROM DUAL

                UNION ALL

                SELECT 2 AS ID, 2 AS Value FROM DUAL

                UNION ALL

                SELECT 3 AS ID, 3 AS Value FROM DUAL

                UNION ALL

                SELECT 4 AS ID, 4 AS Value FROM DUAL

                UNION ALL

                SELECT 5 AS ID, 5 AS Value FROM DUAL

                UNION ALL

                SELECT 6 AS ID, 6 AS Value FROM DUAL

            )

             

             

             

            If want, you can also return the max difference if it is useful:

             

             

            --

            WITH vData AS(

               SELECT 1 AS ID, 10 AS Value FROM DUAL

                UNION ALL

                SELECT 2 AS ID, 20 AS Value FROM DUAL

                UNION ALL

                SELECT 3 AS ID, 30 AS Value FROM DUAL

                UNION ALL

                SELECT 4 AS ID, 40 AS Value FROM DUAL

                UNION ALL

                SELECT 5 AS ID, 50 AS Value FROM DUAL

                UNION ALL

                SELECT 6 AS ID, 60 AS Value FROM DUAL

            )

            SELECT *

            FROM

            (

            SELECT d.*, MAX(Value) OVER() -  MIN(Value) OVER() AS Diff

            FROM vData d

            ) x

            WHERE x.Diff > 20;

             

            • 3. Re: Oracle plsql function for delta in Values
              Sai Amarnadh

              Seems exciting. Will try and let you know. Thanks inadvance

              • 4. Re: Oracle plsql function for delta in Values
                L. Fernigrini

                You are welcome! Take a try, and let us know.

                 

                Whenever you have a question, please follow this guidelines to get help quicker:

                Re: 2. How do I ask a question on the forums?

                 

                 

                And it would also help if you change your display name to something meaningful rather than a number, making simpler for everyone to followup a conversation:

                Update Your Community Display Name and Avatar!

                • 5. Re: Oracle plsql function for delta in Values
                  mathguy

                  4278072 wrote:

                   

                  Hello All,

                   

                  I have a small requirement.

                   

                  I have a table with 6 different values in a particular column. I need to return the values ONLY if the difference between any 2 values is more than 20.

                   

                  Any idea if there is any standard function, instead of writing custom plsql function to do the same?

                   

                   

                  When I first read your question, I didn't understand it. More precisely: I found that it is ambiguous; it is not clear what you mean by "I need to return the values ONLY if the difference between any 2 values is more than 20." This can be interpreted in several ways. L. Fernigrini chose one interpretation. Is it exactly what you intended?

                   

                  To clarify:

                   

                  First, what do you mean by "6 different values in a particular column"? Does your table have exactly six rows, and you are considering the six values (from those rows) in a particular column? Other possible interpretation: the column is of object type; each value is a nested table or an array of six values. (That is: the table may have one row, three rows, six rows, or 100,000 rows. IN EACH ROW, that "particular column" stores 6 different values.)

                   

                  Second, and more importantly - suppose the table has six rows (as Fernigrini assumed). Do you mean that you need a query that will either return ALL the rows or NO ROWS, depending on whether there is at least one pair of values (in the "particular column") with a difference of more than 20? Other possible interpretation (the first that came to my mind - a more interesting requirement, with more possible applications): order the values ascending, for example. The output should always include the first row (by this order), and additional rows only if their difference from the previously selected row is greater than 20 (using the values in the "particular column"). So, for example, if the values were 10, 20, 30, 40, 50, 60, then the output would include 10 (always include the first one), skip 20, skip 30 (the difference is 20, which is not GREATER THAN 20), INCLUDE 40, then exclude 50 and exclude 60.

                   

                  This has nothing to do with code, and not even with how to solve the problem even without computers; it is just a clarification of the problem you are asking for help on. If Fernigrini's interpretation is what you had in mind, then you already have the solution in his post.

                  • 6. Re: Oracle plsql function for delta in Values
                    Sai Amarnadh

                    I am new to this group. forgive me for now

                     

                    changed my display name. Thanks for the guidelines

                    • 7. Re: Oracle plsql function for delta in Values
                      Sai Amarnadh

                      @mathguy/Fernigrini

                       

                      Apologies for not able to explain the requirement i clear.

                       

                      I have table : XXORO_TEMP_SES with one column name as Value with below values

                       

                      Table_Values.PNG

                       

                      Requirement : I need to get the value of set of 2, if the difference between 2 values is greater than 20.

                      ex::

                       

                      1. 10 and 50

                      2.  10 and 90

                      3. 20 and 50

                      4. 20 and 90

                      5. 50 and 90

                      6.  50 and 15

                      7. 50 and 25 (as difference is > 20)

                      8. 90 and 15

                      9. 90 and 25

                       

                      I have tried with below Query But it is returning error as "SQL statement doesn't return rows" Perharps due to version

                       

                       

                      WITH xxoro_tmp_ses AS

                           (SELECT 1 AS ID, 10 AS VALUE

                              FROM DUAL

                            UNION ALL

                            SELECT 2 AS ID, 20 AS VALUE

                              FROM DUAL

                            UNION ALL

                            SELECT 3 AS ID, 30 AS VALUE

                              FROM DUAL

                            UNION ALL

                            SELECT 4 AS ID, 40 AS VALUE

                              FROM DUAL

                            UNION ALL

                            SELECT 5 AS ID, 50 AS VALUE

                              FROM DUAL

                            UNION ALL

                            SELECT 6 AS ID, 60 AS VALUE

                              FROM DUAL)

                      SELECT VALUE

                        FROM (SELECT d.*, MAX (VALUE) OVER () - MIN (VALUE) OVER () AS diff

                                FROM xxoro_tmp_ses d) x

                      WHERE x.diff > 20;

                       

                      My DB version:

                      Version.PNG

                      • 8. Re: Oracle plsql function for delta in Values
                        mathguy

                        Sai Amarnadh wrote:

                         

                        I have tried with below Query But it is returning error as "SQL statement doesn't return rows" Perharps due to version

                         

                        Are you using the query in PL/SQL, and not as a stand-alone SQL statement? In SQL, a query that returns no rows is a perfectly valid query; the fact that it doesn't return rows is not an error.

                         

                        The query should work in 10.2 - the WITH clause was available then, as well as MIN() and MAX() analytic function. What is odd is that it doesn't return any rows; it should return all the rows.

                         

                        - - - - - - - -

                         

                        In any case: that solution only works for Fernigrini's interpretation of your problem - which, as it turns out, is NOT the one you needed. So, it doesn't make much sense to try to understand why it doesn't work on your system. It makes more sense to solve the problem you do actually need to solve.

                         

                        The problem is trivial, really. You need to return PAIRS of values:

                         

                        Requirement : I need to get the value of set of 2, if the difference between 2 values is greater than 20.

                        ex::

                         

                        1. 10 and 50

                        2.  10 and 90

                        3. 20 and 50

                        4. 20 and 90

                        5. 50 and 90

                        6.  50 and 15

                        7. 50 and 25 (as difference is > 20)

                        8. 90 and 15

                        9. 90 and 25

                         

                        So - join the table to itself, on the condition that the difference be > 20. You don't need any function for this:

                         

                        with

                          xxoro_tmp_ses as (

                            select 10 as value from dual union all

                            select 20 as value from dual union all

                            select 50 as value from dual union all

                            select 90 as value from dual union all

                            select 15 as value from dual union all

                            select 25 as value from dual

                          )

                        select t1.value as value_1, t2.value as value_2

                        from   xxoro_tmp_ses t1 inner join xxoro_tmp_ses t2

                                                on t1.value - t2.value > 20

                        ;

                         

                           VALUE_1    VALUE_2

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

                                50         10

                                50         20

                                50         15

                                50         25

                                90         10

                                90         20

                                90         50

                                90         15

                                90         25

                        • 9. Re: Oracle plsql function for delta in Values
                          L. Fernigrini

                          No need to apology we are all here to learn! Thanks for changing the name and posting more details!

                          • 10. Re: Oracle plsql function for delta in Values
                            L. Fernigrini

                            I believe you want something like this:

                             

                             

                            WITH vData AS(

                                SELECT 1 AS ID, 10 AS Value FROM DUAL

                                UNION ALL

                                SELECT 2 AS ID, 20 AS Value FROM DUAL

                                UNION ALL

                                SELECT 3 AS ID, 50 AS Value FROM DUAL

                                UNION ALL

                                SELECT 4 AS ID, 90 AS Value FROM DUAL

                                UNION ALL

                                SELECT 5 AS ID, 15 AS Value FROM DUAL

                                UNION ALL

                                SELECT 6 AS ID, 25 AS Value FROM DUAL

                            )

                            SELECT ID, Value

                            FROM vData d

                            WHERE EXISTS (SELECT 1 FROM vData X WHERE ABS(x.Value - d.Value)  > 20);

                             

                             

                             

                             

                            WITH vData AS(

                                SELECT 1 AS ID, 1 AS Value FROM DUAL

                                UNION ALL

                                SELECT 2 AS ID, 2 AS Value FROM DUAL

                                UNION ALL

                                SELECT 3 AS ID, 5 AS Value FROM DUAL

                                UNION ALL

                                SELECT 4 AS ID, 9 AS Value FROM DUAL

                                UNION ALL

                                SELECT 5 AS ID, 10 AS Value FROM DUAL

                                UNION ALL

                                SELECT 6 AS ID, 22 AS Value FROM DUAL

                            )

                            SELECT ID, Value

                            FROM vData d

                            WHERE EXISTS (SELECT 1 FROM vData X WHERE ABS(x.Value - d.Value)  > 20);

                             

                             

                            • 11. Re: Oracle plsql function for delta in Values
                              L. Fernigrini

                              Or maybe:

                               

                               

                              WITH vData AS(

                                  SELECT 1 AS ID, 1 AS Value FROM DUAL

                                  UNION ALL

                                  SELECT 2 AS ID, 2 AS Value FROM DUAL

                                  UNION ALL

                                  SELECT 3 AS ID, 5 AS Value FROM DUAL

                                  UNION ALL

                                  SELECT 4 AS ID, 9 AS Value FROM DUAL

                                  UNION ALL

                                  SELECT 5 AS ID, 10 AS Value FROM DUAL

                                  UNION ALL

                                  SELECT 6 AS ID, 22 AS Value FROM DUAL

                              )

                              SELECT d.ID, d.Value, x.ID, x.Value, ABS(x.Value - d.Value) AS DIFF

                              FROM vData d

                                  JOIN vData X ON ABS(x.Value - d.Value)  > 20;

                               

                              • 12. Re: Oracle plsql function for delta in Values
                                Sai Amarnadh

                                Thanks Mathguy and Fernigrini, query is working perfectly.. Sorry for delay to confirm the same.