StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Well, you can if you refactor a but your query:</p> <pre><code>WITH t(col) AS ( SELECT 1 FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION SELECT 4 FROM dual UNION SELECT 5 FROM dual ) SELECT col, (SELECT col FROM t WHERE col = outer_q.col) new_col, (SELECT sum (latest_col) from ( SELECT col latest_col FROM t UNION ALL SELECT col FROM t ) x where x.latest_col = outer_q.col ) newest_col -- need to get an output "4" from t outer_q where col = 2; </code></pre> <p>This is possible here because <code>outer_q</code> is now in the <code>where</code> clause of the sub-query. It was used before in the sub-sub-query (the one with the <code>UNION ALL</code>), and this one was hiding it.</p> <p>To try to make things clearer, now we have something like:</p> <pre><code>with t as (...) select col, (SELECT col FROM t WHERE col = outer_q.col) new_col, (SELECT col FROM (Something more complex) WHERE ... = outer_q.col) new_col, from t outer_q where col = 2; </code></pre> <p>So we now have the same level of "interiority".</p> <p><em>EDIT:</em> to answer the updated question, there is a little adaptation needed:</p> <pre><code>WITH t(col) AS ( SELECT 1 FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION SELECT 4 FROM dual UNION SELECT 5 FROM dual ), t1(amount, col) AS ( SELECT 100, 2 FROM dual UNION SELECT 200, 3 FROM dual ) SELECT col, (SELECT col FROM t WHERE col = outer_q.col) new_col, (SELECT SUM(amount) FROM (SELECT col, col amount FROM t -- row is (1, 1), then (2, 2) etc UNION ALL SELECT col, amount FROM t1 -- row is (2, 100), then (3, 200) etc ) WHERE col = outer_q.col ) newest_col -- gives 102 as it takes whole `SUM` FROM t outer_q WHERE col = 2; </code></pre> <p>The part to understand is in the innermost query: you want to sum both the column and the amount value, so you repeat the <code>col</code> value as if it was an amount.</p> <p>Another way to obtain the same result (with more performance, I guess) would be to sum <code>col</code> and <code>amount</code> on the same row:</p> <pre><code>WITH t(col) AS ( SELECT 1 FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual UNION SELECT 4 FROM dual UNION SELECT 5 FROM dual ), t1(amount, col) AS ( SELECT 100, 2 FROM dual UNION SELECT 200, 3 FROM dual ) SELECT col, (SELECT col FROM t WHERE col = outer_q.col) new_col, (SELECT SUM(all_amount) FROM (SELECT col, col + amount all_amount FROM t1) WHERE col = outer_q.col ) newest_col -- gives 315 as it takes whole `SUM` FROM t outer_q WHERE col = 2; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload