1

I have a Column(cliente_x_hora, a numeric field) i put in a interval and count the number in each interval.I have 3 textfields(number of intervals,value between intervals and initial value). When I select the two first(with 5 intervals and 1000 value), the query run flawless and generate the expect barchart.

Query(with two select textfields):

SELECT INTERVAL, COUNT(*) TOTAL FROM (  
SELECT CASE WHEN CLIENTE_X_HORA>0 AND CLIENTE_X_HORA<=1000.00 THEN '0<CLIENTE_X_HORA>  <=1000.00'   
WHEN CLIENTE_X_HORA>1000.00 AND CLIENTE_X_HORA<=2000.00 THEN '1000.00<CLIENTE_X_HORA><=2000.00'   
WHEN CLIENTE_X_HORA>2000.00 AND CLIENTE_X_HORA<=3000.00 THEN '2000.00<CLIENTE_X_HORA><=3000.00'   
WHEN CLIENTE_X_HORA>3000.00 AND CLIENTE_X_HORA<=4000.00 THEN '3000.00<CLIENTE_X_HORA><=4000.00'   
ELSE '4000.00<CLIENTE_X_HORA'  END   INTERVAL, CLIENTE_X_HORA  FROM SGD_CAUSA)  
GROUP BY INTERVAL ORDER BY TOTAL

The barchart is here

The problem is when I select the last field(initial value with, per example 2000), my barchart go crazy(i believe is adding up the discarded values below 2000): http://img40.imageshack.us/img40/5025/querycom5intervalo1000v.png

That ELSE(>6000) should be much smaller than is showing.How can I solve that?

Best Regards, DDias

CLARIFICATION from OP:

The query is the same as above but begins in 2000:

SELECT CASE WHEN CLIENTE_X_HORA>2000 AND CLIENTE_X_HORA<=3000.00... and ends in 6000:ELSE '6000.00<CLIENTE_X_HORA' END INTERVAL, CLIENTE_X_HORA FROM SGD_CAUSA) GROUP BY INTERVAL ORDER BY TOTAL

put the result in table form is impractical(we are talking about over 87 thousand rows) That happens always when i give an initial value different than ZERO.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
DDias
  • 13
  • 3
  • (1) Please show **the exact query** that exhibits this *"crazy"* behavior and **its result** in table form. – PM 77-1 May 12 '13 at 20:49
  • The query is the same as above but begins in 2000:SELECT CASE WHEN CLIENTE_X_HORA>2000 AND CLIENTE_X_HORA<=3000.00... and ends in 6000:ELSE '6000.00 – DDias May 12 '13 at 21:04

1 Answers1

0

Your ELSE is just that. It includes everything that is not matched by specific WHENs.

So if you do not start from zero, that last column will include everything below a lowest limit in addition to greater than highest limit.

So if you do not want this behavior, do not use ELSE at all. Use WHEN CLIENTE_X_HORA > 6000.00 (or whatever your highest limit is) as the last condition.

EDIT:

  1. In your internal query filter out (with WHERE) the values that are below the lowest limit.

  2. Since we no longer have unneeded low range, you no longer need the HAVING clause we added and you can even go back to using ELSE.

  3. If your lowest limit is zero, then you will be filtering everything below 0, which I assume is nothing.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
  • I replaced my last query from: "ELSE "+"'"+maxValue+"<"+selected_field+"' "; To: "WHEN "+selected_field+">"+maxValue+" THEN '"+maxValue+"<"+selected_field+"'" But it gives me that ERROR: java.lang.IllegalArgumentException: Null 'key' argument. – DDias May 12 '13 at 21:35
  • What we have now is that all lower values are now grouped under `NULL` key. We need to exclude them before they get to `JFreeChart`. Please add `HAVING INTERVAL IS NOT NULL` after `GROUP BY`. – PM 77-1 May 12 '13 at 21:54
  • PM 77-1, i am having another wierd behaviour after buting HAVING... in my QUERY: without initial value(initial is 0):http://img109.imageshack.us/img109/1261/with5and1000with0initia.png with 2000 as initial value: http://img853.imageshack.us/img853/5144/with5and1000with2000ini.png should be much bigger than is showing after value>4000 – DDias May 12 '13 at 22:06
  • Another option is to exclude lower range in the *internal* sub-query using `WHERE CLIENTE_X_HORA < 2000.00`; this way `COUNT` will be calculated w/o lower range. – PM 77-1 May 12 '13 at 22:19
  • Where i will put that WHERE clause? Thanks for your help. Best Regards, – DDias May 12 '13 at 22:25
  • Right after `FROM SGD_CAUSA` and before the closing bracket `)`. – PM 77-1 May 12 '13 at 22:27
  • PM 77-1, didn t work fine: http://img826.imageshack.us/img826/5688/wherelower2000.png It must show the five intervals choosen by the user. – DDias May 12 '13 at 22:44
  • I'm having difficulty opening your images. Please see the edit in my answer. Let me know if it solves your problems. – PM 77-1 May 12 '13 at 22:54
  • I tried: SELECT INTERVAL, COUNT(*) TOTAL FROM (SELECT CASE WHEN CLIENTE_X_HORA>2000.00 AND CLIENTE_X_HORA<=3000.00 THEN '2000.003000.00 AND CLIENTE_X_HORA<=4000.00 THEN '3000.004000.00 AND CLIENTE_X_HORA<=5000.00 THEN '4000.005000.00 AND CLIENTE_X_HORA<=6000.00 THEN '5000.006000.00 THEN '6000.00 – DDias May 12 '13 at 23:18
  • GROUP BY INTERVAL HAVING INTERVAL IS NOT NULL ORDER BY TOTAL but i receive a blank screen in JFreeChart. If i skipp HAVING INTERNAL, i receive the Null 'key' argument ERROR. – DDias May 12 '13 at 23:20
  • You need to decide where the lowest limit belongs. In your sample, you need **either** `CASE WHEN CLIENTE_X_HORA >= 2000.00` **or** `WHERE CLIENTE_X_HORA <= 2000.00`. One (and only one) of the conditions should include equality. – PM 77-1 May 12 '13 at 23:26
  • skipping WHERE clause i have:SELECT INTERVAL, COUNT(*) TOTAL FROM (SELECT CASE WHEN CLIENTE_X_HORA>2000.00 AND CLIENTE_X_HORA<=3000.00 THEN '2000.003000.00 AND CLIENTE_X_HORA<=4000.00 THEN '3000.004000.00 AND CLIENTE_X_HORA<=5000.00 THEN '4000.005000.00 AND CLIENTE_X_HORA<=6000.00 THEN '5000.006000.00 THEN '6000.00 – DDias May 12 '13 at 23:34
  • GROUP BY INTERVAL HAVING INTERVAL IS NOT NULL ORDER BY TOTAL but that broughts bar when cliente_x_hora>6000 with only 216 what is totally fake(compares with the first bar with begins in 0). – DDias May 12 '13 at 23:36
  • May be I was unclear. You do need `WHERE' clause. Just use it as `WHERE ... <= 2000.00` (less **or equal**). Then you will not need `HAVING` (or so I think). – PM 77-1 May 12 '13 at 23:42
  • PM 77-1, even using <=2000, it needs HAVING clause(gives null error without), and even with that, dont return ANY value(using less or equal).I changed to WHERE CLIENTE_X_HORA>2000.00 and strangely, all the numbers in each bar are very LOW: between 5000 and 6000=113, between 4000 and 5000=217 and bigger than 6000=216... i odnt know where is my mistake.Without choosing initial value is fine.that s strange. – DDias May 13 '13 at 00:05
  • Something is wrong here. Do you have access to `SQL Developer` or `SQL Plus`? Can you run your query directly in `Oracle` (not through `JDBC`)? I mean - just the SQL part. – PM 77-1 May 13 '13 at 00:09
  • PM 77-1 i post the sqlcode(with pictures in last post) here: http://www.guj.com.br/java/299531-queryoracle-para-jfreechart first picture is with initialvalue equal to 2000, last picture is with initial value equal 0.I will run SQL Plus and post results here.Wait a little bit. – DDias May 13 '13 at 00:26
  • PM 77-1, thanks for your help!The problem was with my ELSE.I never learned even negative values can go to my ELSE value.My query(with last value):FROM SGD_CAUSA WHERE CLIENTE_X_HORA>2000.00) GROUP BY INTERVAL HAVING INTERVAL IS NOT NULL ORDER BY TOTAL is correct!My Main query with ELSE was INCORRECT! – DDias May 13 '13 at 01:26