Details
Major Description
I can execute a SELECT query that works but when I try to create a view on that select query, I simply get a java null pointer exception with no details. I have tested the same statements on a postgres database and they worked without error.
Below, the PW_DIFF_WITHIN_REP_VIEW create does work, but the PW_DIFF_AR_REP_VIEW does not. Again, the SELECT clause of PW_DIFF_AR_REP_VIEW will work, but when used within the create clause the error occurs.
I have attached the entire database creation script and an embedded instance that can be used for testing. It has data.
– WITHIN_REP_VIEW combines the WITHIN_REP_COUNTER_VIEW and WITHIN_REP_RESPONSE_VIEW into one table from which across
– replication or other statistical summaries by replication can be produced
CREATE VIEW JSL_DB.WITHIN_REP_VIEW (SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, VALUE) AS
(SELECT JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, AVERAGE AS VALUE
FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_STAT on JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_STAT.SIM_RUN_ID_FK)
JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID = JSL_DB.WITHIN_REP_STAT.ELEMENT_ID_FK
UNION
SELECT JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK, EXP_NAME, ELEMENT_NAME, STAT_NAME, REP_NUM, LAST_VALUE as VALUE
FROM (JSL_DB.SIMULATION_RUN JOIN JSL_DB.WITHIN_REP_COUNTER_STAT on JSL_DB.SIMULATION_RUN.ID = JSL_DB.WITHIN_REP_COUNTER_STAT.SIM_RUN_ID_FK)
JOIN JSL_DB.MODEL_ELEMENT ON ELEMENT_ID = JSL_DB.WITHIN_REP_COUNTER_STAT.ELEMENT_ID_FK);
– PW_DIFF_WITHIN_REP_VIEW computes the pairwise differences across difference simulation experiments
– doesn't work for derby, 3-28-2019, works for postgres
–
– create view JSL_DB.PW_DIFF_WITHIN_REP_VIEW
– as (select SIMULATION_RUN.SIM_NAME, A.SIM_RUN_ID_FK AS A_SIM_NUM, A.STAT_NAME, A.EXP_NAME as A_EXP_NAME, A.REP_NUM, A.VALUE as A_VALUE,
– B.SIM_RUN_ID_FK as B_SIM_NUM, B.EXP_NAME as B_EXP_NAME, B.VALUE as B_VALUE,
– '(' || A.EXP_NAME || ' - ' || B.EXP_NAME || ')' as DIFF_NAME, (A.VALUE - B.VALUE) as A_MINUS_B
– from JSL_DB.WITHIN_REP_VIEW as A, JSL_DB.WITHIN_REP_VIEW as B, JSL_DB.SIMULATION_RUN
– where A.SIM_RUN_ID_FK = JSL_DB.SIMULATION_RUN.ID
– and A.STAT_NAME = B.STAT_NAME
– and A.REP_NUM = B.REP_NUM
– and A.SIM_RUN_ID_FK > B.SIM_RUN_ID_FK
– and A.ELEMENT_NAME = B.ELEMENT_NAME);
–
-- create view JSL_DB.PW_DIFF_AR_REP_VIEW (SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME, AVG_A, STD_DEV_A,
– AVG_B, STD_DEV_B, AVG_DIFF_A_MINUS_B, STD_DEV_DIFF_A_MINUS_B, STAT_COUNT)
– as (select SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME, AVG(A_VALUE) as AVG_A, STDDEV_SAMP(A_VALUE) as STD_DEV_A,
– AVG(B_VALUE) as AVG_B, STDDEV_SAMP(B_VALUE) as STD_DEV_B,
– AVG(A_MINUS_B) as AVG_DIFF_A_MINUS_B, STDDEV_SAMP(A_MINUS_B) as STD_DEV_DIFF_A_MINUS_B,
– COUNT(A_MINUS_B) as STAT_COUNT
– from JSL_DB.PW_DIFF_WITHIN_REP_VIEW
– group by SIM_NAME, STAT_NAME, A_EXP_NAME, B_EXP_NAME, DIFF_NAME);