formview的select命令中的ISNULL函数

发布于 2024-12-18 16:18:20 字数 397 浏览 5 评论 0原文

这是我在表单视图中选择语句的一部分，它工作正常，直到在更新过程中达到空值为止。

 (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

所以我尝试按以下方式使用 ISNULL 函数，但它抛出错误。怎么办呢？

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小耗子 2024-12-25 16:18:21

你的 ISNULL 需要在 F.tel_id 附近

尝试

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

Your ISNULL needs to be around F.tel_id

Try

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

回复收藏 0 原文

另类 2024-12-25 16:18:21

您可以使用 COALESCE 函数返回 null 以外的内容。因此，当您的查询执行并有一个值时，就会返回该值，否则会返回您指定的值。

回复收藏 0 原文

不回头走下去 2024-12-25 16:18:20

我想我已经找到问题的原因了。如果这确实是这样，那么我几乎可以肯定，如果您更好地格式化第二个代码片段，其他人会比我更早发现它。

因此，这里是您的代码片段，稍微重新格式化：

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

突出显示的部分，即子查询之后紧随其后的 ASfixed 位，是错误的，它不应该在那里。也许只是你忽略了它。

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.

回复收藏 0 原文

~没有更多了~