
1. Re: Based on input sum positive or negative value
Gary_A Apr 23, 2019 5:43 PM (in response to 3722045)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 Apr 23, 2019 5:35 PM (in response to 3722045)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 57 O/p 2
calculation
I/P v_input_number=10 v_input_cid=3 calculation 108 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 recreate 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 Apr 23, 2019 5:51 PM (in response to 3722045)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 Apr 23, 2019 5:53 PM (in response to 3722045)1 person found this helpful3722045 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 57 O/p 2
calculation
I/P v_input_number=10 v_input_cid=3 calculation 108 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.

5. Re: Based on input sum positive or negative value
Paulzip Apr 23, 2019 6:17 PM (in response to 3722045)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 Apr 23, 2019 6:53 PM (in response to 3722045)1 person found this helpfulCan 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
3722045 Apr 24, 2019 6:39 AM (in response to mathguy)Hi All,
Thanks for your responses.
Thanks.

8. Re: Based on input sum positive or negative value
Paulzip Apr 25, 2019 7:02 PM (in response to 3722045)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.