6 Replies Latest reply: Jan 28, 2013 12:39 AM by Naren

# Complex logic

Hi

I have used Analytical functions but couldnt reach the requirment.

I have a requirement where i need to consider a set as two columns and for that set need to check two exactly available combinations.

findings are to be done partition by ID and CD and for that paritition need to find exactly available values say 'Paid' and 'AdjD'. If that partition set holds more than these two values or less than these two values then those records should not be picked.
Example if the partition set has 'Paid','AdjD', 'AdjV' then these this set should not be considered, as it violates the exactly two distinct records match.

below is the sample set:

<br>

WITH T AS
(

SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 230 ID, 444 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 230 ID, 444 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 230 ID, 444 CD, 'AdjC' NM FROM DUAL
UNION ALL
SELECT 230 ID, 491 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 6180917 ID, 444 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 6180917 ID, 444 CD, 'AdjC' NM FROM DUAL
UNION ALL
SELECT 6180917 ID, 444 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 6180917 ID, 491 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 11099353 ID, 444 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 11099353 ID, 491 CD, 'AdjV' NM FROM DUAL
UNION ALL
SELECT 11099353 ID, 491 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 13093371 ID, 444 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 13093371 ID, 444 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 13093371 ID, 444 CD, 'AdjC' NM FROM DUAL
UNION ALL
SELECT 13093371 ID, 491 CD, 'AdjD' NM FROM DUAL
UNION ALL
SELECT 13093371 ID, 491 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 32139609 ID, 444 CD, 'Paid' NM FROM DUAL
UNION ALL
SELECT 32139609 ID, 444 CD, 'AdjV' NM FROM DUAL
UNION ALL
SELECT 32139609 ID, 491 CD, 'Paid' NM FROM DUAL
)
select * FROM T

From above sample below records needs to be picked up.

ID     CD     NM
7     491     Paid
13093371     491     Paid

</br>

I am using oracle 10g.

Thanks,
Naren
• ###### 1. Re: Complex logic
Maybe NOT TESTED!
``````select id,cd,nm
from (select id,cd,nm,
sum(case when nm = 'Paid' then 1 end) over (partition by id,cd order by null
rows between unbounded preceding and unbounded following
) n_paid,
sum(case when nm = 'AdjD' then 1 end) over (partition by id,cd order by null
rows between unbounded preceding and unbounded following
sum(case when nm not in ('Paid','AdjD') then 1 end) over (partition by id,cd order by null
rows between unbounded preceding and unbounded following
) n_other
from t
)
where n_paid = 1
and n_other is null``````
Regards

Etbin
• ###### 2. Re: Complex logic
``````WITH T AS (
SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 230 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 230 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 230 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
SELECT 230 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 6180917 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 6180917 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
SELECT 6180917 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 6180917 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 11099353 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 11099353 ID, 491 CD, 'AdjV' NM FROM DUAL UNION ALL
SELECT 11099353 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 13093371 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 13093371 ID, 444 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 13093371 ID, 444 CD, 'AdjC' NM FROM DUAL UNION ALL
SELECT 13093371 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
SELECT 13093371 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 32139609 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
SELECT 32139609 ID, 444 CD, 'AdjV' NM FROM DUAL UNION ALL
SELECT 32139609 ID, 491 CD, 'Paid' NM FROM DUAL
)
SELECT  ID,
CD,
NM
FROM  (
SELECT  T.*,
SUM(DISTINCT CASE NM WHEN 'Paid' THEN 1 WHEN 'AdjD' THEN 2 ELSE 4 END) OVER(PARTITION BY ID,CD) X
FROM  T
)
WHERE X = 3
/

ID         CD NM
---------- ---------- ----
7        491 Paid
13093371        491 Paid

SQL>  ``````
SY.

Edited by: Solomon Yakobson on Jan 25, 2013 12:37 PM
• ###### 3. Re: Complex logic
Hi,

This is similar to Etbin's solution, but a little more concise:
``````WITH     got_cnts   AS
(
SELECT     id, cd, nm
,     COUNT (DISTINCT nm
) OVER (PARTITION BY  id, cd)     AS total_cnt
,     COUNT (DISTINCT CASE
THEN  nm
END
)     OVER (PARTITION BY  id, cd)     AS target_cnt
FROM    t
)
SELECT    id, cd, nm
FROM       got_cnts
WHERE       total_cnt     = 2
AND       target_cnt     = 2
;``````
The output is what you requested:
```````       ID         CD NM
---------- ---------- ----
7        491 Paid
13093371        491 Paid``````
COUNT, like most of the other analytic functions, does not require an ORDER BY clause. I fnd it clearer, as well as easier, just to omit the ORDER BY clause when it's not needed.
• ###### 4. Re: Complex logic
Just a note, this will work assuming NM is NOT NULL. Otherwise it will return ID, CD combinations whis=ch have NM 'AdjD', 'Paid' and NULL.
``````SQL> WITH T AS (
2             SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
3             SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
4             SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
5             SELECT 7 ID, 491 CD, NULL NM FROM DUAL
6            ),
7  got_cnts   AS
8  (
9   SELECT id, cd, nm
10   , COUNT (DISTINCT nm
11           ) OVER (PARTITION BY  id, cd) AS total_cnt
12   , COUNT (DISTINCT CASE
13          WHEN  nm IN ('AdjD', 'Paid')
14          THEN  nm
15            END
16          ) OVER (PARTITION BY  id, cd) AS target_cnt
17   FROM    t
18  )
19  SELECT    id, cd, nm
20  FROM   got_cnts
21  WHERE   total_cnt = 2
22  AND   target_cnt = 2
23  /

ID         CD NM
---------- ---------- ----
7        491 Paid
7        491

SQL> WITH T AS (
2             SELECT 7 ID, 444 CD, 'Paid' NM FROM DUAL UNION ALL
3             SELECT 7 ID, 491 CD, 'Paid' NM FROM DUAL UNION ALL
4             SELECT 7 ID, 491 CD, 'AdjD' NM FROM DUAL UNION ALL
5             SELECT 7 ID, 491 CD, NULL NM FROM DUAL
6            )
7  SELECT  ID,
8          CD,
9          NM
10    FROM  (
11           SELECT  T.*,
12                   SUM(DISTINCT CASE NM WHEN 'Paid' THEN 1 WHEN 'AdjD' THEN 2 ELSE 4 END) OVER(PARTITION BY ID,CD) X
13             FROM  T
14          )
15    WHERE X = 3
16  /

no rows selected

SQL> ``````
SY.
• ###### 5. Re: Complex logic
Thanks Alll :) this worked
• ###### 6. Re: Complex logic
How were the rows selected?
What was the logic?