Column as parameter of function table

Currently statements such as the following are not supported:

select T1.*, select count(*) from BY_NAME(T1.NAME) from TEST T1

Test case:

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class H2test {

    public static void main(String[] args) throws Exception {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:");
        Statement stmt = conn.createStatement();
        ResultSet rs = null;
        stmt.execute("create table TEST (ID int, NAME varchar(255))");
        stmt.execute("insert into TEST values (1, 'Bill')");
        stmt.execute("insert into TEST values (2, 'Sue')");
        stmt.execute("insert into TEST values (3, 'Bill')");
        stmt.execute("create alias BY_NAME for \"db.H2test.queryByName\"");
        rs = stmt.executeQuery("select * from TEST");
        while (rs.next()) {
            System.out.println("Row: " + rs.getInt(1) + " Name: " + rs.getString(2));
        }
        rs.close();
        rs = stmt.executeQuery(
                "select count(*) from BY_NAME('Bill')");
        while (rs.next()) {
            System.out.println("Bill count: " + rs.getInt(1));
        }
        rs.close();
        rs = stmt.executeQuery(
                "select T1.*, select count(*) from TEST T2 " +
                "where T2.NAME = T1.NAME from TEST T1");
        while (rs.next()) {
            System.out.println("Row: " + rs.getInt(1) +
                    " Name: " + rs.getString(2) + " Count: " + rs.getInt(3));
        }
        rs.close();
        rs = stmt.executeQuery(
                "select T1.*, select count(*) from BY_NAME(T1.NAME) from TEST T1");
        while (rs.next()) {
            System.out.println("Row: " + rs.getInt(1) +
                    " Name: " + rs.getString(2) + " Count: " + rs.getInt(3));
        }
        rs.close();
        stmt.close();
        conn.close();
    }

    public static ResultSet queryByName(
            Connection con, String name) throws SQLException {
        PreparedStatement ps = con.prepareStatement(
                "select ID from TEST where NAME = ?");
        ps.setString(1, name);
        return ps.executeQuery();
    }

}
Original issue reported on code.google.com by thomas.t...@gmail.com on 27 Aug 2010 at 12:23
akarshan2701 / h2database

Column as parameter of function table #228
