ISNULL function within select command of formview

debutante picture debutante · Nov 29, 2011 · Viewed 25.7k times · Source

This is part of my select statement within an formview which works fine till it hits a null value during the update process.

 (SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id = @id
 ORDER BY sort_no ) AS faxid

so i tried using the ISNULL function in the following way but it throws error. how can it be done?

ISNULL((SELECT TOP 1 F.tel_id FROM TELEPHONE as F where F.tel_type_id = 3 AND F.client_id= @id ORDER BY sort_no ) AS faxid ,0) AS faxid

Answer

Andriy M picture Andriy M · Nov 29, 2011

I think I've found the cause of the problem. And if that is really the one then I'm almost certain that someone else would have spotted it sooner than I did if you had formatted your second code snippet more nicely.

So, here goes your code snippet, slightly reformatted:

ISNULL(
  (
    SELECT TOP 1 F.tel_id
    FROM TELEPHONE as F
    where F.tel_type_id = 3
      AND F.client_id= @id
    ORDER BY sort_no
  ) AS faxid,
  0
) AS faxid

The highlighted part, the AS fixed bit immediately after the subquery, is erroneous, it just shouldn't be there. Probably you just overlooked it.