import java.sql.*;
import javax.sql.*;

//  TestDerby485688
// SELECT COUNT(t0.ID) FROM CTS1.TEST_TABLE t0 GROUP BY t0.CODE HAVING (t0.CODE = 'GBR' OR t0.CODE = 'CHA') AND t0.CODE IS NOT NULL
// SELECT COUNT(t0.ID), CODE FROM CTS1.TEST_TABLE t0 GROUP BY t0.CODE HAVING (t0.CODE = 'GBR' OR t0.CODE = 'CHA') AND t0.CODE IS NOT NULL

public class TestHaving
{
    public static void main(String[] args) throws Throwable
    {
        org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource ds 
=
            new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
        
	ds.setDatabaseName("TestHavingDB;create=true");
        ds.setUser("CTS1");
        ds.setPassword("dbpwd1");

        PooledConnection pc = ds.getPooledConnection();
        Connection conn = pc.getConnection();

        System.out.println("Database product: " + 
conn.getMetaData().getDatabaseProductName());
        System.out.println("Database version: " + 
conn.getMetaData().getDatabaseProductVersion());
        System.out.println("Driver name:      " + 
conn.getMetaData().getDriverName());
        System.out.println("Driver version:   " + 
conn.getMetaData().getDriverVersion());

        Statement stmt = conn.createStatement();
        try { stmt.executeUpdate("drop table TEST_TABLE"); 
}
        catch (SQLException sqlX) {} // ok, didn't exist

        stmt.executeUpdate("CREATE TABLE TEST_TABLE ( ID VARCHAR(255)   PRIMARY KEY NOT NULL, NAME VARCHAR(255) , COUNTRY VARCHAR(255), CODE VARCHAR(255), OtherCol VARCHAR(255) , AnotherCol VARCHAR(255))");

        stmt.executeUpdate("insert into TEST_TABLE values  ('1', 'Alan E. Brech ', 'United States', 'USA', '1', '2')");
        stmt.executeUpdate("insert into TEST_TABLE values  ('2', 'Arthur D. Brech ', 'United States', 'USA', '3', '4')");
        stmt.executeUpdate("insert into TEST_TABLE values  ('3', 'Shelly D. Gowan',   'United States', 'USA', '5', '6')");
        stmt.executeUpdate("insert into TEST_TABLE values ('4', 'Robert E. Bassett',   'United States', 'USA', '7', '8')");
        stmt.executeUpdate("insert into TEST_TABLE values ('5', 'Stephen S. D''Millio',  'United States', 'USA', '9', '10')");
        stmt.executeUpdate("insert into TEST_TABLE values ('6', 'Karen R. Smith',      'United States', 'USA', '11', '12')");
        stmt.executeUpdate("insert into TEST_TABLE values ('7', 'Stephen J. Jones',   'United States', 'USA', '13', '14')");
        stmt.executeUpdate("insert into TEST_TABLE values ('8', 'Irene M. Jones',     'United States', 'USA', '15', '16')");
        stmt.executeUpdate("insert into TEST_TABLE values ('9', 'Bonnie B. Smith',   'United States', 'USA', '17', '18')");
        stmt.executeUpdate("insert into TEST_TABLE values ('10', 'Kate P. Adamson',     'United States', 'USA', '19', '20')");
        stmt.executeUpdate("insert into TEST_TABLE values ('11', 'Jonathan K. Smith',  'England',       'GBR', '21', '22')");
        stmt.executeUpdate("insert into TEST_TABLE values ('12', 'null',               'Ireland',       'IRE', '23', '24')");
        stmt.executeUpdate("insert into TEST_TABLE values ('13', 'Douglas A. Bradley', 'China',         'CHA', '25', '26')");
        stmt.executeUpdate("insert into TEST_TABLE values ('14', 'Kellie A. Stevenson',  'Japan',         'JPN', '27', '28')");
        stmt.executeUpdate("insert into TEST_TABLE values ('15', 'Margaret Marks',     'United States', 'USA', '29', '30')");
        stmt.executeUpdate("insert into TEST_TABLE values ('16', 'Sonya C. Sonya',   'England',       'GBR', '31', '32')");
        stmt.executeUpdate("insert into TEST_TABLE values ('17', 'Jack B. Goodson',      'Ireland',       'IRE', '33', '34')");
        stmt.executeUpdate("insert into TEST_TABLE values ('18', 'Ron F. Regal',      'China',         'CHA', '35', '36')");
        stmt.executeUpdate("insert into TEST_TABLE values ('19', 'Lisa M. Priestley',    'China',         'CHA', 'null', 'null')");
        stmt.executeUpdate("insert into TEST_TABLE values ('20', 'David R. Williams',   'China',         'CHA', 'null', 'null')");
        stmt.close();

         PreparedStatement pstmt = conn.prepareStatement("SELECT t0.CODE, COUNT(t0.ID) FROM CTS1.TEST_TABLE t0 GROUP BY t0.CODE HAVING (t0.CODE = ? OR t0.CODE = ?) AND t0.CODE IS NOT NULL");

        pstmt.setString(1, "GBR");
        pstmt.setString(2, "CHA");


        ResultSet rset = pstmt.executeQuery();

        while (rset.next())
        {
            System.out.println("result: " + rset.getObject(1)+":" + rset.getObject(2));
        }

        rset.close();
        pstmt.close();
        conn.close();
        pc.close();

    }
}