Tying to figure out why this query is not working...
Any suggestions / help?
Postgres with a javax native query.
Query query = em.createNativeQuery("select avg(period_01), avg(period_02), avg(period_03), avg(period_04), avg(period_05) avg(period_06), avg(period_07), " +
"avg(period_08), avg(period_09), avg(period_10), avg(period_11), avg(period_12), avg(period_13), avg(period_14), avg(period_15), avg(period_16), avg(period_17), " +
"avg(period_18), avg(period_19), avg(period_20), avg(period_21), avg(period_22), avg(period_23), avg(period_24), avg(period_25), avg(period_26), avg(period_27), " +
"avg(period_28), avg(period_29), avg(period_30), avg(period_31), avg(period_32), avg(period_33), avg(period_34), avg(period_35), avg(period_36), avg(period_37), " +
"avg(period_38), avg(period_39), avg(period_40), avg(period_41), avg(period_42), avg(period_43), avg(period_44), avg(period_45), avg(period_46), avg(period_47), " +
"avg(period_48) " + "from(select * from crosstab( 'select t.date_, t.period, avg(t.price) as avg_price from ecossales_final_price t where t.date_ >= " +
"\\'?1\\' and t.gxp=\\'?2\\' group by t.period, t.date_ order by 1, 2') as ct(" +
" \"t.date_\" date " +
", \"period_01\" numeric " +
", \"period_02\" numeric, \"period_03\" numeric, \"period_04\" numeric, \"period_05\" numeric, \"period_06\" numeric, \"period_07\" numeric, \"period_08\" numeric " +
", \"period_09\" numeric, \"period_10\" numeric, \"period_11\" numeric, \"period_12\" numeric, \"period_13\" numeric, \"period_14\" numeric, \"period_15\" numeric " +
", \"period_16\" numeric, \"period_17\" numeric, \"period_18\" numeric, \"period_19\" numeric, \"period_20\" numeric, \"period_21\" numeric, \"period_22\" numeric " +
", \"period_23\" numeric, \"period_24\" numeric, \"period_25\" numeric, \"period_26\" numeric, \"period_27\" numeric, \"period_28\" numeric, \"period_29\" numeric " +
", \"period_30\" numeric, \"period_31\" numeric, \"period_32\" numeric, \"period_33\" numeric, \"period_34\" numeric, \"period_35\" numeric, \"period_36\" numeric " +
", \"period_37\" numeric, \"period_38\" numeric, \"period_39\" numeric, \"period_40\" numeric, \"period_41\" numeric, \"period_42\" numeric, \"period_43\" numeric " +
", \"period_44\" numeric, \"period_45\" numeric, \"period_46\" numeric, \"period_47\" numeric, \"period_48\" numeric " +
")" +
")s")
.setParameter(1, cal.getTime())
.setParameter(2, gxp);
List list = query.getResultList();
It's a cross-tab query.
The query works in postgres, but when I run it though java as a native query i get an ArrayIndexOutOfBoundsException when it trys to create the list (List list = query.getResultList();)
Your date comparison part below is mishandled.
t.date >= \\'?1\\' and t.gxp=\\'?2\\'
Take a look here for help Query comparing dates in SQL
In short, compare date data with date like 'YYYY-MM-DD'