Hi,
I would like to be able to use a column reference as a parameter of a
function alias, but that does not appear to work. For example the
statement:
select T1.*, select count(*) from BY_NAME(T1.NAME) from TEST T1
throws:
org.h2.jdbc.JdbcSQLException: Column "T1.NAME" not found;
This equivalent query:
select T1.*, select count(*) from TEST T2 where T2.NAME = T1.NAME from
TEST T1
works just fine.
I created a test app below to demonstrate the issue.
Regards,
Peter
package test;
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:~/
test");
Statement stmt = conn.createStatement();
ResultSet rs = null;
try {
stmt.execute("drop table TEST");
} catch (Exception e) { }
try {
stmt.execute("drop alias BY_NAME");
} catch (Exception e) { }
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
\"test.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();
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
