How to declare a variable inside an Oracle select statement

Question

I have this Oracle SQL request:

SELECT col1,
       col2,
       DECODE(
                 SUM(CASE WHEN col3='A' AND col4='+' THEN col5 ELSE 0 END),
                 NULL,
                 0,
                 SUM(CASE WHEN col3='A' AND col4='+' THEN col5 ELSE 0 END)
             )
FROM mytable
group by col1, col2;

I am asking if there is a way to declare a kind of variable and have something like this:

SELECT col1,
       col2,
       DECODE(
                 myVariable,
                 NULL,
                 0,
                 myVariable
             )
FROM mytable
group by col1, col2;

How are you intending to use this query? Variables are not a SQL thing but belong to the realm of the software which runs the query. So the solution will depend on whether your want to run the query interactively in some client such as SQL*Plus, as part of a parameterised stored procedure or as a JDBC PreparedStatement (to name just three possibilities). — APC
– APC, Commented Jan 3, 2019 at 14:48

D Stanley · Accepted Answer · 2019-01-03 14:38:07Z

2

no, but you could do a subquery:

SELECT col1,
       col2,
       DECODE(
                 SUM(myColumn),
                 NULL,
                 0,
                 SUM(myColumn)
             )
FROM (
    SELECT 
        col1,
        col2,
        CASE WHEN col3='A' AND col4='+' THEN col5 ELSE 0 END myColumn   
        FROM mytable
     ) a
group by col1, col2;

answered Jan 3, 2019 at 14:38

D Stanley

153k12 gold badges189 silver badges257 bronze badges

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

Comments

sticky bit · Accepted Answer · 2019-01-03 14:47:48Z

1

You can simlpy use coalesce() (or nvl()) instead of decode().

SELECT col1,
       col2,
       coalesce(sum(CASE
                      WHEN col3 = 'A'
                           AND col4 = '+' THEN
                        col5
                      ELSE
                        0
                    END),
                0)
       FROM mytable
       GROUP BY col1,
                col2;

answered Jan 3, 2019 at 14:47

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-01-03 14:59:20Z

1

You can use coalesce(). I think this is sufficient:

select col1, col2,
       coalesce(sum(case when col3 = 'A' and col4 = '+' then col5 end), 0)
from mytable
group by col1, col2;

In actual fact, this expression:

sum(case when col3 = 'A' and col4 = '+' then col5 else 0 end)

Cannot return NULL in a query with a group by -- every group has at least one row and the else guarantees a 0 returns rather than NULL.

So, this should also do what you want:

select col1, col2,
       sum(case when col3 = 'A' and col4 = '+' then col5 end)
from mytable
group by col1, col2;

answered Jan 3, 2019 at 14:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Ted at ORCL.Pro · Accepted Answer · 2019-01-03 14:42:40Z

0

Yes you can use substitution variables:

SELECT col1,
       col2,
       DECODE(
                 &&myVariable,
                 NULL,
                 0,
                 &&myVariable
             )
FROM mytable
group by col1, col2;

More info here Oracle SQL*Plus Substitution Variables

answered Jan 3, 2019 at 14:42

Ted at ORCL.Pro

1,6121 gold badge11 silver badges10 bronze badges

1 Comment

D Stanley Over a year ago

I believe the intent is to treat SUM(CASE WHEN col3='A' AND col4='+' THEN col5 ELSE 0 END) as a "variable" so it does not need to be repeated, not to pass in arbitrary amounts as variables,

Collectives™ on Stack Overflow

How to declare a variable inside an Oracle select statement

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related