Home » SQL & PL/SQL » SQL & PL/SQL » Query that gives many different values
| Query that gives many different values [message #651595] |
Fri, 20 May 2016 10:51  |
 |
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Hello,
I am trying to get the following values out of a query:
sum(Case when b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > 1000 then 1000 else b.VALUE4 end) - 10000 > 0 then b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > 1000 then 1000 else b.VALUE4 end) - 10000 else 0 end) as a10000ExcessDC1000
It works well, but the issue is that I have 15 different values for "1000" and 16 different values for "10000", and therefore I have 240 of those. The values do not follow an exact specific path (like 1000, 2000, 3000, 4000...), I instead have a list of them. Is there a way I could build a code with a loop and variables that would be easier? I don't mind having the data come out in a long horizontal vector, but I'm not having a very good time with this as of now.
Thank you.
|
|
|
|
| Re: Query that gives many different values [message #651596 is a reply to message #651595] |
Fri, 20 May 2016 10:55  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. When you say
Quote: I have 15 different values for "1000"
do you mean that 1000 is not a constant? If not, what is it? If it is, for example, a value taken from a column in a table, you;ll need to code a join.
|
|
|
|
|
|
|
|
| Re: Query that gives many different values [message #651600 is a reply to message #651598] |
Fri, 20 May 2016 11:10 |
|
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Sorry if this is not too clear, I'll go with an example:
Let's say it was variables #a and #b
I would want something like this:
For each #a in {1000 2000 3000 4000 5000 7500 10000 12500 15000 20000 25000 30000 35000 40000 45000 50000}
For each #b in {10000 15000 20000 25000 30000 35000 40000 45000 50000 60000 70000 75000 80000 90000 100000}
(calculate) sum(Case when b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > #a then #a else b.VALUE4 end) - #b > 0 then b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > #a then #a else b.VALUE4 end) - #b else 0 end) as a || #b || ExcessDC || #a
Loop #b
Loop #a
This isn't SQL coding it's just about the "logic" of what's I'm trying to do
Thanks
[Updated on: Fri, 20 May 2016 11:11] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query that gives many different values [message #651604 is a reply to message #651602] |
Fri, 20 May 2016 11:22 |
|
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Oracle version 10.0.5.1710
I'm not sure how to do a test case that would be helpful to the situation, I don't really understand with the link you provided unfortunately
I'll simple down the request then:
Let's say I have two variables, #A and #B, that each have 10 values #A = (0.1, 0.2, 0.37, 0.44, 0.5, 0.6, 0.7, 0.8, 0.9, 1) and #B = (0.13, 0.2, 0.37, 0.44, 0.54, 0.6, 0.766, 0.8, 0.9, 1)
Then, I'm looking to do a calculation in a database that would look like this:
SUM(a * dtb.paid_amt + b * dtb.paid_amt)
If a and b had only one value each, the code would be easy, I would just require one line. But since I have 10 values of #A and 10 values of #B, I have 100 different calculations.
How can I write the code so it brings out all 100 values I'm looking for?
|
|
|
|
| Re: Query that gives many different values [message #651605 is a reply to message #651604] |
Fri, 20 May 2016 11:28 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What is dtb? What is pad_amt?
Note that "a * dtb.paid_amt + b * dtb.paid_amt" is "(a+b)* dtb.paid_amt".
I don't know if it useful as I don't understand anything you want.
SQL works from data in tables. Where is your table? What is it? What is its data?
What are these variables? Where do they come from? A table in the database? Something else? What?
What don't you understand in the links?
Version 10.0.5.1710 never existed.
Try again.
This is database version we need.
[Updated on: Fri, 20 May 2016 11:29] Report message to a moderator |
|
|
|
| Re: Query that gives many different values [message #651606 is a reply to message #651604] |
Fri, 20 May 2016 11:32 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Sorry, man, I have no idea what you are trying to do. Is it a college homework question? If so, post the question here and people can give advice. Is it some simplification of a real-world problem? If so, what is it? Is it actually anything to do with Oracle? Or any other relational database?
|
|
|
|
| Re: Query that gives many different values [message #651607 is a reply to message #651606] |
Fri, 20 May 2016 11:43 |
|
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
It is a simplification of a real-world problem. Version is probably 11.1.0.7.0 then?
I'll say it differently.
Let's say I have a database with 3 columns in it: VALUE1, VALUE2, VALUE3. All these values are numeric and could be anything between -1000 and 300000. I want to do a calculation with these values.
On each row of code, I do the following calculation:
SELECT
Case when VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 else 0 end
FROM
Database
Then because I want the sum of every row, I add a sum, and give it a name:
SELECT
sum(Case when VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 else 0 end) as a10000ExcessDC1000
FROM
Database
And that works very well. I have no issue with that.
Now, the problem is that instead of doing it with "1000" and "10000", I actually have to do it with 16 different values of "1000" and 15 different values of "10000". Therefore, I would have to enter 240 different formulas like that.
It would be something like this:
SELECT
sum(Case when VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 1000 then 1000 else VALUE4 end) - 10000 else 0 end) as a10000ExcessDC1000,
sum(Case when VALUE1 + VALUE3 + (Case when VALUE4 > 2000 then 2000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 2000 then 2000 else VALUE4 end) - 10000 else 0 end) as a10000ExcessDC2000,
sum(Case when VALUE1 + VALUE3 + (Case when VALUE4 > 3000 then 3000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 3000 then 3000 else VALUE4 end) - 10000 else 0 end) as a10000ExcessDC3000,
sum(Case when VALUE1 + VALUE3 + (Case when VALUE4 > 4000 then 4000 else VALUE4 end) - 10000 > 0 then VALUE1 + VALUE3 + (Case when VALUE4 > 4000 then 4000 else VALUE4 end) - 10000 else 0 end) as a10000ExcessDC4000,
[...] 240 times
FROM
Database
Is there a way to do this with a loop instead?
|
|
|
|
| Re: Query that gives many different values [message #651608 is a reply to message #651607] |
Fri, 20 May 2016 11:47 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I find this incomprehensible. You are still using constants where I think you mean variables or perhaps columns. You need to describe the problem, in words. And then show the table definitions and contents, in the form of of CREATE TABLE statements and a few INSERT statements.
there was a database release 11.1.0.7, but it is many years out of date. Thank you for providing that detail.
|
|
|
|
|
|
|
|
| Re: Query that gives many different values [message #651611 is a reply to message #651610] |
Fri, 20 May 2016 12:31 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:This is exactly my problem, I have no idea how to make Oracle know these values.
If they are only in your head then there is no way that Oracle, SQL or not, can compute your formula.
Quote:That would give me this:
There are 3 lines in input 3 lines in output, so what is the purpose of SUM?
Quote:For 16 different values of #A and 15 different values of #B. Which gives me 16*15 = 240 different values.
What values?
Quote:Is there a way I could achieve that?
No as long as Oracle does not know the values, it can't compute with them.
[Updated on: Fri, 20 May 2016 12:35] Report message to a moderator |
|
|
|
|
|
| Re: Query that gives many different values [message #651613 is a reply to message #651611] |
Fri, 20 May 2016 12:48 |
|
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Michel Cadot wrote on Fri, 20 May 2016 12:31Quote:This is exactly my problem, I have no idea how to make Oracle know these values.
If they are only in your head then there is no way that Oracle, SQL or not, can compute your formula.
I am aware of that and this is why I am asking how I could possibly tell Oracle what they are. How could I input them? This is a part of my problem.
Michel Cadot wrote on Fri, 20 May 2016 12:31Quote:That would give me this:
There are 3 lines in input 3 lines in output, so what is the purpose of SUM?
I don't understand what you mean. I have this:
ABC
DEF
GHI
I do A+B+C, D+E+F, G+H+I
The purpose of sum is to do (A+B+C) + (D+E+F) + (G+H+I) otherwise I would still have 3 values instead of one.
Michel Cadot wrote on Fri, 20 May 2016 12:31Quote:For 16 different values of #A and 15 different values of #B. Which gives me 16*15 = 240 different values.
What values?
The actual values are the one I had specified earlier, in thousands. But do their actually really matter? I can adjust with actual values afterwards, I just want to know how to solve the issue.
Michel Cadot wrote on Fri, 20 May 2016 12:31Quote:Is there a way I could achieve that?
No as long as Oracle does not know the values, it can't compute with them.
Michel Cadot wrote on Fri, 20 May 2016 12:31And still no CREATE TABLE and INSERT statements.
And still no formatting when it is necessary.
Well I have no idea what CREATE TABLE I could do or how, and I have no idea where you could possibly have wanted more formatting.
let's return to my previous example and say my values of A and B would actually be:
A = {0.5 0.6 0.7 0.85}
B = {0.25 0.4 0.75}
This is fewer values than I actually have, just to make an example. I want to calculate using a combination of every possible of (A,B). So I have 4 * 3 = 12 possibilities.
in that case, I would achieve exactly what I want with this code:
SELECT
sum(VALUE1 * 0.5 + VALUE2 * 0.25 + VALUE3) as "0.5 value 0.25",
sum(VALUE1 * 0.5 + VALUE2 * 0.4 + VALUE3) as "0.5 value 0.4",
sum(VALUE1 * 0.5 + VALUE2 * 0.75 + VALUE3) as "0.5 value 0.75",
sum(VALUE1 * 0.6 + VALUE2 * 0.25 + VALUE3) as "0.6 value 0.25",
sum(VALUE1 * 0.6 + VALUE2 * 0.4 + VALUE3) as "0.6 value 0.4",
sum(VALUE1 * 0.6 + VALUE2 * 0.75 + VALUE3) as "0.6 value 0.75",
sum(VALUE1 * 0.7 + VALUE2 * 0.25 + VALUE3) as "0.7 value 0.25",
sum(VALUE1 * 0.7 + VALUE2 * 0.4 + VALUE3) as "0.7 value 0.4",
sum(VALUE1 * 0.7 + VALUE2 * 0.75 + VALUE3) as "0.7 value 0.75",
sum(VALUE1 * 0.85 + VALUE2 * 0.25 + VALUE3) as "0.85 value 0.25",
sum(VALUE1 * 0.85 + VALUE2 * 0.4 + VALUE3) as "0.85 value 0.4",
sum(VALUE1 * 0.85 + VALUE2 * 0.75 + VALUE3) as "0.85 value 0.75"
FROM Database
Now, in my situation, instead of 4 values of A and 3 values of B, I have 16 and 15, which gives me a total of 240 lines, which is too excessive. Is there a way to code it with a loop instead?
Something with that logic:
For #A = A = {0.5 0.6 0.7 0.85}
For #B = {0.25 0.4 0.75}
sum(VALUE1 * #A + VALUE2 * #B + VALUE3) as "#A value #B"
Next #B
Next #A
[Updated on: Fri, 20 May 2016 12:50] Report message to a moderator |
|
|
|
|
|
| Re: Query that gives many different values [message #651618 is a reply to message #651595] |
Fri, 20 May 2016 16:54 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- table and data for demonstration (the type of create table and insert statements we were expecting from you):
SCOTT@orcl_12.1.0.2.0> CREATE TABLE database
2 (value1 NUMBER,
3 value3 NUMBER,
4 value4 NUMBER)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL INTO database (value1, value3, value4)
2 SELECT deptno * 1000, empno, sal FROM emp WHERE job = 'CLERK'
3 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM database ORDER BY value1, value3, value4
2 /
VALUE1 VALUE3 VALUE4
---------- ---------- ----------
10000 7934 1300
20000 7369 800
20000 7876 1100
30000 7900 950
4 rows selected.
-- query that includes the a and b values that you provided:
SCOTT@orcl_12.1.0.2.0> SELECT t1.a, t2.b,
2 sum
3 (Case when b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > t1.a then t1.a else b.VALUE4 end) - t2.b > 0
4 then b.VALUE1 + b.VALUE3 + (Case when b.VALUE4 > t1.a then t1.a else b.VALUE4 end) - t2.b else 0 end)
5 as a10000ExcessDC1000
6 FROM database b,
7 (SELECT 1000 a FROM DUAL UNION ALL
8 SELECT 2000 a FROM DUAL UNION ALL
9 SELECT 3000 a FROM DUAL UNION ALL
10 SELECT 4000 a FROM DUAL UNION ALL
11 SELECT 5000 a FROM DUAL UNION ALL
12 SELECT 7500 a FROM DUAL UNION ALL
13 SELECT 10000 a FROM DUAL UNION ALL
14 SELECT 12500 a FROM DUAL UNION ALL
15 SELECT 15000 a FROM DUAL UNION ALL
16 SELECT 20000 a FROM DUAL UNION ALL
17 SELECT 25000 a FROM DUAL UNION ALL
18 SELECT 30000 a FROM DUAL UNION ALL
19 SELECT 35000 a FROM DUAL UNION ALL
20 SELECT 40000 a FROM DUAL UNION ALL
21 SELECT 45000 a FROM DUAL UNION ALL
22 SELECT 50000 a FROM DUAL) t1,
23 (SELECT 10000 b FROM DUAL UNION ALL
24 SELECT 15000 b FROM DUAL UNION ALL
25 SELECT 20000 b FROM DUAL UNION ALL
26 SELECT 25000 b FROM DUAL UNION ALL
27 SELECT 30000 b FROM DUAL UNION ALL
28 SELECT 35000 b FROM DUAL UNION ALL
29 SELECT 40000 b FROM DUAL UNION ALL
30 SELECT 45000 b FROM DUAL UNION ALL
31 SELECT 50000 b FROM DUAL UNION ALL
32 SELECT 60000 b FROM DUAL UNION ALL
33 SELECT 70000 b FROM DUAL UNION ALL
34 SELECT 75000 b FROM DUAL UNION ALL
35 SELECT 80000 b FROM DUAL UNION ALL
36 SELECT 90000 b FROM DUAL UNION ALL
37 SELECT 100000 b FROM DUAL) t2
38 GROUP BY a, b
39 ORDER BY a, b
40 /
A B A10000EXCESSDC1000
---------- ---------- ------------------
1000 10000 74829
1000 15000 54829
1000 20000 35895
1000 25000 20895
1000 30000 8850
1000 35000 3850
1000 40000 0
1000 45000 0
1000 50000 0
1000 60000 0
1000 70000 0
1000 75000 0
1000 80000 0
1000 90000 0
1000 100000 0
2000 10000 75229
2000 15000 55229
2000 20000 35995
2000 25000 20995
2000 30000 8850
2000 35000 3850
2000 40000 0
2000 45000 0
2000 50000 0
2000 60000 0
2000 70000 0
2000 75000 0
2000 80000 0
2000 90000 0
2000 100000 0
3000 10000 75229
3000 15000 55229
3000 20000 35995
3000 25000 20995
3000 30000 8850
3000 35000 3850
3000 40000 0
3000 45000 0
3000 50000 0
3000 60000 0
3000 70000 0
3000 75000 0
3000 80000 0
3000 90000 0
3000 100000 0
4000 10000 75229
4000 15000 55229
4000 20000 35995
4000 25000 20995
4000 30000 8850
4000 35000 3850
4000 40000 0
4000 45000 0
4000 50000 0
4000 60000 0
4000 70000 0
4000 75000 0
4000 80000 0
4000 90000 0
4000 100000 0
5000 10000 75229
5000 15000 55229
5000 20000 35995
5000 25000 20995
5000 30000 8850
5000 35000 3850
5000 40000 0
5000 45000 0
5000 50000 0
5000 60000 0
5000 70000 0
5000 75000 0
5000 80000 0
5000 90000 0
5000 100000 0
7500 10000 75229
7500 15000 55229
7500 20000 35995
7500 25000 20995
7500 30000 8850
7500 35000 3850
7500 40000 0
7500 45000 0
7500 50000 0
7500 60000 0
7500 70000 0
7500 75000 0
7500 80000 0
7500 90000 0
7500 100000 0
10000 10000 75229
10000 15000 55229
10000 20000 35995
10000 25000 20995
10000 30000 8850
10000 35000 3850
10000 40000 0
10000 45000 0
10000 50000 0
10000 60000 0
10000 70000 0
10000 75000 0
10000 80000 0
10000 90000 0
10000 100000 0
12500 10000 75229
12500 15000 55229
12500 20000 35995
12500 25000 20995
12500 30000 8850
12500 35000 3850
12500 40000 0
12500 45000 0
12500 50000 0
12500 60000 0
12500 70000 0
12500 75000 0
12500 80000 0
12500 90000 0
12500 100000 0
15000 10000 75229
15000 15000 55229
15000 20000 35995
15000 25000 20995
15000 30000 8850
15000 35000 3850
15000 40000 0
15000 45000 0
15000 50000 0
15000 60000 0
15000 70000 0
15000 75000 0
15000 80000 0
15000 90000 0
15000 100000 0
20000 10000 75229
20000 15000 55229
20000 20000 35995
20000 25000 20995
20000 30000 8850
20000 35000 3850
20000 40000 0
20000 45000 0
20000 50000 0
20000 60000 0
20000 70000 0
20000 75000 0
20000 80000 0
20000 90000 0
20000 100000 0
25000 10000 75229
25000 15000 55229
25000 20000 35995
25000 25000 20995
25000 30000 8850
25000 35000 3850
25000 40000 0
25000 45000 0
25000 50000 0
25000 60000 0
25000 70000 0
25000 75000 0
25000 80000 0
25000 90000 0
25000 100000 0
30000 10000 75229
30000 15000 55229
30000 20000 35995
30000 25000 20995
30000 30000 8850
30000 35000 3850
30000 40000 0
30000 45000 0
30000 50000 0
30000 60000 0
30000 70000 0
30000 75000 0
30000 80000 0
30000 90000 0
30000 100000 0
35000 10000 75229
35000 15000 55229
35000 20000 35995
35000 25000 20995
35000 30000 8850
35000 35000 3850
35000 40000 0
35000 45000 0
35000 50000 0
35000 60000 0
35000 70000 0
35000 75000 0
35000 80000 0
35000 90000 0
35000 100000 0
40000 10000 75229
40000 15000 55229
40000 20000 35995
40000 25000 20995
40000 30000 8850
40000 35000 3850
40000 40000 0
40000 45000 0
40000 50000 0
40000 60000 0
40000 70000 0
40000 75000 0
40000 80000 0
40000 90000 0
40000 100000 0
45000 10000 75229
45000 15000 55229
45000 20000 35995
45000 25000 20995
45000 30000 8850
45000 35000 3850
45000 40000 0
45000 45000 0
45000 50000 0
45000 60000 0
45000 70000 0
45000 75000 0
45000 80000 0
45000 90000 0
45000 100000 0
50000 10000 75229
50000 15000 55229
50000 20000 35995
50000 25000 20995
50000 30000 8850
50000 35000 3850
50000 40000 0
50000 45000 0
50000 50000 0
50000 60000 0
50000 70000 0
50000 75000 0
50000 80000 0
50000 90000 0
50000 100000 0
240 rows selected.
|
|
|
|
|
|
| Re: Query that gives many different values [message #651641 is a reply to message #651638] |
Sat, 21 May 2016 09:33 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Ed, I disagree with you (something that happens very rarely) in this case. Installing VirtualBox and then creating a Linux VM is a horribly complicated way to get an Oracle database. If one has Windows, it much (much!) simpler to install Oracle and create a database directly on Windows.
|
|
|
|
| Re: Query that gives many different values [message #652504 is a reply to message #651641] |
Fri, 10 June 2016 11:28 |
|
Jdevil
Messages: 8
Registered: May 2016
|
Junior Member |
|
|
Hi guys, thank you for your help. Using a part of Barbara's code, I managed to make it work. I had to push that project aside and by the time I went back on it, I recieved the authorization to create tables, which did help greatly there (I did not expect to easily have those rights, which is why I was looking for another way to do so).
I basically created a table that looks like what you did, by simply populating a table (see : insert into temptabl (THRESHOLD, DCAP) values (10000, 2000); , etc.) and made those calculations as you did.
Thank you very much for your help and your patience.
Also, no, I'm not using my own computer at work sadly, therefore I'm not absolutely sure of what I can and cannot do but I try to do as I can with the material I have.
[Updated on: Fri, 10 June 2016 11:29] Report message to a moderator |
|
|
|
| Re: Query that gives many different values [message #652523 is a reply to message #652504] |
Sat, 11 June 2016 10:43 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Jdevil wrote on Fri, 10 June 2016 11:28
Also, no, I'm not using my own computer at work sadly, therefore I'm not absolutely sure of what I can and cannot do but I try to do as I can with the material I have.
Well, as John rightly corrected me when I suggested creating a virtual machine, since your concern is more with writing SQL than in learning DBA, you could always install Oracle XE right on your computer. Do you have your own PC? I really have a hard time imagining anyone in any aspect of this business who doesn't have their own personal computer.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 06:50:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
