Обсуждение: Help with prepared statment for crosstab
Hi.
I have been asked to produce a report for a new game. The report needs to show the gamers unique id, gamer_number, his nickname, the average score of the best 5 scores he got playing the game during a specified period and a list of the top five scores that made this average.
e.g.:
gamerNumber;nickname;average;score1;score2;score3;score4;score5
91692656912;"nickname1";"14.33";29.85;21.03;7.38;6.73;6.66
The SQL shown below works if I do not have any parameters, but when I add the parameters, I get:
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2155)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:3114)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:1358)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:420)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:255)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:230)
I am not too sure if this is possible, but I am trying to prepare a statement containing a crosstab:
SELECT
gamehighscore.row_name[1] as gamer_number,
gamehighscore.row_name[2] as nick_name,
gamehighscore.row_name[3] as average_score, score1, score2, score3, score4, score5 from
crosstab('SELECT ARRAY[g.gamer_number::text, pl.nick_name, (SELECT avg(tp.high_score) as myAvg
from (select score as top_score from game_score t2 WHERE
game_date BETWEEN ? AND ?
and g.gamer_number=t2.gamer_number
order by score desc limit 5) as tp)::text] as row_name,
score::text as bucket, score from bet b
left join player pl on g.gamer_number=pl.gamer_number
WHERE g.game_date BETWEEN ? AND ? AND g.gamer_number=? order by score DESC limit 5')
as gamehighscore ( row_name text[], score1 numeric, score2 numeric , score3 numeric , score4 numeric , score5 numeric )
Running against Postgres 8.3.8 running on Windows XP. Tried with jdbc drivers 8.3-603.jdbc3 and 8.4-701.jdbc3
Is this possible? How do I escape the parameters within the crosstab? Is it the crosstab that is the issue even? Any help would be most welcome.
Thanks
Carl
This email and its attachments are intended for the above named only and may be confidential. If you have received them in error please notify the sender immediately and delete all records of the email and its attachments from your computer. Unless you are the intended recipient you must not read, copy, distribute, use or retain this email or any attachments or any part of them. Although we aim to use efficient virus checking procedures for emails and attachments we accept no liability for viruses and it is the recipient's responsibility to ensure they are actually virus free.
Stanley International Betting | 201 - 210 Mercury Court | Tithebarn Street | Liverpool | L2 2QP | +44 151 235 2000 | Registered in England with no.3357517
On Tue, 3 Nov 2009, Carl Shelbourne wrote: > The SQL shown below works if I do not have any parameters, but when I add > the parameters, I get: > > Caused by: org.postgresql.util.PSQLException: The column index is out of ra > nge: 1, number of columns: 0. The ? parameters you've used are inside a string literal, so they are not considered parameters. Consider: SELECT 'Does this work?'; The question mark is part of the literal, not a parameter. Kris Jurka