Cognos Report Studio（案例陈述）-语法错误 [英] Cognos Report Studio (case statement) - Syntax error

问题描述

我在Cognos report studio中有一个案例声明，其中，如果日期是上一年当前月份的1号，则它应获取上一年的最后一个月（1到最后日期）的数据。我认为这是语法错误。以下是我分享的代码。谢谢！

I have a case statement in Cognos report studio in which if the date is 1st of the current month of previous year then it should fetch data for last whole month (1 to the last date) data of the previous year. I think it is a syntax error. Below is the code that I'm sharing.Thanks in advance! Please let me know in case of concerns.

case when 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1))
then 
[Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] 
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1))
end

推荐答案

您的逻辑是正确的。但是，看起来您正在过滤器中使用CASE语句。 Cognos对过滤器中的CASE语法很挑剔。它要求您将条件和结果都放在括号中。尝试以下操作：

Your logic is correct. However, it looks like you are using a CASE statement in a filter. Cognos is picky about CASE syntax in filters. It requires you to put both the condition and the result in parentheses. Try this:

case when 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD] = _first_of_month(_add_years(current_date,-1)))
then 
([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]
  between
_first_of_month(_add_months(_add_years(current_date,-1),-1))
   and
_last_of_month (_add_months(_add_years(current_date,-1),-1)))
end

这里是另一种语法也应该起作用：

Here's an alternate syntax that should also work:

extract(year,current_date) - 1 = extract(year, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])
AND 
extract(month,current_date) = extract(month, [Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD])

==更正==

我注意到，尽管它被接受了。

我提供的情况会产生错误，因为条件引用的波动值随源行而变化。当我构造答案时，我将重点放在句法问题上，而不漏掉逻辑。为了使这种类型的逻辑起作用，在CASE WHEN之后的条件应该为整个查询引用单个值。这可以是用户提供的参数，也可以是返回整个查询的单个值的函数或常量，例如：

The CASE WHEN I provided will generate an error because the condition references fluctuating values that vary by source row. When I constructed the answer I focused on the syntactical problems without evaulating the logic. In order for this type of logic to work, the condition right after CASE WHEN should reference a single value for the entire query. This can be a user-supplied parameter or a function or constant returning a single value for the entire query, such as:

(year(current_date) = 2018)

或

(?param? = 'foo')

以下是一个非常简单的示例，可以在10.2中使用，并且可以用作模板：

The following is a very simple example that works in 10.2 and can be used as a template:

CASE 
WHEN (?param? = 'foo') 
THEN (extract(year,[Date]) = 2018)
ELSE (extract(year,[Date]) = 2017)
END

类似地，我的替代逻辑在形式上是正确的，但在逻辑上却不正确，因为我再次将逻辑从没有考试的问题。构造的逻辑永远不会返回true。修改后的更正版本如下：

_first_of_month(current_date) = current_date /* Today is the first day of the month */
AND month(_add_months(current_date,-1)) = month([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The month of the row matches the previous month */
AND year(_add_years(current_date,-1)) = year([Corporate Calendar_BL].[Receive Date Details].[Receive Date - RD]) /* The year of the row matches the previous year */

如果在第一天运行，它将为您提供去年上个月的所有数据任何一个月。如果在2018年11月1日运行它，则将获得2017年10月整个月的数据。如果在2018年1月1日运行，则将获得2016年12月整个月的数据，等等。

This will give you all of the previous month's data for last year if ran on the first day of any month. If you ran it in November 1, 2018, you'd get data for the entire month of October 2017. If you ran it January 1, 2018, you'd get data for the entire month of December 2016 etc.

这篇关于Cognos Report Studio（案例陈述）-语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！