Informix – Case Statement Using Characters for Sum Value

informix

I have a table that I am trying to pull a single return value on a select saying that either compressions on table is enabled or disabled by using a sum calculator for column value compressed must be No and auto_compressed must equal Disabled.

Here is example data in the table:

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname
collate          TBLSpace
dbsnum           1
compressed       No
auto_compressed  Disabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       No
auto_compressed  Enabled

partnum          1048577
dbsname          rootdbs
owner            informix
tabname          sysdatabases
collate
dbsnum           1
compressed       Yes
auto_compressed  Disabled

You can use the below to test:

create table test (
partnum         varchar(255),
dbsname         varchar(255),
owner           varchar(255),
tabname         varchar(255),
collate         varchar(255),
dbsnum          varchar(255),
compressed      varchar(255),
auto_compressed varchar(255));

insert into test values ('1048577','rootdbs','informix','','TBLSpace','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Disabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','No','Enabled');
insert into test values ('1048577','rootdbs','informix','sysdatabases','','1','Yes','Disabled');

I only want to determine at the end of the day if compressed = No or auto_compressed = Disabled for all records in the database then I want a single return saying disabled or enabled.

I put this together, but I am struggling to replace numbers with values:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 'Enabled' else 'Disabled' end) as Condition
from test

I get this error message below:

367: Sums and averages cannot be computed for character columns.

I can replace the values with numbers and it works like below:

select
sum(case when Compressed != 'No' OR auto_compressed != 'Disabled' then 1 else 0 end) as seven
from test

I believe my case statement is incorrect or there is a better way of doing this. I do need assistance as my mind is boggling at this point.

Best Answer

Test

SELECT CASE SUM(CASE WHEN Compressed = 'Yes' AND auto_compressed = 'Compressed'
                     THEN 1 
                     ELSE 0 
                     END)
       WHEN 0 THEN 'Disabled'
       ELSE        'Enabled'
       END AS Condition
FROM test