3

The case statement below should be retrieving data whenever CL.TYPE = DIAG.TYPE Then DIAG.TYPE is true and if not not then NULL. Does this make sense? Or Is this the most logical way to do this? 

(CASE
WHEN CL.TYPE2 = DIAG.TYPE
OR CL.TYPE3 = DIAG.TYPE 
OR CL.TYPE4 = DIAG.TYPE 
OR CL.TYPE5 = DIAG.TYPE 
OR CL.TYPE6 = DIAG.TYPE 
OR CL.TYPE7 = DIAG.TYPE 
OR CL.TYPE8 = DIAG.TYPE         THEN SUBSTRING(DIAG.TYPE,2,1)
                                ELSE NULL
END)
EkansDasnakE
  • 67
  • 1
  • 10
  • But this query shows that there are 7 `CL.TYPE` columns. So that's whay it's using a `CASE` *expression* – Lamak Sep 27 '16 at 19:17
  • 1
    The query makes sense but it highlights some problematic issues in your table design. You have what is known as repeating groups and it violates 1NF. Consider how painful it would be if you need to add a Type9. You would have to change your table and every single query that touches it. If this was properly normalized you would not have to change anything. – Sean Lange Sep 27 '16 at 19:20
  • if these are claims diagnosis types, see if you can get your administrator to normalize the columns for you. – Beth Sep 27 '16 at 20:27

1 Answers1

4

Your query seems fine, you could use in to reduce OR repetition to make your query more readable: 

(CASE
WHEN  DIAG.TYPE in (CL.TYPE2, CL.TYPE3, CL.TYPE4, CL.TYPE5, CL.TYPE6, CL.TYPE7, CL.TYPE8)  
THEN SUBSTRING(DIAG.TYPE,2,1)
ELSE NULL
END)
Lamak
  • 69,480
  • 12
  • 108
  • 116
EoinS
  • 5,405
  • 1
  • 19
  • 32