add value to local variable oracle PL/SQL

Question

DECLARE 
var1 INTEGER :=0;
var2 INTEGER :=0;
BEGIN

SELECT DISTINCT
<whatever here>

CASE
WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 0 AND 10 THEN var1+1
WHEN trunc(thisDate2) - TRUNC(thatDate2) BETWEEN 11 AND 20 THEN var2+1 
ELSE 0 
END 

FROM
<Rest of query here>

basically what I want to be able to do is to add 1 to the local variable then print out the value of that variable as part of my select statement using a count (or sum of the count) or something everytime the difference in the ages falls within those categories

I'm not sure how to add to the local variable basically.

I suspect that the OP actually wants an incrementing value for each range. SQL Server allows this by letting the variable be assigned a new variable for each row. I suspect that he is trying to reproduce this functionality. — Allan
– Allan, Commented Nov 20, 2014 at 16:52
Very good spot, @Allan (I've messed up when +1 you comment -- sorry). Dan, maybe you should provide some sample input data and the expected outcome. — Sylvain Leroux
– Sylvain Leroux, Commented Nov 20, 2014 at 16:58

Multisync · Accepted Answer · 2014-11-20 16:39:48Z

2

SELECT 
    sum(CASE WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 0 AND 10 THEN 1 ELSE 0 END) + var1,
    sum(CASE WHEN trunc(thisDate2) - TRUNC(thatDate2) BETWEEN 11 AND 20 THEN 1 ELSE 0 END) + var2
    into var1, var2
FROM
<Rest of query here>

edited Nov 20, 2014 at 16:39

answered Nov 20, 2014 at 16:30

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aramillo Over a year ago

check that your else condition will return var1 instead of 0

Multisync Over a year ago

@Aramillo I'm not sure I understand your comment. I suppose I only forgot to sum

Aramillo Over a year ago

sorry my bad english, but what i mean is that in case that condition is false you return 0 + var2, and OP wants only 0

Multisync Over a year ago

@Aramillo I think that after adding a sum it's not actual anymore.

mikron · Accepted Answer · 2014-11-20 16:45:37Z

1

Try analytic functions

BEGIN
SELECT DISTINCT
<whatever here>

sum(CASE
WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 0 AND 10 THEN 1
end) over() var1,
sum(CASE
WHEN trunc(thisDate2) - TRUNC(thatDate2) BETWEEN 11 AND 20 THEN 1 
END) over() var2

FROM
<Rest of query here>

edited Nov 20, 2014 at 16:45

answered Nov 20, 2014 at 16:38

mikron

69310 silver badges20 bronze badges

Comments

Sylvain Leroux · Accepted Answer · 2014-11-20 17:35:33Z

Given a comment by Allan, maybe you are looking for something like than instead:

SELECT V.*,
    case when t1 is not null then
          count(t1) over (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         when t2 is not null then
          count(t2) over (ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         else
          0
    end varX
    FROM (
    SELECT DISTINCT

    <whatever here>

    ROWNUM n,
    CASE WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 0 AND 10 THEN 1 END t1,
    CASE WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 11 AND 20 THEN 1 END t2


    FROM
    <Rest of query here>

    ) V

Some example for you to check if this is what you are looking for: http://sqlfiddle.com/#!4/2de0d/2

The inner query is basically yours, plus

a ROWNUM column (maybe not necessary if you somehow ORDER BY your rows)
a marker set to 1 if the row is in the first range, NULL otherwise
a marker set to 1 if the row is in the second range, NULL otherwise

The outer query use the analytic function COUNT() OVER(...) to respectively count the numbers of markers between the current row and the first of the result set. The row number n is used here. Replace that by something more relevant if your data are already ordered.

Sylvain Leroux · Accepted Answer · 2014-11-20 16:59:49Z

0

I don't think you can update some variable while executing a query. All you can do is fetch the result value into some variable.

Slight variation over Multisync's answer, using COUNT instead of SUM:

SELECT 
    count(CASE WHEN trunc(thisDate) - TRUNC(thatDate) BETWEEN 0 AND 10 THEN 1 END) ,
    count(CASE WHEN trunc(thisDate2) - TRUNC(thatDate2) BETWEEN 11 AND 20 THEN 1 END)
    into var1, var2
FROM
<Rest of query here>

This will actually return a one-row result with the count of values is the first range in the first column, and the count of values in the second range in the second column. Using the INTO var1,var2 clause, PL/SQL will implicitly fetch those values into your local variables.

edited Nov 20, 2014 at 16:59

answered Nov 20, 2014 at 16:48

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Collectives™ on Stack Overflow

add value to local variable oracle PL/SQL

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related