1

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.

1

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
1

This also works I see:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.