CODEWITHSUNDEEP
sqlsql-server
shockwave
shockwave

Reputation: 3262

SQL- Case statement with PARTITION by

How to check if an ID has more than one distinct row? In the below table VBELN 15233 has two VB_TYPE. I need to check if a VBELN has 'p' and 'R' then condition1 . VBELN 25344 has only R, so my else logic will have - if(VBELN does not contain p) then condition2

+-------+---------+
| VBELN | VB_TYPE |
+-------+---------+
| 15233 | p       |
+-------+---------+
| 15233 | R       |
+-------+---------+
| 25344 | R       |
+-------+---------+
| 26455 | p       |
+-------+---------+
| 26455 | R       |
+-------+---------+

I have tried with the below case statement, but I'm not able to think beyond this logic.

SQL:

CASE WHEN (Count(VBELN) OVER (PARTITION BY VBELN))=2  THEN CONDITION 1
     ELSE CONDITION 2
     END

Is there a better logic than count()?

Upvotes: 0

Views: 3800

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Your code is fine if the types are not repeated.

(CASE WHEN (Count(VBELN) OVER (PARTITION BY VBELN)) = 2
      THEN CONDITION 1
      ELSE CONDITION 2
 END)

If you have two types, then this is closer to what you want:

(CASE WHEN MIN(VB_TYPE) OVER (PARTITION BY VBELN) <> MAX(VB_TYPE) OVER (PARTITION BY VBELN)
      THEN CONDITION 1
      ELSE CONDITION 2
 END)

If there are other types, then the logic can be done but is a bit more complicated.

Upvotes: 1

Related Questions