Hi, I'm getting an error when trying to
Hi, I'm getting an error when trying to create a materialised view in Neon. The error only occurs in neon and not in other postgres databases. Can you help? Error details below :-
CREATE MATERIALIZED VIEW results_per_castor_record_mv AS
SELECT
*
FROM
crosstab(
'select a.participant_id, b.list_name , COALESCE(lv.value_to_display,c.text_value) as value
from (select patient_id, participant_id from patients) a
CROSS JOIN (select distinct lov.list_name, lov.list_of_values_id, cftd.field_order as field_order from castor_fields_to_display cftd, list_of_values lov
where lov.list_of_values_id = cftd.list_of_values_id) b
LEFT OUTER JOIN castor_patient_data_import c
ON c.patient_id = a.patient_id
AND b.list_of_values_id = c.list_of_values_id
--WHERE participant_id = 901544
LEFT OUTER JOIN list_values lv
ON lv.list_of_values_id = b.list_of_values_id AND lv.value_key = c.text_value
order by 1, b.field_order'
) AS ct (
participant_id BIGINT,
"CDM_eligible" TEXT,
"site_abbreviation" TEXT,
"CDM_institute" TEXT,
"country_diagnosis" TEXT,
"pilot_or_main_study" TEXT,
"diagnosis_date" TEXT,
"age" TEXT,
"gender" TEXT,
"immuno" TEXT,
"immuno_other" TEXT
)
and the error I get is :
ERROR: relation "patients" does not exist
LINE 2: from (select patient_id, participant_id from patients) a
although I am able to select * from patients.
CREATE MATERIALIZED VIEW results_per_castor_record_mv AS
SELECT
*
FROM
crosstab(
'select a.participant_id, b.list_name , COALESCE(lv.value_to_display,c.text_value) as value
from (select patient_id, participant_id from patients) a
CROSS JOIN (select distinct lov.list_name, lov.list_of_values_id, cftd.field_order as field_order from castor_fields_to_display cftd, list_of_values lov
where lov.list_of_values_id = cftd.list_of_values_id) b
LEFT OUTER JOIN castor_patient_data_import c
ON c.patient_id = a.patient_id
AND b.list_of_values_id = c.list_of_values_id
--WHERE participant_id = 901544
LEFT OUTER JOIN list_values lv
ON lv.list_of_values_id = b.list_of_values_id AND lv.value_key = c.text_value
order by 1, b.field_order'
) AS ct (
participant_id BIGINT,
"CDM_eligible" TEXT,
"site_abbreviation" TEXT,
"CDM_institute" TEXT,
"country_diagnosis" TEXT,
"pilot_or_main_study" TEXT,
"diagnosis_date" TEXT,
"age" TEXT,
"gender" TEXT,
"immuno" TEXT,
"immuno_other" TEXT
)
and the error I get is :
ERROR: relation "patients" does not exist
LINE 2: from (select patient_id, participant_id from patients) a
although I am able to select * from patients.
