SELECT
NBG.Ref_Well, a.WELL AS Nbg_Well, ROUND(NBG.D,2) AS D,MAX(MARKER) as MARKER,
DECODE (MAX(MARKER), '1b', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 1b"
, DECODE(MIN(MARKER), '1c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 1c", DECODE(MIN(MARKER), '1e', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 1e", DECODE(MIN(MARKER), '1h-2', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 1h_2", DECODE(MIN(MARKER), '2a', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2a", DECODE(MIN(MARKER), '2a-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2a_1", DECODE(MIN(MARKER), '2b', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2b", DECODE(MIN(MARKER), '2b-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2b_1", DECODE(MIN(MARKER), '2c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2c", DECODE(MIN(MARKER), '2c-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2c_1", DECODE(MIN(MARKER), '2d', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2d", DECODE(MIN(MARKER), '2d-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2d_1", DECODE(MIN(MARKER), '2e', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2e", DECODE(MIN(MARKER), '2f', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2f", DECODE(MIN(MARKER), '2g', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2g", DECODE(MIN(MARKER), '2h', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 2h", DECODE(MIN(MARKER), '3a', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3a", DECODE(MIN(MARKER), '3b', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3b", DECODE(MIN(MARKER), '3b-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3b_1", DECODE(MIN(MARKER), '3c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3c", DECODE(MIN(MARKER), '3d', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3d", DECODE(MIN(MARKER), '3e', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 3e", DECODE(MIN(MARKER), '4a', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4a", DECODE(MIN(MARKER), '4b', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4b", DECODE(MIN(MARKER), '4b-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4b_1", DECODE(MIN(MARKER), '4c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4c", DECODE(MIN(MARKER), '4d', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4d", DECODE(MIN(MARKER), '4e', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4e", DECODE(MIN(MARKER), '4f', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4f", DECODE(MIN(MARKER), '4g', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4g", DECODE(MIN(MARKER), '4g-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4g_1", DECODE(MIN(MARKER), '4h', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4h", DECODE(MIN(MARKER), '4h-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4h_1", DECODE(MIN(MARKER), '4i', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4i", DECODE(MIN(MARKER), '4j', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4j", DECODE(MIN(MARKER), '4k', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4k", DECODE(MIN(MARKER), '4k-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 4k_1", DECODE(MIN(MARKER), '5a', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5a", DECODE(MIN(MARKER), '5a-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5a_1", DECODE(MIN(MARKER), '5a-2', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5a_2", DECODE(MIN(MARKER), '5b', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5b", DECODE(MIN(MARKER), '5b-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5b_1", DECODE(MIN(MARKER), '5b-2', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5b_2", DECODE(MIN(MARKER), '5c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5c", DECODE(MIN(MARKER), '5c-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5c_1", DECODE(MIN(MARKER), '5c-2', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5c_2", DECODE(MIN(MARKER), '5d', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5d", DECODE(MIN(MARKER), '5e', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5e", DECODE(MIN(MARKER), '5f', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5f", DECODE(MIN(MARKER), '5g', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5g", DECODE(MIN(MARKER), '5g-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5g_1", DECODE(MIN(MARKER), '5h', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5h", DECODE(MIN(MARKER), '5h-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5h_1", DECODE(MIN(MARKER), '5i', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5i", DECODE(MIN(MARKER), '5i-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 5i_1", DECODE(MIN(MARKER), '6a', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 6a", DECODE(MIN(MARKER), '6c', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 6c", DECODE(MIN(MARKER), '6c-1', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 6c_1", DECODE(MIN(MARKER), '6d', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER 6d", DECODE(MIN(MARKER), 'MF0', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER MF0", DECODE(MIN(MARKER), 'MFB_field', CASE WHEN MIN(RESERVOIR)=MAX(Reservoir) THEN MIN(RESERVOIR) ELSE MIN(RESERVOIR) || '-' || MAX(Reservoir) END, '-') "MARKER MFB_field"
--,MAX(DECODE(MARKER,'1b','TRUE','FALSE')) "MARKER 1b"
FROM
(
SELECT
LOC_WELL_HEADER.Short_Name AS Ref_Well,
LOC_WELL_HEADER_1.Short_Name AS Nbg_Well,
LOC_WELL_HEADER_1.WELL_TYPE,
POWER((LOC_WELL_HEADER.BOTTOM_X-LOC_WELL_HEADER_1.BOTTOM_X),2) AS DX2,
POWER((LOC_WELL_HEADER.BOTTOM_Y-LOC_WELL_HEADER_1.BOTTOM_Y),2) AS DY2,
ROUND(SQRT(POWER((LOC_WELL_HEADER.BOTTOM_X-LOC_WELL_HEADER_1.BOTTOM_X),2)+POWER((LOC_WELL_HEADER.BOTTOM_Y-LOC_WELL_HEADER_1.BOTTOM_Y),2)),5) AS D,
LOC_WELL_HEADER_1.Bottom_X,
LOC_WELL_HEADER_1.Bottom_Y,
LOC_WELL_HEADER.Field_Name,
TO_CHAR(LOC_WELL_HEADER_1.STARTUP_EFFECTIVE,'YYYY') AS STARTUP_EFFECTIVE
FROM
(
SELECT DV.FIELD_NAME AS Field_Name, DV.WELL AS Short_Name, DV.X_COORD_TARGET AS Bottom_X, DV.Y_COORD_TARGET AS Bottom_Y,
DECODE(DV.STARTUP_EFFECTIVE,NULL,DV.STARTUP_INITIAL,DV.STARTUP_EFFECTIVE) AS STARTUP_EFFECTIVE, DV.GTS_NAME, DV.WELL_TYPE
FROM DVIEW_WELL_HEADER DV LEFT JOIN MAHAKAM.WELL_HDR@FINDER95 wh ON DV.WELL = wh.SHORT_NAME
WHERE DV.FIELD_NAME='TUNU'
ORDER BY DV.FIELD_INDEX, DV.WELL
)
LOC_WELL_HEADER,
(
SELECT DV.FIELD_NAME AS Field_Name, DV.WELL AS Short_Name, DV.X_COORD_TARGET AS Bottom_X, DV.Y_COORD_TARGET AS Bottom_Y,
DECODE(DV.STARTUP_EFFECTIVE,NULL,DV.STARTUP_INITIAL,DV.STARTUP_EFFECTIVE) AS STARTUP_EFFECTIVE, DV.GTS_NAME, DV.WELL_TYPE
FROM DVIEW_WELL_HEADER DV LEFT JOIN MAHAKAM.WELL_HDR@FINDER95 wh ON DV.WELL = wh.SHORT_NAME
WHERE DV.FIELD_NAME='TUNU'
ORDER BY DV.FIELD_INDEX, DV.WELL
)
LOC_WELL_HEADER_1
WHERE (LOC_WELL_HEADER.Short_Name='ML-1')
AND (LOC_WELL_HEADER.Field_Name='TUNU')
AND (LOC_WELL_HEADER_1.Bottom_X IS NOT NULL)
AND (LOC_WELL_HEADER_1.Bottom_Y IS NOT NULL)
AND (SQRT(POWER(LOC_WELL_HEADER.BOTTOM_X-LOC_WELL_HEADER_1.BOTTOM_X,2)+POWER(LOC_WELL_HEADER.BOTTOM_Y-LOC_WELL_HEADER_1.BOTTOM_Y,2))) <=3500
ORDER BY LOC_WELL_HEADER_1.Short_Name
) NBG INNER JOIN DVIEW_GEO_RESERVOIR_CHART_ALL a ON NBG.Nbg_Well = a.WELL
WHERE (DATE_PERFO IS NOT NULL) --AND NBG_WELL='ML-1'
AND (TOTAL_HPM>0)
AND MARKER IN ('1b', '1c', '1e', '1h-2', '2a', '2a-1', '2b', '2b-1', '2c', '2c-1', '2d', '2d-1', '2e', '2f', '2g', '2h', '3a', '3b', '3b-1', '3c', '3d', '3e', '4a', '4b', '4b-1', '4c', '4d', '4e', '4f', '4g', '4g-1', '4h', '4h-1', '4i', '4j', '4k', '4k-1', '5a', '5a-1', '5a-2', '5b', '5b-1', '5b-2', '5c', '5c-1', '5c-2', '5d', '5e', '5f', '5g', '5g-1', '5h', '5h-1', '5i', '5i-1', '6a', '6c', '6c-1', '6d', 'MF0', 'MFB_field')
--AND MARKER IN('5f')
--GROUP BY MARKER
GROUP BY NBG.Ref_Well,a.WELL, NBG.D--,MARKER
--ORDER BY NBG.D
