CODEWITHSUNDEEP
sqlsql-serversql-server-2005case-when
benerawr
benerawr

Reputation: 5

Why is the field empty when I use a multiple case when?

I have this SQL Code:

SELECT (  P1.Name
    + (CASE P1.Vorname WHEN '' THEN '' ELSE ', ' + P1.Vorname END)
    + (CASE (AP1.Postleitzahl + AP1.Ort)
          WHEN '' THEN ''
          ELSE ' - ' + (AP1.Postleitzahl + AP1.Ort)
       END)
    + (CASE (PKT1.Kennung + PKM1.Kennung)
          WHEN ''
          THEN
             ''
          ELSE
               ' - '
             + (CASE PKT1.Kennung
                   WHEN '' THEN ''
                   ELSE 'Tel: ' + PKT1.Kennung
                END)
             + (CASE PKM1.Kennung
                   WHEN '' THEN ''
                   ELSE ' Mobil: ' + PKM1.Kennung
                END)
       END))
      AS [IO1] FROM XYZ

Now when one field is not filled, the cell is empty. When everything is filled with data the cell gets filled.

So what is the Problem with that code?

I have to work with MSSQL Server 2005 so CONCAT or whatever will not work.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is your query:

SELECT (P1.Name +
        (CASE P1.Vorname WHEN '' THEN '' ELSE ', ' + P1.Vorname END) +
        (CASE (AP1.Postleitzahl + AP1.Ort) WHEN '' THEN '' ELSE ' - ' + (AP1.Postleitzahl + AP1.Ort) END) + 
        (CASE (PKT1.Kennung + PKM1.Kennung) WHEN '' THEN '' ELSE ' - ' + (CASE PKT1.Kennung WHEN '' THEN '' ELSE 'Tel: ' + PKT1.Kennung END) +
        (CASE PKM1.Kennung WHEN '' THEN '' ELSE ' Mobil: ' + PKM1.Kennung END) END)
       ) AS [IO1]
FROM XYZ;

It is going to produce NULL if any of the arguments are NULL. Presumably one or more of the columns is NULL. You can fix this by wrapping things in coalesce() and/or changing the case statements. For instance:

SELECT (coalesce(P1.Name, '') +
        (CASE WHEN P1.Vorname = '' or P1.Vorname is null THEN '' ELSE ', ' + P1.Vorname END) +
        (CASE WHEN (AP1.Postleitzahl + AP1.Ort) = '' or (AP1.Postleitzahl + AP1.Ort) is null THEN '' ELSE ' - ' + (AP1.Postleitzahl + AP1.Ort) END) + 
        (CASE WHEN (PKT1.Kennung + PKM1.Kennung) = '' or (PKT1.Kennung + PKM1.Kennung)  is null THEN '' ELSE ' - ' + (CASE WHEN PKT1.Kennung = '' or PKT1.Kennung  is null THEN '' ELSE 'Tel: ' + PKT1.Kennung END) +
        (CASE WHEN PKM1.Kennung  = '' or PKM1.Kennung is null THEN '' ELSE ' Mobil: ' + PKM1.Kennung END) END)
       ) AS [IO1]
FROM XYZ;

Upvotes: 1

Related Questions