CODEWITHSUNDEEP
sqloracle-databasemax
CreationSL
CreationSL

Reputation: 65

MAX function in oracle not pulling back expected results

I have table which has a bunch of data. A sample of the data is as emphasized textfollows:

DTE UNIQUE_ID ROW_DATA CALL_ROW_ID
8/1/2021 11234 DCNS=FAILED\|HYPO= 1
8/1/2021 11234 DCNS=ACCEPTED \| HYPO=NEW CREDIT 2
8/1/2021 11234 DCNS=FAILED\|HYPO=GIVE ME BIRTHDAY= 4
8/1/2021 11234 DCNS=ACCEPTED\|HYPO=YES 7
8/1/2021 11234 DCNS=CONFIRMED\|HYPO=JULY 25th 2019 10

I have a query that is trying to stack certain data in a single row. The query is trying to pull back: the date, unique_id, and get the first and last HYPO.

SELECT
    DTE,
    UNIQUE_ID,
    MIN(CASE WHEN HYPO > 0 THEN TRIM(SUBSTR(ROW_DATA, HYPO + 7, INSTR(ROW_DATA, '|', HYPO + 7) - (HYPO + 7))) END ) FIRST_HYPO,
    MAX(CASE WHEN HYPO > 0 THEN TRIM(SUBSTR(ROW_DATA, HYPO + 7, INSTR(ROW_DATA, '|', HYPO + 7) - (HYPO + 7))) END ) LAST_HYPO
FROM
    (SELECT DTE, UNIQUE_ID, ROW_DATA, INSTR(ROW_DATA, '| HYPO=') as HYPO
    FROM
        (SELECT TO_CHAR(DTE, 'MM/DD/YYYY' AS DTE, UNIQUE_ID, '| ' || ROW_DATA || ' |' as ROW_DATA, CALL_ROW_ID
        FROM DATATABLE))

What I am trying to get is :

DTE UNIQUE_ID FIRST_HYPO LAST_HYPO
8/1/2021 11234 NEW_CREDIT JULY 25th 2019

But what I am getting back is

DTE UNIQUE_ID FIRST_HYPO LAST_HYPO
8/1/2021 11234 GIVE ME BIRTHDAY YES

Its odd because the data is in order yet the MIN/MAX is choosing different rows. I tried to do a ORDER BY at all levels of the query and it doesn't fix the result. It always pulls back the same fields.

Does anyone know how to fix this?

Upvotes: 0

Views: 72

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

'YES' is the maximum hypo. It starts with a 'Y' and thus comes last in the alphabet. Assuming that you have a typo in your sample data accidentally omitting HYPO= in the row, 'GIVE ME BIRTHDAY' would be the minimum hypo. It starts with a 'G' and is the first in alphabetical order.

You say you want the first and last hypo instead and hoped Oracle would magically know what you consider first and last and apply MIN/MAX accordingly. I suppose what you have in mind is look at all rows with a hypo (call_row_id 2, 7, and 10) and of these consider the one with the lowest call_row_id first and the one with the greatest call_row_id last.

You get the first and last row's value in an aggregation with Oracle's KEEP FIRST/LAST.

select
  dte, unique_id,
  max(hypo) keep (dense_rank first order by nvl2(hypo, 1, 2), call_row_id) as first_hypo,
  max(hypo) keep (dense_rank last  order by nvl2(hypo, 2, 1), call_row_id) as last_hypo
from
(
  select
    dte, unique_id, call_row_id,
    trim(regexp_replace(row_data, '^.*HYPO=(.*)$', '\1')) as hypo
  from datatable
)
group by dte, unique_id
order by dte, unique_id;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ab99c199b1c3ce49a2029f0205700e14

Upvotes: 2

Related Questions