hi all,
i am trying to fix this bug within my program. its a java, postgres and
jasper based program which generates charts. now i am generating a
chart which does not show any 0 data points if they exist, only
non-zero ones. obviously i am trying to do everything in just one sql
query, but i am not sure if its possible or not. i also need to
generate a temporary table and then delete it.
I have broken the tasks into,
1. create a temp table
2. create records for 0 data points
3. update with non 0 records
4. delete temp table.
now this is what i have and i would appreciate all help with fixing
this,
public void createTempTubProdTable(String filter, String tableName)
throws SQLException {
String sqlStr = "";
String sqlWhereStr = " WHERE ";
Debug.println("FILTER: " + filter);
if (filter != null)
{
sqlWhereStr = sqlWhereStr + filter;
}
else
{
sqlWhereStr = sqlWhereStr + "'?' = '?'";
}
String sqlCreateTableStr = "" +
"CREATE TABLE \"public\".\"tubprod_temp\" ( " +
" \"tubprod_event\" BIGSERIAL, " +
" \"edit_time\" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), " +
" \"edit_time_count\" BIGINT, " +
" CONSTRAINT \"tubprod_temp_pkey\" PRIMARY KEY(\"tubprod_event\") " +
") WITHOUT OIDS; ";
String sqlInsertDataStr = " " +
" INSERT INTO tubprod_temp (edit_time,edit_time_count) " +
" SELECT to_timestamp(to_char (last_edit_timestamp,'YYYY-MM-DD
HH24:00:00'), 'YYYY-MM-DD HH24:00:00')AS edit_time, count(to_char
(last_edit_timestamp,'YYYY-MM-DD HH24:00:00')) as edit_time_count " +
" FROM " + tableName + " " + sqlWhereStr +
" GROUP BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00') " +
" ORDER BY to_char (last_edit_timestamp,'YYYY-MM-DD HH24:00:00'); ";
Debug.println("sqlInsertDataStr: " + sqlInsertDataStr);
Statement stmt = conn.createStatement();
// Create temporary tub production table
sqlStr = sqlCreateTableStr + sqlInsertDataStr;
Debug.println(sqlStr);
stmt.executeUpdate(sqlStr);
}
public void deleteTempTubProdTable() throws SQLException {
Statement stmt = conn.createStatement();
// Drop temporary tub production table
String sql = "DROP TABLE tubprod_temp;";
stmt.executeUpdate(sql);
