我正在尝试使用企业管理器在 SQL Server 2000 中创建视图

Question

我正在尝试使用企业管理器在 SQL Server 2000 中创建一个视图，有时它不会返回任何行，当它不返回时，我需要它返回 0。 我已经尝试过此操作，但当我尝试保存它时，我不断收到此消息：

查询设计器不支持 IF SQL 构造。

IF EXISTS (SELECT     dbo.oehdrhst_sql.cus_no, dbo.oehdrhst_sql.inv_dt, dbo.oehdrhst_sql.inv_no, dbo.OELINHST_SQL.item_no, 
                                          dbo.OELINHST_SQL.item_desc_1, dbo.OELINHST_SQL.item_desc_2, dbo.OELINHST_SQL.qty_to_ship, dbo.IMITMIDX_SQL.user_def_fld_1, 
                                          dbo.IMITMIDX_SQL.user_def_fld_2, dbo.IMITMIDX_SQL.user_def_fld_3, dbo.IMITMIDX_SQL.user_def_fld_4, 
                                          dbo.IMITMIDX_SQL.user_def_fld_5, dbo.oehdrhst_sql.ord_type, dbo.oehdrhst_sql.orig_ord_type, dbo.OELINHST_SQL.qty_return_to_stk, 
                                          dbo.OELINHST_SQL.qty_to_ship * dbo.IMITMIDX_SQL.user_def_fld_2 AS extended, CONVERT(datetime, STR(dbo.oehdrhst_sql.inv_dt, 8, 0)) 
                                          AS [Date]
                    FROM         dbo.oehdrhst_sql INNER JOIN
                                          dbo.OELINHST_SQL ON dbo.oehdrhst_sql.ord_no = dbo.OELINHST_SQL.ord_no INNER JOIN
                                          dbo.IMITMIDX_SQL ON dbo.OELINHST_SQL.item_no = dbo.IMITMIDX_SQL.item_no
                    WHERE     (dbo.oehdrhst_sql.inv_dt BETWEEN 20100701 AND 20110630) AND (dbo.OELINHST_SQL.item_no BETWEEN '3065 0' AND 
                                          '3065 ZZZZZZZZZZ') AND (NOT (dbo.IMITMIDX_SQL.user_def_fld_2 IS NULL)))
SELECT     EXTENDED, cus_no, inv_dt, inv_no, item_no, item_desc_1, item_desc_2, qty_to_ship, user_def_fld_1, user_def_fld_2, [date]
 FROM         (SELECT     dbo.oehdrhst_sql.cus_no, dbo.oehdrhst_sql.inv_dt, dbo.oehdrhst_sql.inv_no, dbo.OELINHST_SQL.item_no, 
                                               dbo.OELINHST_SQL.item_desc_1, dbo.OELINHST_SQL.item_desc_2, dbo.OELINHST_SQL.qty_to_ship, 
                                               dbo.IMITMIDX_SQL.user_def_fld_1, dbo.IMITMIDX_SQL.user_def_fld_2, dbo.IMITMIDX_SQL.user_def_fld_3, 
                                               dbo.IMITMIDX_SQL.user_def_fld_4, dbo.IMITMIDX_SQL.user_def_fld_5, dbo.oehdrhst_sql.ord_type, dbo.oehdrhst_sql.orig_ord_type, 
                                               dbo.OELINHST_SQL.qty_return_to_stk, dbo.OELINHST_SQL.qty_to_ship * dbo.IMITMIDX_SQL.user_def_fld_2 AS extended, 
                                               CONVERT(datetime, STR(dbo.oehdrhst_sql.inv_dt, 8, 0)) AS [Date]
                        FROM          dbo.oehdrhst_sql INNER JOIN
                                               dbo.OELINHST_SQL ON dbo.oehdrhst_sql.ord_no = dbo.OELINHST_SQL.ord_no INNER JOIN
                                               dbo.IMITMIDX_SQL ON dbo.OELINHST_SQL.item_no = dbo.IMITMIDX_SQL.item_no
                        WHERE      (dbo.oehdrhst_sql.inv_dt BETWEEN 20100701 AND 20110630) AND (dbo.OELINHST_SQL.item_no BETWEEN '3065 0' AND 
                                               '3065 ZZZZZZZZZZ') AND (NOT (dbo.IMITMIDX_SQL.user_def_fld_2 IS NULL))) a
 GROUP BY EXTENDED, cus_no, inv_dt, inv_no, item_no, item_desc_1, item_desc_2, qty_to_ship, user_def_fld_1, user_def_fld_2, [date] ELSE
                            SELECT     EXTENDED = 0

Answer 1

您可以删除 IF EXISTS 部分并仅使用 ISNULL(EXTENDED,0)。但这假设了很多。

Answer 2

这不是您应该在视图中执行的操作。相反，当您调用视图时执行此操作，即：

IF (SELECT COUNT(*) FROM v_MyView) > 0
BEGIN
   SELECT * FROM v_MyView
END 
ELSE
SELECT 0