Help with prepared statment for crosstab

From: Carl Shelbourne <Carl(dot)Shelbourne(at)stanleybet(dot)com>
To: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Help with prepared statment for crosstab
Date: 2009-11-03 13:15:21
Message-ID: 747BBD65CFF0834A90A43031630CA9EE4B5D4B47AB@sibexchange.stanleybet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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, registered in England with no.3357517.

**************************************************************************

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Richard Broersma 2009-11-03 17:36:56 Bug - DatabaseMetaData.getCatalogs() was [Novice] Can't get list of databases with \list
Previous Message Radosław Smogura 2009-10-30 17:35:25 Re: Implementation of query timeout