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/94bd7840-9366-42d1-b818-277c05246ea7n%40googlegroups.com.
