How to declare a variable inside an Oracle select statement
0
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;
sql oracle
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
add a comment |
0
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;
sql oracle
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
2
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
Jan 3 at 14:48
add a comment |
0
0
0
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;
sql oracle
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
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;
sql oracle
sql oracle
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
asked Jan 3 at 14:32
MChakerMChaker
1,8741233
1,8741233
2
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
Jan 3 at 14:48
add a comment |
2
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
Jan 3 at 14:48
2
2
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
Jan 3 at 14:48
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
Jan 3 at 14:48
add a comment |
4 Answers
4
active
oldest
votes
1
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 at 14:38
D StanleyD Stanley
124k9117181
add a comment |
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 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
I believe the intent is to treatSUM(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,
– D Stanley
Jan 3 at 14:45
add a comment |
0
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 at 14:47
sticky bitsticky bit
15.4k101733
add a comment |
0
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 at 14:59
Gordon LinoffGordon Linoff
792k36316419
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54024332%2fhow-to-declare-a-variable-inside-an-oracle-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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 at 14:38
D StanleyD Stanley
124k9117181
add a comment |
1
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 at 14:38
D StanleyD Stanley
124k9117181
add a comment |
1
1
1
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 at 14:38
D StanleyD Stanley
124k9117181
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 at 14:38
D StanleyD Stanley
124k9117181
answered Jan 3 at 14:38
D StanleyD Stanley
124k9117181
answered Jan 3 at 14:38
D StanleyD Stanley
124k9117181
answered Jan 3 at 14:38
D StanleyD Stanley
124k9117181
124k9117181
add a comment |
add a comment |
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 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
I believe the intent is to treatSUM(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,
– D Stanley
Jan 3 at 14:45
add a comment |
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 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
I believe the intent is to treatSUM(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,
– D Stanley
Jan 3 at 14:45
add a comment |
0
0
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 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
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 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 3 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 3 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 3 at 14:42
Ted at ORCL.ProTed at ORCL.Pro
1,35028
1,35028
I believe the intent is to treatSUM(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,
– D Stanley
Jan 3 at 14:45
add a comment |
I believe the intent is to treatSUM(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,
– D Stanley
Jan 3 at 14:45
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,– D Stanley
Jan 3 at 14:45
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,– D Stanley
Jan 3 at 14:45
add a comment |
0
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 at 14:47
sticky bitsticky bit
15.4k101733
add a comment |
0
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 at 14:47
sticky bitsticky bit
15.4k101733
add a comment |
0
0
0
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 at 14:47
sticky bitsticky bit
15.4k101733
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 at 14:47
sticky bitsticky bit
15.4k101733
answered Jan 3 at 14:47
sticky bitsticky bit
15.4k101733
answered Jan 3 at 14:47
sticky bitsticky bit
15.4k101733
answered Jan 3 at 14:47
sticky bitsticky bit
15.4k101733
15.4k101733
add a comment |
add a comment |
0
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 at 14:59
Gordon LinoffGordon Linoff
792k36316419
add a comment |
0
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 at 14:59
Gordon LinoffGordon Linoff
792k36316419
add a comment |
0
0
0
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 at 14:59
Gordon LinoffGordon Linoff
792k36316419
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 at 14:59
Gordon LinoffGordon Linoff
792k36316419
answered Jan 3 at 14:59
Gordon LinoffGordon Linoff
792k36316419
answered Jan 3 at 14:59
Gordon LinoffGordon Linoff
792k36316419
answered Jan 3 at 14:59
Gordon LinoffGordon Linoff
792k36316419
792k36316419
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54024332%2fhow-to-declare-a-variable-inside-an-oracle-select-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
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
Jan 3 at 14:48