Nested If Statement and Syntax Check

[Cognos Peasent]

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?

Code Select Expand

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!

[BigChris]

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:

Code Select Expand

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

[Cognos Peasent]

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"

[BigChris]

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?

[Cognos Peasent]

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!