I have 2 tables Table1 and cvdata table. I need an sql to join the two tables to return 5 rows of data
TABLE1
FE_KEY ID
6-K-1201 512623
6-K-1410 512624
6-K-1411 512625
6-K-1430 512785
6-K-1201 512623
6-K-1410 512624
6-K-1411 512625
6-K-1430 512785
6-K-1450 512792
CVDATA
FE_ID CV_CODE CV_VALUE CV_UOM
512623 EST_ISENTROPIC_POWER 445 KW
512624 EST_ISENTROPIC_POWER 47000 KW
512625 EST_ISENTROPIC_POWER 30000 KW
512785 EST_ISENTROPIC_POWER 70456 KW
512623 MAX_TEMP_COMPRESSION 200 DegC
512624 MAX_TEMP_COMPRESSION 170 DegC
512625 MAX_TEMP_COMPRESSION 200 DegC
512785 MAX_TEMP_COMPRESSION 150 DegC
512792 MAX_TEMP_COMPRESSION 180 DegC
Expected output
FE_KEY MAX_TEMP_COMPRESSION MAX_TEMP_COMPRESSIONUOM EST_ISENTROPIC_POWER EST_ISENTROPIC_POWERUOM
6-K-1201 200 DegC 445 KW
6-K-1410 170 DegC 47000 KW
6-K-1411 200 DegC 30000 KW
6-K-1430 150 DegC 70456 KW
6-K-1450 180 DegC NULL NULL
When I use this script the last row that has two null values are not returned
SELECT PACERLIVE.FEMAST_TAG_ALL.FE_KEY,
CASE WHEN PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION" THEN PACERLIVE.R_CVDATA.CV_VALUE ELSE NULL END AS MAX_TEMP_COMPRESSION,
CASE WHEN PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION" THEN PACERLIVE.R_CVDATA.CV_UOM ELSE NULL END AS MAX_TEMP_COMPRESSIONUOM ,
CASE WHEN CVDATA1.CV_CODE = "EST_ISENTROPIC_POWER" THEN CVDATA1.CV_VALUE ELSE NULL END AS EST_ISENTROPIC_POWER,
CASE WHEN CVDATA1.CV_CODE = "EST_ISENTROPIC_POWER" THEN CVDATA1.CV_UOM ELSE NULL END AS EST_ISENTROPIC_POWERUOM
FROM PACERLIVE.FEMAST_TAG_ALL FULL OUTER JOIN PACERLIVE.R_CVDATA ON PACERLIVE.FEMAST_TAG_ALL.ID = PACERLIVE.R_CVDATA.FE_ID FULL OUTER JOIN PACERLIVE.R_CVDATA AS CVDATA1 ON PACERLIVE.FEMAST_TAG_ALL.ID = CVDATA1.FE_ID
WHERE (PACERLIVE.FEMAST_TAG_ALL.FE_KEY Like "6-K-%" AND (PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION") AND (CVDATA1.CV_CODE ="EST_ISENTROPIC_POWER"))
I get the result
FE_KEY MAX_TEMP_COMPRESSION MAX_TEMP_COMPRESSIONUOM EST_ISENTROPIC_POWER EST_ISENTROPIC_POWERUOM
6-K-1201 200 DegC 445 KW
6-K-1410 170 DegC 47000 KW
6-K-1411 200 DegC 30000 KW
6-K-1430 150 DegC 70456 KW
the last row with some null values is omitted.
6-K-1450 180 DegC NULL NULL
Please I need help on the script to output the expected result, including the row with some null values.