8 Replies Latest reply on Apr 25, 2019 7:02 PM by Paulzip

    Based on input sum positive or negative value

    3722045

      Hi Experts,

       

       

      The requirement is to write a procedure to accept  two variables one is v_input_number and the other is v_input_cid.

      If I pass positive number for a particular cid the number should sum with negative value.

      If I pass negative number for a particular cid the number should sum with positive value

       

       

      The data in the table as below.

       

       

      cid   val1   val2

      A       -5   10

      B       15   -7

      C       -8    4

      D       -20   30

       

       

      I have tried the below logic and stuck up. It's not completed.

       

       

      CREATE OR REPLACE PROCEDURE p1 (v_input_number NUMBER,v_input_cid NUMBER)

      IS

      v_result NUMBER;

      BEGIN

      SELECT  CASE WHEN v_input_number<0 THEN v_input_number

      WHEN v_input_number>=0 THEN v_input_number

      END as result INTO v_result

      FROM test_table WHERE cid=v_input_cid;

      DBMS_OUTPUT.PUT_LINE(v_result);

      END;

       

       

      Expected Result :

       

      I/P v_input_number=-2  v_input_cid=A  calculation  -2+10 O/P 8

                                            calculation 

      I/P v_input_number=5  v_input_cid=B   calculation   5-7  O/p -2

                                            calculation 

      I/P v_input_number=10  v_input_cid=3  calculation  10-8  O/P 2

                                             

      Please help me with the logic.

       

       

      Thanks in advance.

        • 1. Re: Based on input sum positive or negative value
          Gary_A

          I don't see how you get any of those outputs from your data. You'll need a better explanation or I'm just too dense to see it.

           

          Never mind...mostly. I looked more closely and was able to follow, however there is no CID 3.

          • 2. Re: Based on input sum positive or negative value
            Frank Kulash

            Hi,

            3722045 wrote:

             

            Hi Experts,

             

             

            The requirement is to write a procedure to accept two variables one is v_input_number and the other is v_input_cid.

            If I pass positive number for a particular cid the number should sum with negative value.

            If I pass negative number for a particular cid the number should sum with positive value

             

             

            The data in the table as below.

             

             

            cid val1 val2

            A -5 10

            B 15 -7

            C -8 4

            D -20 30

             

             

            I have tried the below logic and stuck up. It's not completed.

             

             

            CREATE OR REPLACE PROCEDURE p1 (v_input_number NUMBER,v_input_cid NUMBER)

            IS

            v_result NUMBER;

            BEGIN

            SELECT CASE WHEN v_input_number<0 THEN v_input_number

            WHEN v_input_number>=0 THEN v_input_number

            END as result INTO v_result

            FROM test_table WHERE cid=v_input_cid;

            DBMS_OUTPUT.PUT_LINE(v_result);

            END;

             

             

            Expected Result :

             

            I/P v_input_number=-2 v_input_cid=A calculation -2+10 O/P 8

            calculation

            I/P v_input_number=5 v_input_cid=B calculation 5-7 O/p -2

            calculation

            I/P v_input_number=10 v_input_cid=3 calculation 10-8 O/P 2

             

            Please help me with the logic.

             

             

            Thanks in advance.

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

            Always say which version of Oracle you're using (for example, 12.2.0.1.0).
            See the Forum FAQ: Re: 2. How do I ask a question on the forums?

             

            Dbms_output is a handy debggng tool, but it's nothing you want to use for production results.  You should make this a function, or perhaps add an OUT argument to return the results.

            • 3. Re: Based on input sum positive or negative value
              Frank Kulash

              Hi,

               

              Perhaps you want a query like this:

              SELECT  v_input_number

                    + CASE

                          WHEN  SIGN (v_input_number) <> SIGN (val1)

                          THEN  val1

                          ELSE  0

                      END

                    + CASE

                          WHEN  SIGN (v_input_number) <> SIGN (val2)

                          THEN  val2

                          ELSE  0

                      END

              INTO    v_result

              FROM    test_table

              WHERE   cid  = v_input_cid;

              Of course, without test_table, I can't actually run it.

               

              Is cid unique?  What do you want to do when v_input_number is 0 (which is neither positive nor negative)?  What if you pass v_input_cid that isn't in the table?  Can both val1 and val2 be positive (or both negative) on the same row?  How do you want to deal with NULLs?

              When you post the sample data, include examples of any special cases like these that you need to handle.

              • 4. Re: Based on input sum positive or negative value
                CarlosDLG

                3722045 wrote:

                 

                Hi Experts,

                 

                 

                The requirement is to write a procedure to accept two variables one is v_input_number and the other is v_input_cid.

                If I pass positive number for a particular cid the number should sum with negative value.

                If I pass negative number for a particular cid the number should sum with positive value

                 

                 

                The data in the table as below.

                 

                 

                cid val1 val2

                A -5 10

                B 15 -7

                C -8 4

                D -20 30

                 

                 

                I have tried the below logic and stuck up. It's not completed.

                 

                 

                CREATE OR REPLACE PROCEDURE p1 (v_input_number NUMBER,v_input_cid NUMBER)

                IS

                v_result NUMBER;

                BEGIN

                SELECT CASE WHEN v_input_number<0 THEN v_input_number

                WHEN v_input_number>=0 THEN v_input_number

                END as result INTO v_result

                FROM test_table WHERE cid=v_input_cid;

                DBMS_OUTPUT.PUT_LINE(v_result);

                END;

                 

                 

                Expected Result :

                 

                I/P v_input_number=-2 v_input_cid=A calculation -2+10 O/P 8

                calculation

                I/P v_input_number=5 v_input_cid=B calculation 5-7 O/p -2

                calculation

                I/P v_input_number=10 v_input_cid=3 calculation 10-8 O/P 2

                 

                Please help me with the logic.

                 

                 

                Thanks in advance.

                You are not performing any sum in your query.

                 

                Why is that?

                 

                If you have a specific doubt, it would be better to say so.

                1 person found this helpful
                • 5. Re: Based on input sum positive or negative value
                  Paulzip

                  Maybe something like this...

                   

                  create or replace function MySum(v_input_number number, v_input_cid test_table.cid%type) return number is

                    v_result number;

                  begin

                    select v_input_number + case when sign(val1) <> sign(v_input_number) then val1 else val2 end

                    into   v_result

                    from   test_table

                    where cid = :cid;

                    return v_result;

                  end;

                   

                  Example usage...

                  select MySum(-2, 'A') from dual

                   

                  Caveat

                  Assuming Val1 and Val2 are always a non zero value, if you pass zero as input number, it will be considered to be a different sign than val1 so will use val1.

                  • 6. Re: Based on input sum positive or negative value
                    mathguy

                    Can the input number be NULL? Can it be 0? If the answer to any of these question is YES, how should that be handled?

                     

                    Can the value in column VAL1 be NULL? Can it be 0? If so, how should that be handled?  Same question for VAL2.

                     

                    Can both values (VAL1 and VAL2) be positive? In your sample data, one value is positive and the other is negative, in each row. Can that be assumed to be true in all cases? If not - that is, if VAL1 and VAL2 are both positive (for example), what is the desired handling when the input number is positive? What is the desired handling when it's negative?

                     

                    If none of the numbers involved can ever be NULL or 0, and if VAL1 and VAL2 always have opposite signs, you could do something like

                     

                    create or replace procedure p1(v_input_number number,v_input_cid char)

                    as

                      v_result number;

                    begin

                      select   v_input_number + case when v_input_number < 0 then greatest(val1, val2) else least(val1, val2) end

                        into   v_result

                        from   test_table

                        where  cid = v_input_cid;

                      dbms_output.put_line(v_result);

                    end p1;

                    /

                     

                    Note that in your attempt the input variable v_input_cid is NUMBER, but in your sample data the values are 'A', 'B', 'C' etc. It's OK to change your mind, but you must be consistent.

                    1 person found this helpful
                    • 7. Re: Based on input sum positive or negative value
                      3722045

                      Hi All,

                       

                      Thanks for your responses.

                       

                      Thanks.

                      • 8. Re: Based on input sum positive or negative value
                        Paulzip

                        3722045 wrote:

                         

                        Hi All,

                         

                        Thanks for your responses.

                         

                        Thanks.

                        How is this posting above the correct answer?  This doesn't help people who search for answers on the community.