Oracle calculates the maximum value horizontally (2)

Based on the previous time, add null value judgment:

1. Direct judgment

Select case when S1 is null then S1 when S2 is null then S1 when S3 is null then greatest (S1, S2) When S4 is null then greatest (S1, S2, S3) when S5 is null then greatest (S1, S2, S3, S4) When S6 is null then greatest (S1, S2, S3, S4, S5) when S6 is not null then greatest (S1, S2, S3, S4, S5, S6) end as a from test;

2. Write the judgment condition as a function call

Create or replace function max_value (S1 varchar2, S2 varchar2, S3 varchar2, S4 varchar2, S5 varchar2, S6 varchar2)
Return varchar2
Is
-- Returns the maximum value of a string.
V_max varchar2 (20 );
Begin

V_max: = case when S1 is null then S1
When S2 is null then S1
When S3 is null then greatest (S1, S2)
When S4 is null then greatest (S1, S2, S3)
When S5 is null then greatest (S1, S2, S3, S4)
When S6 is null then greatest (S1, S2, S3, S4, S5)
When S6 is not null then greatest (S1, S2, S3, S4, S5, S6)
End;
Return v_max;
End max_value;

Call method:

Select max_value (S1, S2, S3, S4, S5, S6) as a from test;

The above is for your reference only!