Sort Order using Case Statement

[Quigwam2058]

I have created a query calculation that is a case statement that is used to group account numbers for a list report.  I also want to sort those groups in a custom order, so I also have a case statement that used to list the groups according to the order I want them to appear:
case
when [Unit Group] = 'Enhancement' then 'a'
when [Unit Group] = 'Business Office' then 'b'
when [Unit Group] = 'Human Resources' then 'c'
when [Unit Group] = 'Maintenance & Operations' then 'd'
when [Unit Group] = 'M&O Split' then 'e'
when [Unit Group] = 'Public Information' then 'f'
when [Unit Group] = 'Board of Education' then 'g'
when [Unit Group] = 'Superintendent' then 'h'
when [Unit Group] = 'GSRP' then 'i'
when [Unit Group] = 'SW MiTech' then 'j'
when [Unit Group] = 'S4S/GSC' then 'k'
when [Unit Group] = 'Print Center' then 'l'
when [Unit Group] = 'RTSI' then 'm'
when [Unit Group] = 'EFE' then 'n'
when [Unit Group] = 'EFA' then 'o'
when [Unit Group] = 'ISCS' then 'p'
when [Unit Group] = 'Head Start' then 'q'
when [Unit Group] = 'YOU' then 'r'
when [Unit Group] = 'Fund 22 Operations & Maintenance' then 's'
when [Unit Group]  = 'Fund 22 Payout for Local Programs' then 't'
when [Unit Group] = 'Fund 22 MPSRS UAAL' then 'u'
when [Unit Group] = 'Fund 22 Other' then 'v'
when [Unit Group] = 'Fund 22 YAP' then 'w'
when [Unit Group] = 'Fund 22 Juvenile Home' then 'x'
when [Unit Group] = 'Fund 22 PET' then 'y'
when [Unit Group] = 'Fund 22 WELC' then 'z'
when [Unit Group] = 'Fund 22 Valley Center' then 'z1'
when [Unit Group] = 'Fund 22 Deaf/HI' then 'z2'
when [Unit Group] = 'Fund 22 Other Grants' then 'z3'
when [Unit Group] = 'Fund 22 Cent Svcs' then 'z4'
when [Unit Group] = 'Fund 22 Early On/ECSE' then 'z5'
when [Unit Group] = 'Fund 26 Maint. & Operations' then 'z6'
when [Unit Group] = 'Fund 26 Career Connect' then 'z7'
when [Unit Group] = 'Fund 26 CTD' then 'z8'
Else 'Other'
end

The above expression works when I run the preview the query with just the items in red or just the items in green, but not if I try to run it all together. 
I am receiving the following error:  I have tried running this with one additional line at a time, but continue get an error. 

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-239'.
UDA-SQL-0460 A general exception has occurred during local processing.
There is an ExpEng message system failure at eemisc.cpp:191
RSV-SRV-0042 Trace back:
RSReportService.cpp(734): QFException: CCL_CAUGHT: RSReportService::pro

[BigChris]

Complete shot in the dark, but is there a character limit on the length of a calculation? That line in your calculation takes you over 800 characters - that might be a red herring though. To keep your sort sequence in the right order, I'd be tempted to alter it to

Code Select Expand

case
when [Unit Group] = 'Enhancement' then '01'
when [Unit Group] = 'Business Office' then '02'
when [Unit Group] = 'Human Resources' then '03'
when [Unit Group] = 'Maintenance & Operations' then '04'
when [Unit Group] = 'M&O Split' then '05'
when [Unit Group] = 'Public Information' then '06'
when [Unit Group] = 'Board of Education' then '07'
when [Unit Group] = 'Superintendent' then '08'
when [Unit Group] = 'GSRP' then '09'
when [Unit Group] = 'SW MiTech' then '10'
when [Unit Group] = 'S4S/GSC' then '11'
when [Unit Group] = 'Print Center' then '12'
when [Unit Group] = 'RTSI' then '13'
when [Unit Group] = 'EFE' then '14'
when [Unit Group] = 'EFA' then '15'
when [Unit Group] = 'ISCS' then '16'
when [Unit Group] = 'Head Start' then '17'
when [Unit Group] = 'YOU' then '18'
when [Unit Group] = 'Fund 22 Operations & Maintenance' then '19'
when [Unit Group] = 'Fund 22 Payout for Local Programs' then '20'
when [Unit Group] = 'Fund 22 MPSRS UAAL' then '21'
when [Unit Group] = 'Fund 22 Other' then '21'
when [Unit Group] = 'Fund 22 YAP' then '23'
when [Unit Group] = 'Fund 22 Juvenile Home' then '24'
when [Unit Group] = 'Fund 22 PET' then '25'
when [Unit Group] = 'Fund 22 WELC' then '26'
when [Unit Group] = 'Fund 22 Valley Center' then '27'
when [Unit Group] = 'Fund 22 Deaf/HI' then '28'
when [Unit Group] = 'Fund 22 Other Grants' then '29'
when [Unit Group] = 'Fund 22 Cent Svcs' then '30'
when [Unit Group] = 'Fund 22 Early On/ECSE' then '31'
when [Unit Group] = 'Fund 26 Maint. & Operations' then '32'
when [Unit Group] = 'Fund 26 Career Connect' then '33'
when [Unit Group] = 'Fund 26 CTD' then '34'
Else '99'
end

[Quigwam2058]

This is partially correct.  I ended up having to break out this sort order field into a new query and join it.  Since the Unit Group was also defined in the same query, cognos couldn't interpret the unit group case statement at the same time as the sort order case statement - if that makes sense.

[MFGF]

I have created a query calculation that is a case statement that is used to group account numbers for a list report.  I also want to sort those groups in a custom order, so I also have a case statement that used to list the groups according to the order I want them to appear:
case
when [Unit Group] = 'Enhancement' then 'a'
when [Unit Group] = 'Business Office' then 'b'
when [Unit Group] = 'Human Resources' then 'c'
when [Unit Group] = 'Maintenance & Operations' then 'd'
when [Unit Group] = 'M&O Split' then 'e'
when [Unit Group] = 'Public Information' then 'f'
when [Unit Group] = 'Board of Education' then 'g'
when [Unit Group] = 'Superintendent' then 'h'
when [Unit Group] = 'GSRP' then 'i'
when [Unit Group] = 'SW MiTech' then 'j'
when [Unit Group] = 'S4S/GSC' then 'k'
when [Unit Group] = 'Print Center' then 'l'
when [Unit Group] = 'RTSI' then 'm'
when [Unit Group] = 'EFE' then 'n'
when [Unit Group] = 'EFA' then 'o'
when [Unit Group] = 'ISCS' then 'p'
when [Unit Group] = 'Head Start' then 'q'
when [Unit Group] = 'YOU' then 'r'
when [Unit Group] = 'Fund 22 Operations & Maintenance' then 's'
when [Unit Group]  = 'Fund 22 Payout for Local Programs' then 't'
when [Unit Group] = 'Fund 22 MPSRS UAAL' then 'u'
when [Unit Group] = 'Fund 22 Other' then 'v'
when [Unit Group] = 'Fund 22 YAP' then 'w'
when [Unit Group] = 'Fund 22 Juvenile Home' then 'x'
when [Unit Group] = 'Fund 22 PET' then 'y'
when [Unit Group] = 'Fund 22 WELC' then 'z'
when [Unit Group] = 'Fund 22 Valley Center' then 'z1'
when [Unit Group] = 'Fund 22 Deaf/HI' then 'z2'
when [Unit Group] = 'Fund 22 Other Grants' then 'z3'
when [Unit Group] = 'Fund 22 Cent Svcs' then 'z4'
when [Unit Group] = 'Fund 22 Early On/ECSE' then 'z5'
when [Unit Group] = 'Fund 26 Maint. & Operations' then 'z6'
when [Unit Group] = 'Fund 26 Career Connect' then 'z7'
when [Unit Group] = 'Fund 26 CTD' then 'z8'
Else 'Other'
end

The above expression works when I run the preview the query with just the items in red or just the items in green, but not if I try to run it all together. 
I am receiving the following error:  I have tried running this with one additional line at a time, but continue get an error. 

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-239'.
UDA-SQL-0460 A general exception has occurred during local processing.
There is an ExpEng message system failure at eemisc.cpp:191
RSV-SRV-0042 Trace back:
RSReportService.cpp(734): QFException: CCL_CAUGHT: RSReportService::pro

Hi,

I'm wondering if the expression is too long for the database connection you are using? I see a UDA error in there, which means you are using Compatible Query - is it a connection via native client or are you using an ODBC connection?

One thing you might try is using a simple case construct rather than the search case construct you are using currently, eg instead of

Code Select Expand

case
when [Unit Group] = 'Enhancement' then '01'
when [Unit Group] = 'Business Office' then '02'
...
else '99'
end

try

Code Select Expand

case [Unit Group]
when 'Enhancement' then '01'
when 'Business Office' then '02'
...
else '99'
end

It might shorten the expression enough to work?

Cheers!

MF.