Populating an entire column with same value returned from a CASE statement
Multi tool use
I am trying to populate a column with the same result value from a resulting CASE statement through the entire s_date/part_no grouping.
We have tried multiple different routes to get recursion to work for what we're needing, but with no luck. The calculations are extremely tricky, and we know SQL isn't the best for recursion, so we're trying to find an alternate route to go for the time being to meet the customer needs and time crunch.
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note,
CASE
WHEN curr_mth_note IS NOT NULL
AND i_group = '1'
AND s_level = '80' THEN qty_filled
ELSE NULL
END AS g1s1_filled
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
ORDER BY
s_date,
part_no,
i_group,
DECODE(s_level, '80', 1, '100', 2, 'Late', 3)
The current output for the above looks like this:
What I'm trying/hoping to get is the entire g1s1_filled column to show the same 67 amount where i_group = '1' and s_level = '80' for all available date/part combos.
So for that column to show this:
G1S1_FILLED
67
67
67
67
67
I've researched how to try to use a where instead of when, with no luck.
There will be multiple other columns built out like this, to where I can eventually do calculations as well.
sql oracle case
asked Dec 28 '18 at 14:26
KassieBKassieB
305
add a comment |
I am trying to populate a column with the same result value from a resulting CASE statement through the entire s_date/part_no grouping.
We have tried multiple different routes to get recursion to work for what we're needing, but with no luck. The calculations are extremely tricky, and we know SQL isn't the best for recursion, so we're trying to find an alternate route to go for the time being to meet the customer needs and time crunch.
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note,
CASE
WHEN curr_mth_note IS NOT NULL
AND i_group = '1'
AND s_level = '80' THEN qty_filled
ELSE NULL
END AS g1s1_filled
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
ORDER BY
s_date,
part_no,
i_group,
DECODE(s_level, '80', 1, '100', 2, 'Late', 3)
The current output for the above looks like this:
What I'm trying/hoping to get is the entire g1s1_filled column to show the same 67 amount where i_group = '1' and s_level = '80' for all available date/part combos.
So for that column to show this:
G1S1_FILLED
67
67
67
67
67
I've researched how to try to use a where instead of when, with no luck.
There will be multiple other columns built out like this, to where I can eventually do calculations as well.
sql oracle case
asked Dec 28 '18 at 14:26
KassieBKassieB
305
1
In your example data, only one record meets the criteria ofi_group = '1'ands_level = '80'and it's value is 67 forg1s_filled. So I am having trouble seeing the problem.
– Ryan Wilson
Dec 28 '18 at 14:33
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
What if there is more than one 1/80 row for ans_date/part_nocombination - how will you choose which of theqty_filledvalues to use? (Or do those four columns form a composite key, so that can't happen?)
– Alex Poole
Dec 28 '18 at 14:49
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53
add a comment |
I am trying to populate a column with the same result value from a resulting CASE statement through the entire s_date/part_no grouping.
We have tried multiple different routes to get recursion to work for what we're needing, but with no luck. The calculations are extremely tricky, and we know SQL isn't the best for recursion, so we're trying to find an alternate route to go for the time being to meet the customer needs and time crunch.
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note,
CASE
WHEN curr_mth_note IS NOT NULL
AND i_group = '1'
AND s_level = '80' THEN qty_filled
ELSE NULL
END AS g1s1_filled
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
ORDER BY
s_date,
part_no,
i_group,
DECODE(s_level, '80', 1, '100', 2, 'Late', 3)
The current output for the above looks like this:
What I'm trying/hoping to get is the entire g1s1_filled column to show the same 67 amount where i_group = '1' and s_level = '80' for all available date/part combos.
So for that column to show this:
G1S1_FILLED
67
67
67
67
67
I've researched how to try to use a where instead of when, with no luck.
There will be multiple other columns built out like this, to where I can eventually do calculations as well.
sql oracle case
asked Dec 28 '18 at 14:26
KassieBKassieB
305
I am trying to populate a column with the same result value from a resulting CASE statement through the entire s_date/part_no grouping.
We have tried multiple different routes to get recursion to work for what we're needing, but with no luck. The calculations are extremely tricky, and we know SQL isn't the best for recursion, so we're trying to find an alternate route to go for the time being to meet the customer needs and time crunch.
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note,
CASE
WHEN curr_mth_note IS NOT NULL
AND i_group = '1'
AND s_level = '80' THEN qty_filled
ELSE NULL
END AS g1s1_filled
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
ORDER BY
s_date,
part_no,
i_group,
DECODE(s_level, '80', 1, '100', 2, 'Late', 3)
The current output for the above looks like this:
What I'm trying/hoping to get is the entire g1s1_filled column to show the same 67 amount where i_group = '1' and s_level = '80' for all available date/part combos.
So for that column to show this:
G1S1_FILLED
67
67
67
67
67
I've researched how to try to use a where instead of when, with no luck.
There will be multiple other columns built out like this, to where I can eventually do calculations as well.
sql oracle case
sql oracle case
asked Dec 28 '18 at 14:26
KassieBKassieB
305
asked Dec 28 '18 at 14:26
KassieBKassieB
305
asked Dec 28 '18 at 14:26
KassieBKassieB
305
asked Dec 28 '18 at 14:26
KassieBKassieB
305
asked Dec 28 '18 at 14:26
KassieBKassieB
305
305
1
In your example data, only one record meets the criteria ofi_group = '1'ands_level = '80'and it's value is 67 forg1s_filled. So I am having trouble seeing the problem.
– Ryan Wilson
Dec 28 '18 at 14:33
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
What if there is more than one 1/80 row for ans_date/part_nocombination - how will you choose which of theqty_filledvalues to use? (Or do those four columns form a composite key, so that can't happen?)
– Alex Poole
Dec 28 '18 at 14:49
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53
add a comment |
1
In your example data, only one record meets the criteria ofi_group = '1'ands_level = '80'and it's value is 67 forg1s_filled. So I am having trouble seeing the problem.
– Ryan Wilson
Dec 28 '18 at 14:33
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
What if there is more than one 1/80 row for ans_date/part_nocombination - how will you choose which of theqty_filledvalues to use? (Or do those four columns form a composite key, so that can't happen?)
– Alex Poole
Dec 28 '18 at 14:49
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53
1
1
In your example data, only one record meets the criteria of
i_group = '1' and s_level = '80' and it's value is 67 for g1s_filled. So I am having trouble seeing the problem.– Ryan Wilson
Dec 28 '18 at 14:33
In your example data, only one record meets the criteria of
i_group = '1' and s_level = '80' and it's value is 67 for g1s_filled. So I am having trouble seeing the problem.– Ryan Wilson
Dec 28 '18 at 14:33
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
What if there is more than one 1/80 row for an
s_date/part_no combination - how will you choose which of the qty_filled values to use? (Or do those four columns form a composite key, so that can't happen?)– Alex Poole
Dec 28 '18 at 14:49
What if there is more than one 1/80 row for an
s_date/part_no combination - how will you choose which of the qty_filled values to use? (Or do those four columns form a composite key, so that can't happen?)– Alex Poole
Dec 28 '18 at 14:49
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53
add a comment |
4 Answers
4
active
oldest
votes
I think you want a window function:
SUM(CASE WHEN curr_mth_note IS NOT NULL AND i_group = '1' AND
s_level = '80'
THEN qty_filled
END) OVER (PARTITION BY s_date, part_no) AS g1s1_filled
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
add a comment |
You could use a conditional analytic minimum in the SELECT clause. I don't have your data, so I will illustrate with a similar query on the SCOTT.EMP table - suppose I want to fill NEWCOL with the salary of one particular employee. I would do it like this:
select empno, ename, sal, job, deptno,
min(case when empno = 7499 and ename = 'ALLEN' then sal end) over () as newcol
from scott.emp;
EMPNO ENAME SAL JOB DEPTNO NEWCOL
---------- ---------- ---------- --------- ---------- ----------
7369 SMITH 800 CLERK 20 1600
7499 ALLEN 1600 SALESMAN 30 1600
7521 WARD 1250 SALESMAN 30 1600
7566 JONES 2975 MANAGER 20 1600
7654 MARTIN 1250 SALESMAN 30 1600
7698 BLAKE 2850 MANAGER 30 1600
7782 CLARK 2450 MANAGER 10 1600
7788 SCOTT 3000 ANALYST 20 1600
7839 KING 5000 PRESIDENT 10 1600
7844 TURNER 1500 SALESMAN 30 1600
7876 ADAMS 1100 CLERK 20 1600
7900 JAMES 950 CLERK 30 1600
7902 FORD 3000 ANALYST 20 1600
7934 MILLER 1300 CLERK 10 1600
If (in your problem) there is only one row with the "special" combination of values in I_GROUP and S_LEVEL, then you can use (conditional, analytic) MIN or MAX or even AVG or SUM - they will all be equal to the single value, 67 in your case. If there is more than one such value, then you will need to make a choice: do you want the MIN, the MAX, the AVG, or perhaps something else. In any case, you can still use the same idea.
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
add a comment |
instead of case statement, write a subquery for it
(select qty_filled
from t_Table
where
i_group = '1' and
s_level = '80' and
rownum = 1
) as g1s1_filled
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
add a comment |
You can remove the CASE expression and move your whole query (excepted the ORDER BY clause) to a CTE. Then you can self-JOIN it to append the relevant g1s1_filled to each row.
NB : you have to be careful when defining the self-join conditions ; I provided a suggestion below, you might need to refine it.
Query :
WITH cte AS (
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
)
SELECT a.*, b.qty_filled as g1s1_filled
FROM
cte AS a
INNER JOIN cte as b
ON b.s_date = a.s_date
AND b.part_no = a.part_no
AND b.i_group = '1'
AND b.s_level = '80'
AND b.curr_mth_note IS NOT NULL
ORDER BY
a.s_date,
a.part_no,
a.i_group,
DECODE(a.s_level, '80', 1, '100', 2, 'Late', 3)
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
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
});
}
});
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%2f53960043%2fpopulating-an-entire-column-with-same-value-returned-from-a-case-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
I think you want a window function:
SUM(CASE WHEN curr_mth_note IS NOT NULL AND i_group = '1' AND
s_level = '80'
THEN qty_filled
END) OVER (PARTITION BY s_date, part_no) AS g1s1_filled
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
add a comment |
I think you want a window function:
SUM(CASE WHEN curr_mth_note IS NOT NULL AND i_group = '1' AND
s_level = '80'
THEN qty_filled
END) OVER (PARTITION BY s_date, part_no) AS g1s1_filled
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
add a comment |
I think you want a window function:
SUM(CASE WHEN curr_mth_note IS NOT NULL AND i_group = '1' AND
s_level = '80'
THEN qty_filled
END) OVER (PARTITION BY s_date, part_no) AS g1s1_filled
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
I think you want a window function:
SUM(CASE WHEN curr_mth_note IS NOT NULL AND i_group = '1' AND
s_level = '80'
THEN qty_filled
END) OVER (PARTITION BY s_date, part_no) AS g1s1_filled
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
answered Dec 28 '18 at 14:41
Gordon LinoffGordon Linoff
762k35296400
762k35296400
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
add a comment |
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
Thank you so much ... This worked great! I was able to build out the calculations and get the results I was hoping for as well. I hate when the answers seem so simple and I feel like I've wracked my brain for hours. Thanks again!
– KassieB
Dec 28 '18 at 15:04
add a comment |
You could use a conditional analytic minimum in the SELECT clause. I don't have your data, so I will illustrate with a similar query on the SCOTT.EMP table - suppose I want to fill NEWCOL with the salary of one particular employee. I would do it like this:
select empno, ename, sal, job, deptno,
min(case when empno = 7499 and ename = 'ALLEN' then sal end) over () as newcol
from scott.emp;
EMPNO ENAME SAL JOB DEPTNO NEWCOL
---------- ---------- ---------- --------- ---------- ----------
7369 SMITH 800 CLERK 20 1600
7499 ALLEN 1600 SALESMAN 30 1600
7521 WARD 1250 SALESMAN 30 1600
7566 JONES 2975 MANAGER 20 1600
7654 MARTIN 1250 SALESMAN 30 1600
7698 BLAKE 2850 MANAGER 30 1600
7782 CLARK 2450 MANAGER 10 1600
7788 SCOTT 3000 ANALYST 20 1600
7839 KING 5000 PRESIDENT 10 1600
7844 TURNER 1500 SALESMAN 30 1600
7876 ADAMS 1100 CLERK 20 1600
7900 JAMES 950 CLERK 30 1600
7902 FORD 3000 ANALYST 20 1600
7934 MILLER 1300 CLERK 10 1600
If (in your problem) there is only one row with the "special" combination of values in I_GROUP and S_LEVEL, then you can use (conditional, analytic) MIN or MAX or even AVG or SUM - they will all be equal to the single value, 67 in your case. If there is more than one such value, then you will need to make a choice: do you want the MIN, the MAX, the AVG, or perhaps something else. In any case, you can still use the same idea.
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
add a comment |
You could use a conditional analytic minimum in the SELECT clause. I don't have your data, so I will illustrate with a similar query on the SCOTT.EMP table - suppose I want to fill NEWCOL with the salary of one particular employee. I would do it like this:
select empno, ename, sal, job, deptno,
min(case when empno = 7499 and ename = 'ALLEN' then sal end) over () as newcol
from scott.emp;
EMPNO ENAME SAL JOB DEPTNO NEWCOL
---------- ---------- ---------- --------- ---------- ----------
7369 SMITH 800 CLERK 20 1600
7499 ALLEN 1600 SALESMAN 30 1600
7521 WARD 1250 SALESMAN 30 1600
7566 JONES 2975 MANAGER 20 1600
7654 MARTIN 1250 SALESMAN 30 1600
7698 BLAKE 2850 MANAGER 30 1600
7782 CLARK 2450 MANAGER 10 1600
7788 SCOTT 3000 ANALYST 20 1600
7839 KING 5000 PRESIDENT 10 1600
7844 TURNER 1500 SALESMAN 30 1600
7876 ADAMS 1100 CLERK 20 1600
7900 JAMES 950 CLERK 30 1600
7902 FORD 3000 ANALYST 20 1600
7934 MILLER 1300 CLERK 10 1600
If (in your problem) there is only one row with the "special" combination of values in I_GROUP and S_LEVEL, then you can use (conditional, analytic) MIN or MAX or even AVG or SUM - they will all be equal to the single value, 67 in your case. If there is more than one such value, then you will need to make a choice: do you want the MIN, the MAX, the AVG, or perhaps something else. In any case, you can still use the same idea.
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
add a comment |
You could use a conditional analytic minimum in the SELECT clause. I don't have your data, so I will illustrate with a similar query on the SCOTT.EMP table - suppose I want to fill NEWCOL with the salary of one particular employee. I would do it like this:
select empno, ename, sal, job, deptno,
min(case when empno = 7499 and ename = 'ALLEN' then sal end) over () as newcol
from scott.emp;
EMPNO ENAME SAL JOB DEPTNO NEWCOL
---------- ---------- ---------- --------- ---------- ----------
7369 SMITH 800 CLERK 20 1600
7499 ALLEN 1600 SALESMAN 30 1600
7521 WARD 1250 SALESMAN 30 1600
7566 JONES 2975 MANAGER 20 1600
7654 MARTIN 1250 SALESMAN 30 1600
7698 BLAKE 2850 MANAGER 30 1600
7782 CLARK 2450 MANAGER 10 1600
7788 SCOTT 3000 ANALYST 20 1600
7839 KING 5000 PRESIDENT 10 1600
7844 TURNER 1500 SALESMAN 30 1600
7876 ADAMS 1100 CLERK 20 1600
7900 JAMES 950 CLERK 30 1600
7902 FORD 3000 ANALYST 20 1600
7934 MILLER 1300 CLERK 10 1600
If (in your problem) there is only one row with the "special" combination of values in I_GROUP and S_LEVEL, then you can use (conditional, analytic) MIN or MAX or even AVG or SUM - they will all be equal to the single value, 67 in your case. If there is more than one such value, then you will need to make a choice: do you want the MIN, the MAX, the AVG, or perhaps something else. In any case, you can still use the same idea.
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
You could use a conditional analytic minimum in the SELECT clause. I don't have your data, so I will illustrate with a similar query on the SCOTT.EMP table - suppose I want to fill NEWCOL with the salary of one particular employee. I would do it like this:
select empno, ename, sal, job, deptno,
min(case when empno = 7499 and ename = 'ALLEN' then sal end) over () as newcol
from scott.emp;
EMPNO ENAME SAL JOB DEPTNO NEWCOL
---------- ---------- ---------- --------- ---------- ----------
7369 SMITH 800 CLERK 20 1600
7499 ALLEN 1600 SALESMAN 30 1600
7521 WARD 1250 SALESMAN 30 1600
7566 JONES 2975 MANAGER 20 1600
7654 MARTIN 1250 SALESMAN 30 1600
7698 BLAKE 2850 MANAGER 30 1600
7782 CLARK 2450 MANAGER 10 1600
7788 SCOTT 3000 ANALYST 20 1600
7839 KING 5000 PRESIDENT 10 1600
7844 TURNER 1500 SALESMAN 30 1600
7876 ADAMS 1100 CLERK 20 1600
7900 JAMES 950 CLERK 30 1600
7902 FORD 3000 ANALYST 20 1600
7934 MILLER 1300 CLERK 10 1600
If (in your problem) there is only one row with the "special" combination of values in I_GROUP and S_LEVEL, then you can use (conditional, analytic) MIN or MAX or even AVG or SUM - they will all be equal to the single value, 67 in your case. If there is more than one such value, then you will need to make a choice: do you want the MIN, the MAX, the AVG, or perhaps something else. In any case, you can still use the same idea.
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
edited Dec 28 '18 at 14:42
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
answered Dec 28 '18 at 14:38
mathguymathguy
26k41736
26k41736
add a comment |
add a comment |
instead of case statement, write a subquery for it
(select qty_filled
from t_Table
where
i_group = '1' and
s_level = '80' and
rownum = 1
) as g1s1_filled
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
add a comment |
instead of case statement, write a subquery for it
(select qty_filled
from t_Table
where
i_group = '1' and
s_level = '80' and
rownum = 1
) as g1s1_filled
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
add a comment |
instead of case statement, write a subquery for it
(select qty_filled
from t_Table
where
i_group = '1' and
s_level = '80' and
rownum = 1
) as g1s1_filled
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
instead of case statement, write a subquery for it
(select qty_filled
from t_Table
where
i_group = '1' and
s_level = '80' and
rownum = 1
) as g1s1_filled
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
answered Dec 28 '18 at 14:32
SimonareSimonare
7,18611435
7,18611435
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
add a comment |
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
This only returns the column with a value of 1 in all of them.
– KassieB
Dec 28 '18 at 14:45
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
I am not sure about your table relations. you can put your relational constraint on where caluse so that It will return what you need.
– Simonare
Dec 28 '18 at 14:49
add a comment |
You can remove the CASE expression and move your whole query (excepted the ORDER BY clause) to a CTE. Then you can self-JOIN it to append the relevant g1s1_filled to each row.
NB : you have to be careful when defining the self-join conditions ; I provided a suggestion below, you might need to refine it.
Query :
WITH cte AS (
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
)
SELECT a.*, b.qty_filled as g1s1_filled
FROM
cte AS a
INNER JOIN cte as b
ON b.s_date = a.s_date
AND b.part_no = a.part_no
AND b.i_group = '1'
AND b.s_level = '80'
AND b.curr_mth_note IS NOT NULL
ORDER BY
a.s_date,
a.part_no,
a.i_group,
DECODE(a.s_level, '80', 1, '100', 2, 'Late', 3)
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
add a comment |
You can remove the CASE expression and move your whole query (excepted the ORDER BY clause) to a CTE. Then you can self-JOIN it to append the relevant g1s1_filled to each row.
NB : you have to be careful when defining the self-join conditions ; I provided a suggestion below, you might need to refine it.
Query :
WITH cte AS (
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
)
SELECT a.*, b.qty_filled as g1s1_filled
FROM
cte AS a
INNER JOIN cte as b
ON b.s_date = a.s_date
AND b.part_no = a.part_no
AND b.i_group = '1'
AND b.s_level = '80'
AND b.curr_mth_note IS NOT NULL
ORDER BY
a.s_date,
a.part_no,
a.i_group,
DECODE(a.s_level, '80', 1, '100', 2, 'Late', 3)
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
add a comment |
You can remove the CASE expression and move your whole query (excepted the ORDER BY clause) to a CTE. Then you can self-JOIN it to append the relevant g1s1_filled to each row.
NB : you have to be careful when defining the self-join conditions ; I provided a suggestion below, you might need to refine it.
Query :
WITH cte AS (
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
)
SELECT a.*, b.qty_filled as g1s1_filled
FROM
cte AS a
INNER JOIN cte as b
ON b.s_date = a.s_date
AND b.part_no = a.part_no
AND b.i_group = '1'
AND b.s_level = '80'
AND b.curr_mth_note IS NOT NULL
ORDER BY
a.s_date,
a.part_no,
a.i_group,
DECODE(a.s_level, '80', 1, '100', 2, 'Late', 3)
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
You can remove the CASE expression and move your whole query (excepted the ORDER BY clause) to a CTE. Then you can self-JOIN it to append the relevant g1s1_filled to each row.
NB : you have to be careful when defining the self-join conditions ; I provided a suggestion below, you might need to refine it.
Query :
WITH cte AS (
SELECT
s_date,
part_no,
i_group,
s_level,
p_category,
qty_filled,
qty_total,
relief_amt,
extreme_amt,
curr_mth_note
FROM
(
SELECT
t1.s_date,
t1.part_no,
t1.i_group,
t1.s_level,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
FROM
multi_table mv,
t_table t1
WHERE
t1.part_no = mv.part_no
AND mv.part_no = 'xxxx'
AND t1.s_date = mv.s_date
AND t1.s_date = '201805'
GROUP BY
t1.s_date,
t1.i_group,
t1.s_level,
t1.part_no,
t1.p_category,
t1.qty_filled,
t1.qty_total,
mv.relief_amt,
mv.extreme_amt,
mv.curr_mth_note,
ORDER BY
t1.s_date,
t1.i_group,
t1.s_level DESC
)
)
SELECT a.*, b.qty_filled as g1s1_filled
FROM
cte AS a
INNER JOIN cte as b
ON b.s_date = a.s_date
AND b.part_no = a.part_no
AND b.i_group = '1'
AND b.s_level = '80'
AND b.curr_mth_note IS NOT NULL
ORDER BY
a.s_date,
a.part_no,
a.i_group,
DECODE(a.s_level, '80', 1, '100', 2, 'Late', 3)
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
answered Dec 28 '18 at 14:41
GMBGMB
6,8602520
6,8602520
add a comment |
add a comment |
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.
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%2f53960043%2fpopulating-an-entire-column-with-same-value-returned-from-a-case-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
9 TDG9QFUQpy3VhMZ4bfyPJ,vfc5uQz uU,Rzw1v
1
In your example data, only one record meets the criteria of
i_group = '1'ands_level = '80'and it's value is 67 forg1s_filled. So I am having trouble seeing the problem.– Ryan Wilson
Dec 28 '18 at 14:33
@RyanWilson yeah I know, but I'm going to be doing separate calculations for each i_group / s_level combo, that has to build off of the previous totals dependent on what is left in the relief_amt column. So I'm trying to populate the same value vertically so I can make those calculations work as expected.
– KassieB
Dec 28 '18 at 14:38
What if there is more than one 1/80 row for an
s_date/part_nocombination - how will you choose which of theqty_filledvalues to use? (Or do those four columns form a composite key, so that can't happen?)– Alex Poole
Dec 28 '18 at 14:49
@AlexPoole That will not happen, as the date/part/group/level combo qtys are already being calculated within the other views, and being pulled into here, so we can use them individually for a report.
– KassieB
Dec 28 '18 at 14:53