CODEWITHSUNDEEP
sqloracle-databasecasewhere-clause
user3335535
user3335535

Reputation: 35

Conditional Where?

Using the following table. I am trying to get the Party_ID. I will have just the NEW_ORG_ID and ORIGINAL_ID.

Using the NEW_ORG_ID I want to get the PARTY_ID with the MAX(USR_CRTE_TS) unless the ORIGINAL_ID matches as well. Then I want the PARTY_ID where the NEW_ORG_ID and ORIGINAL_ID match.

PARTY_ID          |DIRS_ID  |SUPPLIER_ID|ORIGINAL_ID|TRIANGLE_ID|NEW_ORG_ID|USR_CRTE_TS        |
------------------|---------|-----------|-----------|-----------|----------|-------------------|
5385606962        |476800490|5385606962 |NULL       |NULL       |69214683  |2018-09-27 04:56:13|
69214683          |476800490|NULL       |2740793553 |NULL       |69214683  |2018-09-27 04:54:15|

I tried the following. Keep in mind I hardcoded the NEW_ORG_ID and ORIGINAL_ID for this example only. In the real world example NEW_ORG_ID and ORIGINAL_ID are supplied by another part of the query.

    SELECT DISTINCT PARTY_ID 
FROM UNIQUE_IDS UNQ 
WHERE   
( 
        CASE
            WHEN ( 
                    SELECT ts.PARTY_ID
                    FROM UNIQUE_IDS ts 
                    WHERE (ts.NEW_ORG_ID = '69214683' AND ts.ORIGINAL_ID = '2740793553') 
                 ) IS NOT NULL
            THEN 
                (
                    SELECT ts.PARTY_ID 
                    FROM UNIQUE_IDS ts 
                    WHERE (ts.NEW_ORG_ID = '69214683' AND ts.ORIGINAL_ID = '2740793553') 
                )
            ELSE 
                (
                    SELECT ts.PARTY_ID
                    FROM UNIQUE_IDS ts 
                    WHERE ts.NEW_ORG_ID = '69214683'
                    AND ts.USR_CRTE_TS = (  
                                            SELECT MAX(ts.USR_CRTE_TS) 
                                            FROM UNIQUE_IDS ts 
                                            WHERE ts.NEW_ORG_ID = '69214683'
                                        )--xref.SBL_GLOB_CSTMR_ID)
                )
        END
) = UNQ.NEW_ORG_ID

;

Upvotes: 0

Views: 58

Answers (2)

trincot
trincot

Reputation: 351403

You can use the ROW_NUMBER() analytic function to get the "best" match (first by ORIGINAL_ID, and then by USR_CRTE_TS) marked with number 1, and filter on that:

SELECT PARTY_ID
FROM   (
            SELECT  PARTY_ID,
                    USR_CRTE_TS,  
                    ROW_NUMBER() OVER (
                        PARTITION BY PARTY_ID 
                        ORDER BY     CASE ORIGINAL_ID 
                                         WHEN '2740793553' THEN 0 
                                         ELSE 1 
                                     END,
                                     USR_CRTE_TS DESC
                    ) RN
            FROM    UNIQUE_IDS
            WHERE   NEW_ORG_ID = '69214683'
        )
WHERE   RN = 1

If USR_CRTE_TS can be NULL, then add another condition at the very end to be in line with what you had:

AND     (ORIGINAL_ID = '2740793553' OR USR_CRTE_TS IS NOT NULL)

When the criteria are selected elsewhere

You can use the WITH clause to build up your query, layer by layer. So let's assume you already have working logic to select the two "parameters", then put that in a with clause like this:

WITH CRIT(SEL_ORG_ID, SEL_ORIGINAL_ID) AS (
    SELECT '69214683', '2740793553' FROM DUAL -- this is just an example
)

... and then continue the query like this:

), BASE AS (
    SELECT  UI.PARTY_ID,
            UI.USR_CRTE_TS,
            UI.ORIGINAL_ID,
            CRIT.SEL_ORIGINAL_ID,
            ROW_NUMBER() OVER (
                PARTITION BY UI.PARTY_ID 
                ORDER BY     CASE UI.ORIGINAL_ID 
                                 WHEN CRIT.SEL_ORIGINAL_ID THEN 0 
                                 ELSE 1 
                             END,
                             UI.USR_CRTE_TS DESC
            ) RN
    FROM    UNIQUE_IDS UI,
            CRIT
    WHERE   UI.NEW_ORG_ID = CRIT.SEL_NEW_ORG_ID
)
SELECT PARTY_ID
FROM   BASE
WHERE  RN = 1
AND    (ORIGINAL_ID = SEL_ORIGINAL_ID OR USR_CRTE_TS IS NOT NULL)

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

I would use row_number() with conditional ordering:

select party_id 
  from (
    select party_id, 
           row_number() over (order by case when original_id = '2740793553' then 1 end, 
                                       usr_crte_ts desc) rn
      from unique_ids unq
      where new_org_id = '69214683')
  where rn = 1

dbfiddle demo

If original_id matches then row has higher priority. Second priority is highest usr_crte_ts. Finally we take first best row.

Upvotes: 0

Related Questions