CODEWITHSUNDEEP
sqlh2spring-boot-test
Maxime B
Maxime B

Reputation: 3

SQL view creating on PostgreSQL but not on H2

I am having trouble to create a view on H2 database used for my Spring Boot tests. The view is created fine on regular startup using a PostgreSQL database but fails with the following error with H2:

Column "prov.block_id" not found

I am pretty sure there's something that the H2 engine cannot understand in my view creation, but I am not sure what.

CREATE VIEW my_view AS
SELECT CASE
           WHEN EXISTS (SELECT 1
                        FROM signal si
                                 LEFT JOIN block b
                                           ON prov.block_id= b.id AND si.signal_id = b.id AND si.type = 'BLOCK'
                                 LEFT JOIN prov p_direct
                                           ON si.signal_id = p_direct.id AND si.type = 'PROV'
                                 LEFT JOIN slot s
                                           ON prov.slot_id= s.id AND si.signal_id = s.id AND si.type = 'SLOT'
                                 LEFT JOIN beam be
                                           ON s.beam_id = be.id AND si.type = 'BEAM'
                        WHERE si.status = 'VALID'
                          AND (
                            prov.block_id = b.id
                                OR prov.id = p_direct.id
                                OR prov.slot_id = s.id
                                OR s.beam_id = be.id
                            )) THEN TRUE
           ELSE FALSE
           END AS is_ok,
       slot.beam_id AS b_id,
       prov.name
FROM public.prov prov
         LEFT JOIN public.slot slot ON prov.slot_id = slot.id;

Upvotes: 0

Views: 55

Answers (2)

Jiandong
Jiandong

Reputation: 104

I faced similar view creation issues when using h2 for spring-boot integration test.

since its for test only, so before test method runs, I execute a table creation Sql (table name same as view name, columns same as view column), and after method completed, drop the table.

dummy code

@Sql(scripts = "classpath: src/resources/setup.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@Sql(scripts = "classpath: src/resources/cleanup.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
@Test
void testMethod() {
}

setup.sql

create table my_view (
  is_ok char(1),
  b_id varchar2(10),
  name varchar2(20)
);
insert into my_view values ('', '', '');
commit;

cleanup.sql

drop table my_view;

Upvotes: 0

Sergey
Sergey

Reputation: 5217

FROM signal si LEFT JOIN block b ON prov.block_id= b.id AND si.signal_id = b.id AND si.type = 'BLOCK'

On this level your prov.block_id is not visible. You add it later

Upvotes: -1

Related Questions