WITH CTE AS
(
WITH flattened_data AS
(
SELECT
DISTINCT
MAX(CASE WHEN (entry.value:Id::STRING)='123' THEN entry.value:Answer:text::STRING END) AS VD,
MAX(CASE WHEN (entry.value:Id::STRING)='3456' THEN entry.value:Answer:text::STRING END) AS DPL,
MAX(CASE WHEN (entry.value:Id::STRING)='8971' THEN entry.value:Answer:text::STRING END) AS DL_Date
FROM TABLE_FCA,
LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry
WHERE entry.value:Answer:text IS NOT NULL
AND entry.value:Id IN ('123','3456','8971')
GROUP BY COLUMN_TX
HAVING VD IS NOT NULL
)
SELECT VD,MAX(DPL) AS DPL ,MAX(DL_Date) AS DL_Date
FROM flattened_data GROUP BY VD
)
Select * from
TABLE_A A
Join TABLE_B B ON...
.
.
.
.
.
LEFT JOIN CTE ON CTE.VD=A.VD
The reason why it is done like this is beause the TABLE_FCA doesn't have a unique key and COLUMN_TX has a json value. There could be multiple json records having the same VD and could have differrent DL_Date. using a CTE inside a CTE and then using it in another JOIN feels like there could be an easier way which iam missing.
I tried using CTE inside a CTE. Iam not sure if there is a better way this could be done like using a temp table.
