Need help with null field values

advantagenull

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the text in field 142 unless field 142 is null, then update with field 17. Here is the existing code:

--Defendant Address
update xrji_:Usernum 
    set DefendantAddress=trim(MemoText)
from eqanswer
where eqanswer.entitynum=xrji_:Usernum.DefendantEntityNum and
                       entityrole='DEFENDANT' and fieldnum=142
;

Columns MemoText, entityrole and fieldnum belong to the eqanswer table.

Let me know if this needs further clarification.
Any suggestions would be appreciated.

DBMS is: Advantage.

Best Answer

The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part:

set <field_to_update> = ISNULL(<field142>, <field17>)

The ISNULL function will evaluate to <field142> if that value is not null, otherwise the function will evaluate to <field17>.

So is it possible to arrange two values from different rows on the same row? Yes, there are at least two ways to do so.

First, you could replace the eqanswer reference with a derived table that groups eqanswer rows by entitynum and uses conditional aggregation to present field 142 and field 17 values on the same row, like this:

SELECT
  entitynum,
  MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
  MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
FROM
  eqanswer
WHERE
  entityrole = 'DEFENDANT'
  AND fieldnum IN (142, 17)
GROUP BY
  entitynum

Now you can use the above query in the UPDATE statement like this:

UPDATE
  xrji_:Usernum
SET
  DefendantAddress = TRIM(ISNULL(eqa.MemoText142, eqa.MemoText17))
FROM
  (
    SELECT
      entitynum,
      MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
      MAX(CASE fieldnum WHEN 17  THEN MemoText END) AS MemoText17
    FROM
      eqanswer
    WHERE
      entityrole = 'DEFENDANT'
      AND fieldnum IN (142, 17)
    GROUP BY
      entitynum
  ) AS eqa
WHERE
  eqa.entitynum = xrji_:Usernum.DefendantEntityNum
;

The other method would require joining the eqanswer table to xrji_:Usernum twice: once to get the field 142 MemoText and once again to get the field 17 MemoText. For this it would be more convenient to rewrite UPDATE to use an explicit join. The complete statement would look something like this:

UPDATE
  xun
SET
  DefendantAddress = TRIM(ISNULL(eqa142.MemoText, eqa17.MemoText))
FROM
  xrji_:Usernum AS xun
  LEFT JOIN eqanswer AS eqa142 ON eqa142.entitynum = xun.DefendantEntityNum
    AND eqa142.entityrole = 'DEFENDANT'
    AND eqa142.fieldnum = 142
  LEFT JOIN eqanswer AS eqa17 ON eqa17.entitynum = xun.DefendantEntityNum
    AND eqa17.entityrole = 'DEFENDANT'
    AND eqa17.fieldnum = 17
WHERE
  eqa142.entitynum IS NOT NULL
  OR eqa17.entitynum IS NOT NULL
;

You can see that this UPDATE statement uses the same ISNULL pattern as the previous UPDATE does. The difference is only in how the MemoText values from different rows manage to get on the same row. This time they simply come from different, independently joined subsets of eqanswer.

The query uses outer joins instead of inner joins because of the assumption that field 142 or field 17 may be absent for any given entitynum. The query still checks that at least one of the two rows is present – otherwise the corresponding xrji_:Usernum row is not updated at all. If you still would like to update such rows (with a null, apparently), just remove the WHERE clause.

Related Question