从 DB2 到 SQL Server 2005 的视图

发布于 2024-11-27 10:58:02 字数 579 浏览 6 评论 0原文

我正在尝试在 DB2 和 SQL Server 之间移动视图。

CREATE VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr 
  WHERE  ( bus_cnty_cntry_cd, bus_st ) IN (SELECT cnty_cntry_cd, 
                                                  st 
                                           FROM   uhelp.cnty_cntry_cd 
                                           WHERE 
         cnty_cntry_descr LIKE '%invalid%');

该视图适用于 DB2，但由于存在 WHERE 子句而不适用于 SQL Server。我能否就如何重写此视图以与 SQL Server 一起使用提供建议？

原文

I'm attempting to move a view between DB2 and SQL Server.

CREATE VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr 
  WHERE  ( bus_cnty_cntry_cd, bus_st ) IN (SELECT cnty_cntry_cd, 
                                                  st 
                                           FROM   uhelp.cnty_cntry_cd 
                                           WHERE 
         cnty_cntry_descr LIKE '%invalid%');

The view works in DB2, but doesn't work with SQL Server because of the WHERE clause. Can I have a recommendation on how to rewrite this view to work with SQL Server?

分享到QQ

分享到微博

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

发布评论

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

旧梦荧光笔 2024-12-04 10:58:02

它通常有助于定义“不起作用”的含义（例如您遇到了什么错误）以及指定您正在使用的 SQL Server 版本。

不幸的是，SQL Server 不支持具有多个子句的 IN()。但是您可以这样重写您的视图：

ALTER VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr AS mstr 
  WHERE EXISTS 
  (
     SELECT 1
        FROM uhelp.cnty_cntry_cd 
        WHERE cnty_cntry_descr LIKE '%invalid%'
         AND cnty_cntry_cd = mstr.bus_cnty_cntry_cd
         AND st = mstr.bus_st
  );

It usually helps to define what "doesn't work" means (e.g. what error did you get) and also to specify the version of SQL Server you are using.

Unfortunately SQL Server doesn't support IN() with more than one clause. However you can re-write your view this way:

ALTER VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr AS mstr 
  WHERE EXISTS 
  (
     SELECT 1
        FROM uhelp.cnty_cntry_cd 
        WHERE cnty_cntry_descr LIKE '%invalid%'
         AND cnty_cntry_cd = mstr.bus_cnty_cntry_cd
         AND st = mstr.bus_st
  );

回复收藏 0 原文

月竹挽风 2024-12-04 10:58:02

单程

CREATE VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr m
  WHERE  EXISTS( SELECT 1 FROM   uhelp.cnty_cntry_cd  c
  WHERE c.cnty_cntry_descr LIKE '%invalid%'
  AND c.bus_cnty_cntry_cd = m.bus_cnty_cntry_cd
  AND c.st = m.bus_st)

one way

CREATE VIEW msu.bad_bus_cnty_st_mstr 
AS 
  SELECT id, 
         bus_cnty_cntry_cd, 
         bus_st, 
         bus_zip 
  FROM   summit.mstr m
  WHERE  EXISTS( SELECT 1 FROM   uhelp.cnty_cntry_cd  c
  WHERE c.cnty_cntry_descr LIKE '%invalid%'
  AND c.bus_cnty_cntry_cd = m.bus_cnty_cntry_cd
  AND c.st = m.bus_st)

回复收藏 0 原文

~没有更多了~