Can I use the expression IF to find more than one condition?

[camilooso]

Hello;


Please your support, I have to replace the data from one Expression in order to show it different in the report, but I have 174 different conditions to change to just three, I was using the following expression:

if ([House Bill].[HB Detailed].[Service Code] = 'CA')
then ('CAO')
else (if([House Bill].[HB Detailed].[Service Code] = 'CA0')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA1')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA2')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA3')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA4')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA5')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA6')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA7')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA8')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CA9')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CAO')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB' )
then ('CAO')
else (if([House Bill].[HB Detailed].[Service Code] = 'CB0')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB1')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB2')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB3')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB4')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB5')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB6')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB7')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB8')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CB9')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC' )
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC0')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC1')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC2')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC3')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC4')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC5')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC6')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC7')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC8')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CC9')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CCM')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD' )
then ('CAO')
else (if([House Bill].[HB Detailed].[Service Code] = 'CD0')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD1')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD2')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD3')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD4')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD5')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD6')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD7')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD8')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'CD9')
then ('CAO'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA1' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA2' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA3' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA4' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA5')
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA6' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA7' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA8' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AA9' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AAZ' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD1' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD2' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD3' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD4' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD5')
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD6' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD7' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD8' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'AD9' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'ADZ' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA1' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA2' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA3' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA4' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA5')
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA6')
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA7' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA8' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DA9' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DAZ' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD1' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD2' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD3' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD4' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD5')
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD6' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD7' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD8' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DD9' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DDZ' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DGD' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'DGR' )
then ('GC'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA0' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA1' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA2' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA3' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA4' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA5' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA6' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA7' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA8' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HA9' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HAA' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HAZ' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB0' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB1' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB2' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB3' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB4' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB5' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB6' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB7' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB8' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HB9' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC0' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC1' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC2' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC3' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC4' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC5' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC6' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC7' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC8' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HC9' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD0' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD1' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD2' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD3' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD4' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD5' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD6' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD7' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD8' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HD9' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HDJ' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'HDM' )
then ('PAX'))
else (if([House Bill].[HB Detailed].[Service Code] = 'PAX' )
then ('PAX'))
else (null)

end

But at the end when I validated the expression y received the error: QE-DEF-0459 CCLException and QE-DE-0260, Am I using this expression right?

Thank you!.

[sdf]

correct me if I am wrong, there's seems to be a pattern in your data.
Maybe you can use the STARTS WITH/ NOT STARTS WITH or LIKE/ NOT LIKE functions

say:
CASE
WHEN [Service Code] like 'C%' then ('CAO')
WHEN [Service Code] like 'A%' or [Service Code] like 'D%' then ('GC')
WHEN [Service Code] like 'H%' or [Service Code] like 'P%' then ('PAX')
else (null)
end

If the pattern is correct.
Else, I am thinking of creating sets for particular values.
then you can have something like

IF [Service Code] in [Service Code Set1] then ('CAO')
else if [Service Code] in [Service Code Set2] then ('GC')
...
else (null)
end


Also, depending on your datasource you can assign a label/attribute and use them in reports to show caption.
Just my idea..

[camilooso]

Thank you! that was really usefull