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 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8580
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 #651597 is a reply to message #651595] Fri, 20 May 2016 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is clear as mud.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Query that gives many different values [message #651598 is a reply to message #651596] Fri, 20 May 2016 10:58 Go to previous messageGo to next message
Jdevil
Messages: 8
Registered: May 2016
Junior Member
Hi,
Thank you for the quick reply

No, I have a manual list of values for this.

For "1000" I have:
1000 2000 3000 4000 5000 7500 10000 12500 15000 20000 25000 30000 35000 40000 45000 50000

For "10000" I have:

10000
15000
20000
25000
30000
35000
40000
45000
50000
60000
70000
75000
80000
90000
100000
Re: Query that gives many different values [message #651600 is a reply to message #651598] Fri, 20 May 2016 11:10 Go to previous messageGo to next message
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 #651602 is a reply to message #651600] Fri, 20 May 2016 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Sorry if this is not too clear, I'll go with an example:


Do NOT just give a text example, give a test case and give the rules WITH WORDS AND SENTENCES.

AND GIVE YOUR ORACLE VERSION.

Re: Query that gives many different values [message #651603 is a reply to message #651602] Fri, 20 May 2016 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is the input, what is the output, what is the table? what are b.VALUEx? What is b.?
This is completely UNUNDERSTANDABLE.



Re: Query that gives many different values [message #651604 is a reply to message #651602] Fri, 20 May 2016 11:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
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 Go to previous messageGo to next message
John Watson
Messages: 8580
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8580
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 #651609 is a reply to message #651607] Fri, 20 May 2016 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


You mean a table.
So post CREATE TABLE and INSERT STATEMENTS for them.
And post the final result for the data you gave.

You still didn't say where does the 1000... or 0.1,0.2... come from.
How do Oracle can know these values?

Re: Query that gives many different values [message #651610 is a reply to message #651609] Fri, 20 May 2016 12:27 Go to previous messageGo to next message
Jdevil
Messages: 8
Registered: May 2016
Junior Member
This is exactly my problem, I have no idea how to make Oracle know these values.

I actually cannot create tables directly with my current authorisations for some reason, I could probably play around that to provide you something but I honestly don't really know how that works

Let's say you had this Database:

VALUE1 | VALUE2 | VALUE3
140.52 | 120.21 | 0
110.6 | 8.512 | 15.61
0 | 0 | 150.51

And I wanted to do the following calculation:

VALUE1 * 0.5 + VALUE2 * 0.25 + VALUE3

That would give me this:

47.6175
31.563
150.51

Then I want the sum of all rows:

229.6905

I can do that just fine. It's working and all.

That would be:

SELECT sum(VALUE1 * 0.5 + VALUE2 * 0.25 + VALUE3) as "0.5 value 0.25"
FROM Database 


But the problem is that I have 240 calculations like that to do. VALUE1, VALUE2 and VALUE3 never change, they're part of Database. What changes everytime is 0.5 and 0.25. So I'll replace them with #A and #B.

I need to calculate
SELECT sum(VALUE1 * #A + VALUE2 * #B + VALUE3) as "#A value #B" 


For 16 different values of #A and 15 different values of #B. Which gives me 16*15 = 240 different values.

Is there a way I could achieve that?
Re: Query that gives many different values [message #651611 is a reply to message #651610] Fri, 20 May 2016 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
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 #651612 is a reply to message #651611] Fri, 20 May 2016 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And still no CREATE TABLE and INSERT statements.
And still no formatting when it is necessary.

[Updated on: Fri, 20 May 2016 12:33]

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 Go to previous messageGo to next message
Jdevil
Messages: 8
Registered: May 2016
Junior Member
Michel Cadot wrote on Fri, 20 May 2016 12:31
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.


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:31
Quote:
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:31
Quote:
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:31
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.



Michel Cadot wrote on Fri, 20 May 2016 12:31
And 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 #651614 is a reply to message #651613] Fri, 20 May 2016 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


Put them in a table, Oracle works data in tables.

Quote:
in that case, I would achieve exactly what I want with this code:


Now this is a clear requirement.
As soon as you succeed the previous point AND POST US A TEST CASE WE CAN WORK WITH, we can help you.

Note that you can write a SQL SELECT only if you know the number of columns you want to return. There is no dynamic number of columns in pure SQL.

Re: Query that gives many different values [message #651618 is a reply to message #651595] Fri, 20 May 2016 16:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #651638 is a reply to message #651610] Sat, 21 May 2016 08:28 Go to previous messageGo to next message
EdStevens
Messages: 1333
Registered: September 2013
Senior Member
Jdevil wrote on Fri, 20 May 2016 12:27
This is exactly my problem, I have no idea how to make Oracle know these values.


Where do these values come from? Do they change from one execution to the next or could they theoretical be hard-coded into you query? Not that I'm going to suggest hard-coding. I'm just trying to nail down how much flexibility we need to allow for.

Quote:
I actually cannot create tables directly with my current authorisations for some reason


Do you have a personal computer? If so, there is no excuse for you to not have your own, private test lab with full authority to test anything you want. Install VirtualBox (free). Inside that, crate a virtual Linux system using Oracle Linux (free). Inside that, install Oracle (free for personal use under the OTN license). There will be a learning curve for you. Probably a steep one. But it will serve you well in your professional development. I've written more about it here and here.
Re: Query that gives many different values [message #651641 is a reply to message #651638] Sat, 21 May 2016 09:33 Go to previous messageGo to next message
John Watson
Messages: 8580
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 Go to previous messageGo to next message
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 Go to previous message
EdStevens
Messages: 1333
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.
Previous Topic: error 04063 creating view
Next Topic: Extract the most data after joining 3 tables
Goto Forum:
  


Current Time: Sun Aug 01 17:28:48 CDT 2021