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);
    }