Asking for help

[Jim Foster]

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

[Andrei Tokar]

Your test will pass if last view will be defined as (please note placement of join conditions):

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 ON IL.INV_ID = B.ID
INNER JOIN TB_ACTIVITY C ON B.ACTID = C.ID
INNER JOIN TB_ORG D ON C.ORGID = D.ID
GROUP BY IL.INV_ID

[Jim Foster]

Hi Andrei,

Thank you for your help.  That seems to work ok now.

I wanted to try to work out what happened.  Do you know if the syntax has changed?  I upgraded just before I noticed the problem but deleted the old jar so couldn't downgrade.  I went down to the version I thought it used to be but still got the same thing.

Many thanks and best regards,

Jim

[Evgenij Ryazanov]

Hello!

In H2 2.1.210 an old bug with incorrectly parsed join conditions was fixed. In earlier version of H2 in some cases they were applied to the wrong joins.

Few applications use workarounds for this bug and these workarounds need to be removed from them during upgrade to H2 2.1.