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