Help with prepared statment for crosstab

Поиск
Список
Период
Сортировка
От Carl Shelbourne
Тема Help with prepared statment for crosstab
Дата
Msg-id 747BBD65CFF0834A90A43031630CA9EE4B5D4B47AB@sibexchange.stanleybet.com
обсуждение исходный текст
Ответы Re: Help with prepared statment for crosstab
Список 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  |  201 - 210 Mercury Court |  Tithebarn Street  |  Liverpool  |  L2 2QP  |  +44 151 235 2000  |  Registered in England with no.3357517


В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Richard Broersma
Дата:
Сообщение: Bug - DatabaseMetaData.getCatalogs() was [Novice] Can't get list of databases with \list
Следующее
От: Kris Jurka
Дата:
Сообщение: Re: Bug - DatabaseMetaData.getCatalogs() was [Novice] Can't get list of databases with \list