Hi, My financial database has stopped working and I don't have any idea why. When creating a view, I get a syntax error saying a column can't be found even though I can see it there. It worked fine last time I ran it and after a lot of playing round I can't get it working again.
I hacked out the code into a test class, which also fails. The code is: public class DBTst { public static void main(String[] args) throws SQLException { //JdbcConnectionPool cp = JdbcConnectionPool.create("jdbc:h2:mem:db1", "sa", ""); // Set up the database. try (Connection cn = DriverManager.getConnection("jdbc:h2:mem:db1", "sa", "")) { // Make sure not autocommit. cn.setAutoCommit(false); // Create DB structure. // // Create the tables. try (Statement st = cn.createStatement()) { // Organisations table. st.execute("CREATE TABLE TB_ORG ( ID IDENTITY, NAME VARCHAR NOT NULL, ABBREV VARCHAR NOT NULL, TYPE INTEGER NOT NULL)"); // Activity table. st.execute("CREATE TABLE TB_ACTIVITY ( ID IDENTITY (35), NAME VARCHAR NOT NULL, CUSTREF VARCHAR NOT NULL, ORGID INTEGER NOT NULL, FOREIGN KEY (ORGID) REFERENCES TB_ORG(ID))"); // Invoice table. st.execute("CREATE TABLE TB_INVOICE (ID IDENTITY(114), DESC VARCHAR NOT NULL, YEARREL INTEGER NOT NULL, RAISED DATE NOT NULL, DUE DATE NOT NULL, JURISID INTEGER NOT NULL, ACTID INTEGER NOT NULL, FOREIGN KEY (ACTID) REFERENCES TB_ACTIVITY(ID) )"); // Event line table. st.execute("CREATE TABLE TB_EVENTLINE(ID IDENTITY, DESC VARCHAR NOT NULL, QUANTITY NUMERIC(100,2) NOT NULL, RATE NUMERIC(100,2) NOT NULL, VATVAL NUMERIC(100,2))"); // Invoice line table. st.execute("CREATE TABLE TB_INVOICETRANSACTIONLINE (INVID INTEGER NOT NULL, EVLID INTEGER NOT NULL, FOREIGN KEY (INVID) REFERENCES TB_INVOICE(ID), FOREIGN KEY (EVLID) REFERENCES TB_EVENTLINE(ID))"); //setupQueries //VIEWS //Invoice lines st.execute("CREATE VIEW VW_INVOICELINES AS SELECT A.INVID AS INV_ID, A.EVLID AS EVLID, B.DESC AS DESC, B.QUANTITY AS QUANTITY, B.RATE AS RATE, ROUND(B.QUANTITY * B.RATE, 2) AS NETTOT, B.VATVAL AS VATVAL, FROM TB_INVOICETRANSACTIONLINE A INNER JOIN TB_EVENTLINE B ON A.EVLID = B.ID"); System.out.println("Invoice lines view:"); ResultSet executeQuery = st.executeQuery("SELECT * FROM VW_INVOICELINES "); printResults(executeQuery); System.out.println("Invoice table:"); executeQuery = st.executeQuery("SELECT * FROM TB_INVOICE "); printResults(executeQuery); System.out.println("Activity table:"); executeQuery = st.executeQuery("SELECT * FROM TB_ACTIVITY "); printResults(executeQuery); System.out.println("Organisations table:"); executeQuery = st.executeQuery("SELECT * FROM TB_ORG "); printResults(executeQuery); //Invoice summary System.out.println("Invoice summary view:"); st.execute("CREATE VIEW VW_INVOICESUMMARY AS SELECT IL.INV_ID, ROUND(SUM(IL.QUANTITY * IL.RATE), 2) AS NETTOT, ROUND(SUM(IL.VATVAL), 2) AS VATTOT, ROUND(SUM(IL.QUANTITY * IL.RATE) + SUM(IL.VATVAL), 2) AS GROSSTOT, B.DESC AS DESC, B.YEARREL AS RELEVANT_YEAR, B.RAISED AS RAISED, B.DUE AS DUE, B.JURISID AS JURISID, C.ID AS ACTID, C.NAME AS ACTNAME, D.ID AS ORGID, D.NAME AS ORGNAME FROM VW_INVOICELINES IL INNER JOIN TB_INVOICE B INNER JOIN TB_ACTIVITY C INNER JOIN TB_ORG D ON IL.INV_ID = B.ID AND B.ACTID = C.ID AND C.ORGID = D.ID GROUP BY IL.INV_ID"); // Commit. cn.commit(); } } } public static void printResults(ResultSet rs) throws SQLException { StringBuilder sb = new StringBuilder(); // Top line - the column names. for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) { if (i > 0) sb.append('\t'); sb.append(rs.getMetaData().getColumnName(i + 1)); } //Output. System.out.println(sb.toString()); //Clear buffer. sb.setLength(0); //Lines. while(rs.next()) { for(int i = 0; i < rs.getMetaData().getColumnCount(); i++) { if (i > 0) sb.append('\t'); sb.append(rs.getObject(i + 1)); } //Output. System.out.println(sb.toString()); //Clear buffer. sb.setLength(0); } } } The output is: Invoice lines view: INV_ID EVLID DESC QUANTITY RATE NETTOT VATVAL Invoice table: ID DESC YEARREL RAISED DUE JURISID ACTID Activity table: ID NAME CUSTREF ORGID Organisations table: ID NAME ABBREV TYPE Invoice summary view: Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "IL.INV_ID" not found; SQL statement: CREATE VIEW VW_INVOICESUMMARY AS SELECT IL.INV_ID, ROUND(SUM(IL.QUANTITY * IL.RATE), 2) AS NETTOT, ROUND(SUM(IL.VATVAL), 2) AS VATTOT, ROUND(SUM(IL.QUANTITY * IL.RATE) + SUM(IL.VATVAL), 2) AS GROSSTOT, B.DESC AS DESC, B.YEARREL AS RELEVANT_YEAR, B.RAISED AS RAISED, B.DUE AS DUE, B.JURISID AS JURISID, C.ID AS ACTID, C.NAME AS ACTNAME, D.ID AS ORGID, D.NAME AS ORGNAME FROM VW_INVOICELINES IL INNER JOIN TB_INVOICE B INNER JOIN TB_ACTIVITY C INNER JOIN TB_ORG D ON IL.INV_ID = B.ID AND B.ACTID = C.ID AND C.ORGID = D.ID GROUP BY IL.INV_ID [42122-214] I've really got no idea what's happening. Any help would be appreciated. Regards, Jim -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ce4ed6fa-6241-45c4-a3b7-df81d0cb4bc5n%40googlegroups.com.