HANA-SQL How to get a minimum excluding null vallues?

54 Views Asked by At

I am interested in a start time of a set of packages that can be started or not started. I want the start time of the first started package. So the minimum start time, but not initial start time. To make things more complex, there are several sets of packages which are defined by a combination of run_id + step_id. So I have a pool of multiple runs each with multiple steps each with multiple packages.

lt_pkg_rtimes =    SELECT *,                                -- some more fields
                       MIN("EXEC_STARTTIME") OVER
                         ( PARTITION BY "RUN_ID","STEP_ID" ORDER BY "RUN_ID","STEP_ID"  ) --has to be 2nd minimum
                       AS STEP_TSTMAP_START,
                       FROM :lt_pkgdata          --contains all start times, including initial
                       ORDER BY "RUN_ID",
                         "STEP_ID";

So, I tried to replace lt_pkgdata with lt_pkg_min which I create like follows.

    --finding second minimum execution start time
    lt_pkg_started = SELECT * FROM :lt_pkgdata AS pkg WHERE pkg.exec_starttime > 0;

    --Only if there is no 2nd minimum per run+step, also include the 1st minimum
    lt_pkg_min = SELECT
                     client,
                     run_id,
                     step_id,
                     CASE
                       WHEN exec_starttime > 0
                       THEN ( SELECT MIN("EXEC_STARTTIME")
                                FROM :lt_pkg_started AS std
                                WHERE std.client = client       -- this where does not work like I need it
                                  AND std.run_id = run_id
                                  AND std.step_id = step_id )
                     ELSE
                       exec_starttime
                     END AS exec_starttime,
                   FROM :lt_pkgdata;

The result of my try is that the second minimum of all the packages of all run_id + step_id combinations is used. But I need it per run_id + step_id combination.

Also, I am working in an AMDP (SAP HANA SQL).

0

There are 0 best solutions below