What I want to achieve is to create a new column based on the num_extents between a range and only print the table name below.
This is what I have [table1]:
This is what I want [table2]:
I used a case statement that allows to get values between a range using the below:
select sum(case when num_of_extents between 0 and 9 then 1 else 0 end) as one,
sum(case when num_of_extents between 10 and 19 then 1 else 0 end) as two,
sum(case when num_of_extents between 20 and 29 then 1 else 0 end) as three,
sum(case when num_of_extents between 30 and 39 then 1 else 0 end) as four,
sum(case when num_of_extents between 40 and 49 then 1 else 0 end) as five,
sum(case when num_of_extents >= 50 then 1 else 0 end) as six
from (
select
a.tabname,
count(*) num_of_extents,
sum( pe_size ) total_size_pg,
round(sum( pe_size*4),2) total_size_kb,
round(sum( pe_size*4/1024 ),2) total_size_mb,
round(sum( pe_size *4/1024/1024),2) total_size_gb
from systabnames a, sysptnext b, sysdatabases c
where a.partnum = b.pe_partnum
and a.dbsname = c.name
and a.dbsname = 'system'
-- a.tabname not like ' %'
group by 1
order by 3 desc, 4 desc
) a
Output from above:
one 2
two 1
three 1
four 5
five 0
six 2
Please ignore the naming of the columns used, but instead of doing a sum between the range, I want to print out the names of the tables instead like in [table2]. How can I change my sql case statement to do this in informix?
Best Answer
I figured out the answer to my question. The below works fine
Output shows like below within dbaccess:
Please note that I did not use the same table data as above. The above is just an example of how the information will be presented within dbaccess. Due to the number of columns formatting it will not be able to display as needed, but can be filtered when outputting to spreadsheet when extracting.
The below formatting would work if I mess around with my environment variables within AIX:
I don't know how to make the section visible in bold on stackexchange, but see [2,20], that will be used for format.