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

# Based on input sum positive or negative value

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

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

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

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

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

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

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

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.

• ###### 5. Re: Based on input sum positive or negative value

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

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.

• ###### 7. Re: Based on input sum positive or negative value

Hi All,

Thanks.

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

3722045 wrote:

Hi All,