We need to write a SQL query that returns the first non-null value in the set, or NULL if all values are null.
The FIRST_VALUE is an analytic function seems to be the right function.
Unfortunately the example below does not select the right values:
with
test
as
(
SELECT 4110 ACP_COD_GUARANTEE_KEY, NULL ACP_COD_ESTATE_TYPE_FK from dual
UNION ALL
SELECT 4111 ACP_COD_GUARANTEE_KEY, NULL ACP_COD_ESTATE_TYPE_FK from dual
UNION ALL
SELECT 4112 ACP_COD_GUARANTEE_KEY, '002' ACP_COD_ESTATE_TYPE_FK from dual
UNION ALL
SELECT 4112 ACP_COD_GUARANTEE_KEY, '002' ACP_COD_ESTATE_TYPE_FK from dual
UNION ALL
SELECT 4112 ACP_COD_GUARANTEE_KEY, '002' ACP_COD_ESTATE_TYPE_FK from dual
UNION ALL
SELECT 4112 ACP_COD_GUARANTEE_KEY, '003' ACP_COD_ESTATE_TYPE_FK from dual
)
select test.*
, FIRST_VALUE( acp_cod_estate_type_fk IGNORE NULLS) OVER ( ORDER BY acp_cod_guarantee_key ASC ) AS first_value_cod_estate_type
from test
;
The right result set should be
What's wrong?