0

I was trying to make a query like in that topic: JFreeChart using numeric query ORACLE

to generate a barchart, but i am always receiving that error: java.sql.SQLException: JDBCCategoryDataset.executeQuery() : insufficient columns returned from the database.

My code is as follow:

SELECT CASE WHEN TIME>23.73 AND TIME<=24.0 THEN '23.73<TIME<=24.10' 
    WHEN TIME>24.10 AND TIME<=25.68 THEN '24.10<TIME<=25.68'           
    WHEN TIME>25.68 AND TIME<=27.36 THEN '25.68<TIME<=27.36' 
        ELSE '27.36<TIME'  
   END   || ' with value '|| COUNT(*) v  FROM SWIMMER 
GROUP BY CASE 
WHEN TIME>23.73 AND TIME<=24.10 THEN '23.73<TIME<=24.10' 
WHEN TIME>24.10 AND TIME<=25.68 THEN '24.10<TIME<=25.68'  
WHEN TIME>25.68 AND TIME<=27.36 THEN '25.68<TIME<=27.36' 
ELSE '27.36<TIME'  END

What i am doing wrong?

Improve this question
1
  • Cross-posted here. Commented May 13, 2013 at 4:28

1 Answer 1

Reset to default
1

The SQL you have quoted above results in an error (using SQL Fiddle to check the code) as the two case statements don't match JFreeChart is just re-throwing the error with a misleading message.

If your SQL is correct JFreeChart is telling you that you need additional columns in your result set as you are concatenating your case statement with the total your query, if it worked, would only return one column.

Try using this SQL:

SELECT time_range || ' with value ' || total as category, total
  FROM (SELECT time_range, COUNT(*) AS total
          FROM (SELECT CASE
                         WHEN TIME > 23.73
                              AND TIME <= 24.0 THEN
                          '23.73<TIME<=24.10'
                         WHEN TIME > 24.10
                              AND TIME <= 25.68 THEN
                          '24.10<TIME<=25.68'
                         WHEN TIME > 25.68
                              AND TIME <= 27.36 THEN
                          '25.68<TIME<=27.36'
                         ELSE
                          '27.36<TIME'
                       END AS time_range
                  FROM swimmer)
         GROUP BY time_range)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I tried that: SELECT intrvl || ' with value '|| count(*) v FROM ( SELECT CASE WHEN TIME>23.73 AND TIME<=24.0 THEN '23.73<TIME<=24.10' WHEN TIME>24.10 AND TIME<=25.68 THEN '24.10<TIME<=25.68' WHEN TIME>25.68 AND TIME<=27.36 THEN '25.68<TIME<=27.36' ELSE '27.36<TIME' END intrvl, TIME FROM SWIMMER) GROUP BY intrvl copying that approach: sqlfiddle.com/#!4/02d93/11 but did n work!
GrahamA, i need to simplify my query at max, because sometimes there is a hundred of intervals(i am showing only with four)!I will go crazy with many selects within selects!I got confused with your answer: why a need that many Alias?all the work in only one field in my DB(numeric field TIME) Why my last didn t work? Best Wishes, PaulH.
@user2113983 your original query didn't work as you used 24.0 in the select and 24.10 in the group by. The one in your comment didn't work as you still only have one column I've corrected it here. I used 3 in line queries (alias) so I didn't have to repeat the case statement - the code is dry
GrahamA, thanks!That 24.0 was a mistyped when i made copyNpaste(its correct in original code), but you nailed:**select intrvl || ' with value '|| count() v, count() total** worked! But i have a minor problem: the text outside the chart is showing like that: 23.73<TIME<24.10 with value 23 , i wanna show just 23.73<TIME<24.10, i retired WITH VALUE, but whats the variable with the TOTAL?When i retired total, i got the same old error.I just wanna show the total in top of the bar, not in outside text. Thanks for your help!
Graham, i found it: SELECT intrvl, count(*) total FROM Thanks for your help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.