Informix-'||'操作员无法通过 ODBC 连接工作

发布于 2024-09-25 13:23:51 字数 673 浏览 3 评论 0原文

我正在 Reporting Services 2005 中构建一个报告，其中的数据是从 Informix 数据库检索的。与数据库的连接是通过 ODBC 声明的。这是该查询的更简单版本：

select
    prodtype,
    familynum,
    family,
    sum(invested) invested,
    month(recevdate) month,
    year(recevdate) year,
    day(recevdate) day,
    'All Year' const
from
    sales_product
where
    (region not in ('15876','15852')) and
    (prodtype in ('4','7','50','1')) and
    (recevdate >= ('01/01/' || (year(?) - 1))) and
    (recevdate <= Date('12/31/' || (year(?) - 1)))
 group by 1,2,3,5,6,7

如果您查看 where 子句，您将看到我从参数中获取年份，然后将其添加到字符串月份和日期。这里的问题是“||”运算符直接在 Informix 中工作，但不能通过 ODBC 工作。当我执行此查询时，出现语法错误。还有其他方法可以连接两个字符串吗？

原文

I am building a report in Reporting Services 2005, where the data is retrieved from an Informix database. The connection to the database is declared via ODBC. Here is the simpler version of the query:

select
    prodtype,
    familynum,
    family,
    sum(invested) invested,
    month(recevdate) month,
    year(recevdate) year,
    day(recevdate) day,
    'All Year' const
from
    sales_product
where
    (region not in ('15876','15852')) and
    (prodtype in ('4','7','50','1')) and
    (recevdate >= ('01/01/' || (year(?) - 1))) and
    (recevdate <= Date('12/31/' || (year(?) - 1)))
 group by 1,2,3,5,6,7

If you look at the where clause, you will see that I am taking the year from the parameter and then adding it to a string month and day. The problem here is that the '||' operator works directly in Informix, but not over ODBC. When I execute this query, I get a syntax error. Is there any other way to concatenate two strings?

分享到QQ

分享到微博

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

发布评论

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

晨曦慕雪 2024-10-02 13:23:51

在这种情况下，我认为您最好的选择是使用 MDY() 函数而不是字符串连接：

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, YEAR(?) - 1) AND MDY(12, 31, YEAR(?) - 1))
 GROUP BY 1,2,3,5,6,7

我假设 ?占位符值是完整的 DATE 值，因此需要 YEAR 函数。如果您传入您感兴趣的确切年份数字，您可以简化查询：

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, ?) AND MDY(12, 31, ?))
 GROUP BY 1,2,3,5,6,7

至于为什么字符串连接“失败”......尚不清楚。然而，MDY() 函数的优点之一是它的参数是明确的，独立于区域设置（客户端和服务器区域设置）。造成问题的一个可能原因是 Reporting Services 设置（或未设置）的日期格式与您在查询中强制设置的日期格式不同，并且与您直接执行查询时设置的格式不同。这是一个猜测——一个看似合理但绝不是确定的猜测。另一种可能性是您只传递参考日期一次，即使它在查询的条件子句中使用了两次。如果我们给出了错误消息，我们也许能够更好地猜测问题的根源。

In the circumstances, I think your best choice is to use the MDY() function instead of string concatenation:

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, YEAR(?) - 1) AND MDY(12, 31, YEAR(?) - 1))
 GROUP BY 1,2,3,5,6,7

I am assuming that the ? placeholder value is a full DATE value, so the YEAR function is needed. You can simplify the query if you pass in the exact year number that you are interested in:

SELECT prodtype, familynum, family,
       sum(invested) invested,
       month(recevdate) month,
       year(recevdate) year,
       day(recevdate) day,
       'All Year' const
  FROM sales_product
 WHERE (region NOT IN ('15876','15852'))
   AND (prodtype in ('4','7','50','1'))
   AND (recevdate BETWEEN MDY(1, 1, ?) AND MDY(12, 31, ?))
 GROUP BY 1,2,3,5,6,7

As to why the string concatenation is 'failing'...that is not clear. However, one of the advantages of the MDY() function is that its arguments are unambiguous independent of the locale (both client and server locales). One possible cause of your trouble is that the date format set by (or not set by) Reporting Services is different from the one you are forcing in your query - and different from the one that is set when you execute the query directly. That's a guess - a plausible but by no means definitive guess. Another possibility is that you only pass the reference date once, even though it is used twice in the condition clause of the query. If we had the error message given, we might be able to make a better guess at the source of the trouble.

回复收藏 0 原文

瘫痪情歌 2024-10-02 13:23:51

尝试使用 + 进行 concat 或者类似 strcat()

回复收藏 0 原文

九公里浅绿 2024-10-02 13:23:51

我想我找到了解决该问题的方法...而不是使用 ||运算符我想到使用替换功能。因此，我使用 (recevdate >= Date('0101' + Year(?))) 而不是 (recevdate >= Date(Replace('01/01/1900', '1900',year(?)-1))) 并且它起作用了。我仍然对更好的方法持开放态度，但在那之前我会继续这样做。

回复收藏 0 原文