h2 VIEW has problems with correct SELECT statement

Question

I am working on an accounting system, backed by an h2 database. I have a number of views to pull together data from the ACCOUNTS, TRANSACTIONS and TEMPLATE_TRANSACTIONS tables.

Problem, in a nutshell

Although I have a SELECT statement which produces exactly the data I need to see in my VIEW_TEMPLATES_DATA view, when I use that SELECT inside CREATE VIEW VIEW_TEMPLATES_DATA and then go SELECT * FROM VIEW_TEMPLATES_DATA, h2 tells me that one of the columns in it can't be found. How can h2 be completely happy with the 'naked' SELECT statement, but then not happy once it's packaged into a CREATE VIEW statement?

I apologise for the length of this post, but the SELECT statement is of necessity rather long and complex, and there is necessary background information. The post contains all the SQL needed to construct a small test database, for those who are interested.

Background

A template transaction is a way of storing frequently-used transactions in 'prototype' form: the only requirement for a template transaction is that it have a narration. Date, amounts, accounts etc are all optional.

An account may have associated with it a template transaction which can be instantiated automatically when the account is reconciled. This is very handy for credit card accounts, for example.

Naturally, the 'legs' for a template transaction are stored in a separate table from that which stores the header information.

So, we have the following tables and view (naturally, simplified from the real thing):

ACCOUNTS
ID   NAME         PAYER_TEMPLATE_ID   CURRENCY_ID
-------------------------------------------------
95   account0     null                1
122  account1     47                  0
178  foo bar      35                  0

TEMPLATE_TRANSACTIONS
ID   NARRATION
--------------
32   template0
35   template1
47   template2

TEMPLATE_TRANSACTION_LEGS
ID   HEAD_TABLE_ID   ACCOUNT_ID   AMOUNT
----------------------------------------
23   32              95           null
74   35              178          500
75   35              null         -500

VIEW_TEMPLATES_DATA
HEAD_ID   NARRATION   LEG_ID   ACCOUNT_ID   AMOUNT   CURRENCY_ID
----------------------------------------------------------------
23        template0   23       95           null     1
35        template1   74       178          500      0
35        template1   75       null         -500     null
47        template2   null     null         null     null

Here is the code for the above:

CREATE TABLE TEMPLATE_TRANSACTIONS(
   ID BIGINT NOT NULL PRIMARY KEY,
   NARRATION VARCHAR NOT NULL DEFAULT ' '
);

CREATE TABLE CURRENCIES(
   ID BIGINT NOT NULL PRIMARY KEY,
   DESCRIPTION VARCHAR DEFAULT ''
);

CREATE TABLE ACCOUNTS(
   ID BIGINT NOT NULL PRIMARY KEY,
   NAME VARCHAR DEFAULT '',
   PAYER_TEMPLATE_ID BIGINT,
   CURRENCY_ID BIGINT NOT NULL
);
ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_FK_2 FOREIGN KEY(CURRENCY_ID) REFERENCES CURRENCIES(ID);   
ALTER TABLE ACCOUNTS ADD CONSTRAINT ACCOUNTS_FK_3 FOREIGN KEY(PAYER_TEMPLATE_ID) REFERENCES TEMPLATE_TRANSACTIONS(ID);

CREATE TABLE TEMPLATE_TRANSACTION_LEGS(
   ID BIGINT NOT NULL PRIMARY KEY,
   HEAD_TABLE_ID BIGINT NOT NULL,
   ACCOUNT_ID BIGINT DEFAULT NULL,
   AMOUNT INT DEFAULT NULL
);
ALTER TABLE TEMPLATE_TRANSACTION_LEGS ADD CONSTRAINT TEMPLATE_TRANSACTION_LEGS_FK_1 FOREIGN KEY(HEAD_TABLE_ID) REFERENCES TEMPLATE_TRANSACTIONS(ID);
ALTER TABLE TEMPLATE_TRANSACTION_LEGS ADD CONSTRAINT TEMPLATE_TRANSACTION_LEGS_FK_2 FOREIGN KEY(ACCOUNT_ID) REFERENCES ACCOUNTS(ID);

CREATE VIEW VIEW_TEMPLATES_DATA AS (SELECT
   TEMPLATE_TRANSACTIONS.ID     AS HEAD_ID,
   TEMPLATE_TRANSACTIONS.NARRATION,

   TEMPLATE_TRANSACTION_LEGS.ID     AS LEG_ID,
   TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID,
   TEMPLATE_TRANSACTION_LEGS.AMOUNT,
   ACCOUNTS.CURRENCY_ID
   FROM (TEMPLATE_TRANSACTIONS LEFT OUTER JOIN TEMPLATE_TRANSACTION_LEGS ON TEMPLATE_TRANSACTION_LEGS.HEAD_TABLE_ID = TEMPLATE_TRANSACTIONS.ID)
      LEFT OUTER JOIN ACCOUNTS ON TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID = ACCOUNTS.ID
);

INSERT INTO CURRENCIES (ID, DESCRIPTION) VALUES (0, 'currency0');
INSERT INTO CURRENCIES (ID, DESCRIPTION) VALUES (1, 'currency1');
INSERT INTO TEMPLATE_TRANSACTIONS (ID, NARRATION) VALUES (32, 'template0');
INSERT INTO TEMPLATE_TRANSACTIONS (ID, NARRATION) VALUES (35, 'template1');
INSERT INTO TEMPLATE_TRANSACTIONS (ID, NARRATION) VALUES (47, 'template2');
INSERT INTO ACCOUNTS (ID, NAME, PAYER_TEMPLATE_ID, CURRENCY_ID) VALUES (95, 'account0', null, 1);
INSERT INTO ACCOUNTS (ID, NAME, PAYER_TEMPLATE_ID, CURRENCY_ID) VALUES (122, 'account1', 47, 0);
INSERT INTO ACCOUNTS (ID, NAME, PAYER_TEMPLATE_ID, CURRENCY_ID) VALUES (178, 'foo bar', 35, 0);
INSERT INTO TEMPLATE_TRANSACTION_LEGS (ID, HEAD_TABLE_ID, ACCOUNT_ID, AMOUNT) VALUES (23, 32, 95, null);
INSERT INTO TEMPLATE_TRANSACTION_LEGS (ID, HEAD_TABLE_ID, ACCOUNT_ID, AMOUNT) VALUES (74, 35, 178, 500);
INSERT INTO TEMPLATE_TRANSACTION_LEGS (ID, HEAD_TABLE_ID, ACCOUNT_ID, AMOUNT) VALUES (75, 35, null, -500);

This has been working fine in the production database for some time. Now I need to add a boolean column, IS_PAYER, to the view, which will be TRUE for any template transactions whose IDs are in ACCOUNTS.PAYER_TEMPLATE_ID. As stated above, I have a SELECT statement which does exactly what I want:

HEAD_ID   NARRATION   LEG_ID   ACCOUNT_ID   AMOUNT   CURRENCY_ID   IS_PAYER
---------------------------------------------------------------------------
32        template0   23       95           null     1             FALSE
35        template1   74       178          500      0             TRUE
35        template1   75       null         -500     null          TRUE
47        template2   null     null         null     null          TRUE

SELECT TEMPLATE_TRANSACTIONS.ID     AS HEAD_ID,
   TEMPLATE_TRANSACTIONS.NARRATION,
   TEMPLATE_TRANSACTION_LEGS.ID     AS LEG_ID,
   TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID,
   TEMPLATE_TRANSACTION_LEGS.AMOUNT,
   ACCOUNTS.CURRENCY_ID,
   IS_PAYER
FROM (
   (TEMPLATE_TRANSACTIONS LEFT OUTER JOIN TEMPLATE_TRANSACTION_LEGS ON TEMPLATE_TRANSACTION_LEGS.HEAD_TABLE_ID = TEMPLATE_TRANSACTIONS.ID)
    LEFT OUTER JOIN ACCOUNTS ON TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID = ACCOUNTS.ID)
JOIN
   (SELECT TEMPLATE_ID, (CASE WHEN PAYER_TEMPLATE_ID IS NOT NULL THEN TRUE ELSE FALSE END) AS IS_PAYER FROM 
      (SELECT TEMPLATE_TRANSACTIONS.ID AS TEMPLATE_ID, ACCOUNTS.PAYER_TEMPLATE_ID AS PAYER_TEMPLATE_ID FROM 
          ACCOUNTS RIGHT JOIN TEMPLATE_TRANSACTIONS ON ACCOUNTS.PAYER_TEMPLATE_ID =TEMPLATE_TRANSACTIONS.ID))
ON TEMPLATE_ID = TEMPLATE_TRANSACTIONS.ID

but when I do this:

DROP VIEW VIEW_TEMPLATES_DATA;
CREATE VIEW VIEW_TEMPLATES_DATA AS (SELECT
   TEMPLATE_TRANSACTIONS.ID     AS HEAD_ID,
   TEMPLATE_TRANSACTIONS.NARRATION,
   TEMPLATE_TRANSACTION_LEGS.ID     AS LEG_ID,
   TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID,
   TEMPLATE_TRANSACTION_LEGS.AMOUNT,
   ACCOUNTS.CURRENCY_ID,
   IS_PAYER
FROM (
   (TEMPLATE_TRANSACTIONS LEFT OUTER JOIN TEMPLATE_TRANSACTION_LEGS ON TEMPLATE_TRANSACTION_LEGS.HEAD_TABLE_ID = TEMPLATE_TRANSACTIONS.ID)
    LEFT OUTER JOIN ACCOUNTS ON TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID = ACCOUNTS.ID)
JOIN
   (SELECT TEMPLATE_ID, (CASE WHEN PAYER_TEMPLATE_ID IS NOT NULL THEN TRUE ELSE FALSE END) AS IS_PAYER FROM 
      (SELECT TEMPLATE_TRANSACTIONS.ID AS TEMPLATE_ID, ACCOUNTS.PAYER_TEMPLATE_ID AS PAYER_TEMPLATE_ID FROM 
          ACCOUNTS RIGHT JOIN TEMPLATE_TRANSACTIONS ON ACCOUNTS.PAYER_TEMPLATE_ID =TEMPLATE_TRANSACTIONS.ID))
ON TEMPLATE_ID = TEMPLATE_TRANSACTIONS.ID
);

and then go SELECT * FROM VIEW_TEMPLATES_DATA it tells me TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID not found.

I really don't understand why my original view was acceptable, and my new SELECT is too, but my new CREATE VIEW isn't! I don't do a lot in SQL, and the error message is not exactly helpful, and I really don't know where to go with this one.

Can any kind folks point me in the direction of a solution?

Answer 1

The problem are the parentheses that you are using in your JOIN clauses. The SELECT on the view will work as soon as you remove them:

CREATE VIEW VIEW_TEMPLATES_DATA AS (SELECT
   TEMPLATE_TRANSACTIONS.ID     AS HEAD_ID,
   TEMPLATE_TRANSACTIONS.NARRATION,
   TEMPLATE_TRANSACTION_LEGS.ID     AS LEG_ID,
   TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID,
   TEMPLATE_TRANSACTION_LEGS.AMOUNT,
   ACCOUNTS.CURRENCY_ID,
   IS_PAYER
FROM  TEMPLATE_TRANSACTIONS
      LEFT OUTER JOIN TEMPLATE_TRANSACTION_LEGS ON TEMPLATE_TRANSACTION_LEGS.HEAD_TABLE_ID = TEMPLATE_TRANSACTIONS.ID
      LEFT OUTER JOIN ACCOUNTS ON TEMPLATE_TRANSACTION_LEGS.ACCOUNT_ID = ACCOUNTS.ID
      JOIN (SELECT TEMPLATE_ID, (CASE WHEN PAYER_TEMPLATE_ID IS NOT NULL THEN TRUE ELSE FALSE END) AS IS_PAYER
              FROM (SELECT TEMPLATE_TRANSACTIONS.ID AS TEMPLATE_ID, ACCOUNTS.PAYER_TEMPLATE_ID AS PAYER_TEMPLATE_ID
                      FROM ACCOUNTS
                           RIGHT JOIN TEMPLATE_TRANSACTIONS ON ACCOUNTS.PAYER_TEMPLATE_ID =TEMPLATE_TRANSACTIONS.ID))
      ON TEMPLATE_ID = TEMPLATE_TRANSACTIONS.ID
);

When you put the first LEFT OUTER JOIN between parentheses, the joined table TEMPLATE_TRANSACTION_LEGS is not visible from the second LEFT OUTER JOIN.