Hey I've got a report working on fiscal years and I have a working prototype of the expression definition here
case
when [Month] in (01,02)
then [Year]-1
when [Month] = 03
then
case
when [Day] < 26
then [Year]-1
else [Year]
end
else [Year]
end
Now I'm trying to replicate this code and put it into my query which looks like the following
case
when extract(month,current_date) in (01,02)
then ([Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)-1)
when extract(month,current_date) = 03
then
case
when extract(day,current_date) < 26
then ([Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)-1)
else ([Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date))
end
else ([Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date))
end
Yet I get an error that says
UDA-QOS-0006 Error
UDA-SQL-0358 Line 233: Syntax error near "=".
Any ideas guys? I read the IBM document but it didn't help
http://www-01.ibm.com/support/docview.wss?uid=swg21420771
Thanks!
Try this:
(
extract(month,current_date) in (01,02)
and [Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)-1
)
OR
(
extract(month,current_date) = 03
and /*your logic for day <26 or >= 26*/
)
OR
(
[Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)
)
You'll find a lot of posts on Cognoise, many by Lynn, on the correct way to write filters.
We owe a lot to George Boole (https://en.wikipedia.org/wiki/George_Boole)
;D
QuoteWe owe a lot to George Boole
Nice one, Lynn :)
Thank you for your answer! I'll have to check Lynn's posts out in the future!
I'm now having a new error which says
QE-DEF-0459 CCLException
QE-DEF-0260 Parsing error near...
(
extract(month,current_date) in (01,02)
and [Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)-1
)
OR
(
extract(month,current_date) = 03
and extract(day,current_date) <26
)
OR
(
[Relational Layer].[Gaap Reporting Period].[Gaap Reporting Year] = extract(year,current_date)
)