CODEWITHSUNDEEP
sqlcase
Spurious
Spurious

Reputation: 1995

SQL simplifying case in case in case

I got the following SQL code (part of a select Statement):

Case
      When HilfsTab2.Gruppe = 'HST' And Basis.Breite_FLA = Basis.Breite Then 0
      Else Case When HilfsTab2.Gruppe = 'SA' Or HilfsTab2.Gruppe = 'HO / TB' Or
        HilfsTab2.Gruppe = 'PR' Then 0 Else Case
          When HilfsTab2.Gruppe Is Null Then -1 Else 1 End End
    End As IsHST_Fluegel

Now, I run this over a table of several million entries. From my understanding, SQL checks the first case when for all rows, then the second for all entries and so on. This takes ages. Now I was thinking, there needs to be an easier way to do this.

I was thinking of a stored procedure / custom function that basically outputs -1, 0 or 1 depending on the entry.

Thanks in advance

Upvotes: 0

Views: 116

Answers (2)

Steve Chambers
Steve Chambers

Reputation: 39424

For a possible speed improvement, do the NULL check first, the column comparison last and refactor to remove the nested CASE:

CASE WHEN HilfsTab2.Gruppe IS NULL
     THEN -1
     WHEN HilfsTab2.Gruppe IN ('SA', 'HO / TB', 'PR')
       OR (HilfsTab2.Gruppe = 'HST' AND Basis.Breite_FLA = Basis.Breite)
     THEN 0
     ELSE 1
END AS IsHST_Fluegel

Upvotes: 1

roman
roman

Reputation: 117380

Your case could be simplified as:

Case
      When HilfsTab2.Gruppe = 'HST' And Basis.Breite_FLA = Basis.Breite Then 0
      When HilfsTab2.Gruppe in ('SA', 'HO / TB', 'PR') Then 0
      When HilfsTab2.Gruppe Is Null Then -1
      Else 1
End As IsHST_Fluegel

But this will not speed up your query. If you want to select millions of rows, it would take time anyway.

Upvotes: 0

Related Questions