I am working on an accounting system supported by the h2 database. I have several views to collect data from the ACCOUNTS , TRANSACTIONS and TEMPLATE_TRANSACTIONS tables.
Problem in a nutshell
Although I have a SELECT that accurately displays the data I need to see in my VIEW_TEMPLATES_DATA , when I use this SELECT inside CREATE VIEW VIEW_TEMPLATES_DATA and then go SELECT * FROM VIEW_TEMPLATES_DATA , h2 tells me that one column in it cannot to be found. How can h2 be completely satisfied with a bare SELECT , but then not happy when it is packed into a CREATE VIEW statement?
I apologize for the length of this message, but the SELECT is quite long and complex, and there is the necessary reference information. The message contains all the SQL needed to create a small test database for those interested.
Background
A template transaction is a way of storing frequently used transactions in the form of 'prototype': the only requirement for a template transaction is that it has a narrative. Date, amounts, bills, etc. Are optional.
An account can associate a template transaction with it, which can be automatically created when the account is negotiated. This is very convenient for credit card accounts.
Naturally, the “legs” for the template transaction are stored in a separate table from the one that 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 in the production database for some time. Now I need to add the boolean column IS_PAYER to the view, which will be TRUE for any template transactions whose identifiers are in ACCOUNTS.PAYER_TEMPLATE_ID . As stated above, I have a SELECT that 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 look was acceptable, and my new SELECT too, but my new CREATE VIEW not! I don't do much in SQL, and the error message is not entirely useful, and I really don't know where to go with this.
Can any people tell me the direction of the solution?