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

问题描述

我在 Cognos 报表工作室中有一个案例陈述，如果日期是上一年当前月份的第一天，那么它应该获取上一年最后一个整月(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])

==CORRECTION==

我注意到我的回答虽然被接受，但并不完全正确.请参阅下面的说明.

我提供的 CASE WHEN 将产生错误，因为条件引用了因源行而异的波动值.当我构建答案时，我专注于语法问题而不评估逻辑.为了使这种类型的逻辑起作用，紧跟在 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屋！