SQL – Create Column for Values Between a Range in Informix

informix

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]:

enter image description here

This is what I want [table2]:

enter image description here

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

select
case when num_of_extents between 0 and 9 then a.tabname end as one,
case when num_of_extents between 10 and 19 then a.tabname end two,
case when num_of_extents between 20 and 29 then a.tabname end three,
case when num_of_extents between 30 and 39 then a.tabname end four,
case when num_of_extents between 40 and 49 then a.tabname end five,
case when num_of_extents >= 50 then a.tabname end 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 group by 1,2,3,4,5,6

Output shows like below within dbaccess:

one
two    bc_storage_idx2
three
four
five
six

one
two
three
four   tock_mvmt
five
six

one
two
three  rd_stats
four
five
six

one
two    tock_mvmt_idx1
three
four
five
six
...

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:

       select
       (case when num_of_extents between 0 and 9 then a.tabname end)[2,20] as one,
       case when num_of_extents between 10 and 19 then a.tabname[2,20] end two,
       case when num_of_extents between 20 and 29 then a.tabname[2,20] end three,
       case when num_of_extents between 30 and 39 then a.tabname[2,20] end four,
       case when num_of_extents between 40 and 49 then a.tabname[2,20] end five,
       case when num_of_extents >= 50 then a.tabname[2,20] end 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 group by 1,2,3,4,5,6

I don't know how to make the section visible in bold on stackexchange, but see [2,20], that will be used for format.