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
On Saturday, February 11, 2023 at 10:26:07 PM UTC Andrei Tokar wrote:
> 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
>
>
> On Wednesday, February 8, 2023 at 6:08:20 AM UTC-5 Jim Foster wrote:
>
>> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/88044a42-10f8-4cd2-bf94-421144bab4d7n%40googlegroups.com.
