Code it better

I've got a table with 3 pairs of values. Both values in a pair can be null/blank. I want to return rows that have one value in a pair null/blank and one value populated. I've included what I've got but it seems kind of clunky to me. Thanks in advance for any ideas.

Code:

---

  (ISNULL(PER2a,'') =  '' and ISNULL(PER2b,'') <> '')
OR (ISNULL(PER2a,'') <> '' and ISNULL(PER2b,'') =  '')
OR (ISNULL(PER3a,'') =  '' and ISNULL(PER3b,'') <> '')
OR (ISNULL(PER3a,'') <> '' and ISNULL(PER3b,'') =  '')
OR (ISNULL(PER4a,'') =  '' and ISNULL(PER4b,'') <> '')
OR (ISNULL(PER4a,'') <> '' and ISNULL(PER4b,'') =  '')

---

Yeah that does look a bit clunky.

How about

Code:

---

Where (pera2a is null and pera2b is not null) or (pera2a is not null and pera2b is null)

---

maybe something like

Code:

---

  coalesce(PER2a,PER2b,'itsnulla')=coalesce(PER2b,PER2a,'itsnullb')
or coalesce(PER3a,PER3b,'itsnulla')=coalesce(PER3b,PER3a,'itsnullb')
or coalesce(PER4a,PER4b,'itsnulla')=coalesce(PER4b,PER4a,'itsnullb')

---

it should work if the key is never equal to the value and no key/value is equal to 'itsnulla'/'itsnullb' (but it's in your Hand to choose some other value to use if both are NULL)
however, i'm not sure if its more readable than yours or datamisers...

Post #2 does not work since blank and null are not treated the same (as you are doing yourself).

#3 is more complicated, imo.

Stick with what you have - it looks clear to me.

Quote:

---

it looks clear to me

---

Agreed. It's verbose but that doesn't make it unclear.

In fact I might be tempted to make it even more verbose by removing the isnulls and testing for nulls and blanks separately:-

Code:

---

Where ((PER2a is null or PER2a = '') and PER2b is not null and PER2b != '')
Or (PER2a is not null and PER2a != '' and (PER2b is null or PER2b = '')
...

---

The reason I would consider that is because the use of isnull may well be preventing the optimiser from using appropriate indexes on those fields (you'd have to test it to be sure).

yeah, i realized ist not that readable, but i got one more ;)
how about:

Code:

---

WHERE
        (NOT (PER2a IS NULL AND PER2b IS NULL) AND PER2a+PER2b IS NULL)
OR  (NOT (PER3a IS NULL AND PER3b IS NULL) AND PER3a+PER3b IS NULL)
OR  (NOT (PER4a IS NULL AND PER4b IS NULL) AND PER4a+PER4b IS NULL)

---

but the concat might make it slow on big tables...

arghh, it would Need extra stuff for '', so gets more complicated again :(
stick with what you have :P

Quote:

---

Originally Posted by FunkyDexter

...The reason I would consider that is because the use of isnull may well be preventing the optimiser from using appropriate indexes on those fields (you'd have to test it to be sure).

---

Not sure indexes and nulls mix in real life much...

What I like about your code is that it's not using any SCALAR functions - just EQUAL and NOT EQUAL stuff which is so compile-able into clear operational instructions to the engine.

Not like the speed involved here would make a difference...

Quote:

---

Originally Posted by wild_bill

I've got a table with 3 pairs of values. Both values in a pair can be null/blank. I want to return rows that have one value in a pair null/blank and one value populated. I've included what I've got but it seems kind of clunky to me. Thanks in advance for any ideas.
...

---

I'm not a database person, but this sounds like it might be an XOR situation.
Can both values be populated, and do you want to return the row if they are, or only return the row when they are different (i.e one null and one not).
XOR returns True when the two values are different.
False Xor False = False
True Xor True = False
False Xor True = True
True Xor False = True

Code:

---

  (ISNULL(PER2a,'') =  '' xor ISNULL(PER2b,'') = '')
OR (ISNULL(PER3a,'') =  '' xor ISNULL(PER3b,'') = '')
OR (ISNULL(PER4a,'') =  '' xor ISNULL(PER4b,'') = '')

---

I'm assuming the code above is not valid syntax for a query, but is the principle.

Seems that T-SQL does not have an XOR logical operator - people try to trick it up with the BITWISE XOR of "^" (caret).