Hey guys
I'm building a report that tries to capture the fiscal quarter using current day and month.
I have built an impromptu report where I can manually input the day and month.
But I have no way of checking my syntax/balancedbrackets like I could on Toad for Oracle at my old job.
I'm trying to get Notepad++ on my machine to check the code but in my company things take a while to get processed.
Can anyone see anywhere I'm going wrong with my code?
if
([Day] <26)
then
(if
([Month] in (01,02,03))
then (1)
else
(if
([Month] in (04,05,06))
then (2)
else
(if
([Month] in (07,08,09))
then (3)
else
(if
([Month] in (10,11,12))
then (4)
else
(if ([Day] >26)
then
(if [Month] IN (01,02)
then (1)
else
(if
([Month] IN (04,05)
then (2)
else
(if
([Month] IN (07,08))
then (3)
else (
(if
([Month] IN (10,11))
then (4)
else
(if ([Month] = 03)
then (2)
else
(if ([Month] = 06)
then (3)
else
(if [Month] = 09)
then (4)
else
(if ([Month] = 12)
then (1))))))))))))))
Thanks!
I prefer case statements to convoluted if...then...else constructions - I just find them easier to read. Anyway, see if this does the job for you:
case when [Day] < 26 then
case
when [Month] in ('01', '02', '03') then 1
when [Month] in ('04', '05', '06') then 2
when [Month] in ('07', '08', '09') then 3
when [Month] in ('10', '11', '12') then 4
else 99
End
else
case
when [Month] in ('12', '01', '02') then 1
when [Month] in ('03', '04', '05') then 2
when [Month] in ('06', '07', '08') then 3
when [Month] in ('09','10', '11') then 4
else 99
End
End
Thanks Big Chris for your help!
Just tried to run the data item in tabular format but there was a parsing error
QE-DEF-0260
Parsing error before or near position: 429 of:
"case when [Day] < 26 then
case
when [Month] in ('01', '02', '03') then 1
when [Month] in ('04', '05', '06') then 2
when [Month] in ('07', '08', '09') then 3
when [Month] in ('10', '11', '12') then 4
else 99
End
else
case
when [Month] in ('12', '01', '02') then 1
when [Month] in ('03', '04', '05') then 2
when [Month] in ('06', '07', '08') then 3
when [Month] in ('09','10', '11') then 4
else 99
End"
I've just created some test data and run my function over it and it worked ok...the snip you posted didn't appear to have the last End - did you miss that off the code?
Your absolutely right looks like I missed the last END off the code. Works perfectly now just had to turn the numbers into integers on the IN statements.
I'll let you know if I encounter any other issues but thanks for your help!