Re: SQL question

On 05-Feb-2014 10:57 -0800, Jack Tucky wrote:

I have a file with a catalog# in it. I want to view/print a list of
the lengths of the data within the field. I want one record per
length.

The catalog# is defined as 20 characters, I was able to use
CHAR_LENGTH(STRIP(CAT#)) to see the lengths.

How can I make SQL give me one result like this:

Length count
20 1500
19 5000
18 10000
17 20000
Etc.

I tried this but I get an error, Column list required

create table wrk/counter as
( select
cat#
, CHAR_LENGTH(STRIP(CAT#))
from FILE
where cat#<> ' '
) with data

For that error SQL0153 [sqlcode -153], the resolution is to give the expression a name. The CREATE TABLE needs to have a name for each column that will be created, and as the message suggest, the SELECT list includes an expression without an AS-name clause. Variants of the following would suffice:

create table wrk/counter as
( select
cat#
, CHAR_LENGTH(STRIP(CAT#)) AS CatLen
/* Use AS clause to name col; either System or Alternative name */
from ...

create table wrk/counter
( catnbr, CatCharLength )
/* use column list to give Alias\Alternative or System names */
as
( select ...

create table wrk/counter
( CatNbr for cat#, CatCharLength for CatLen )
/* use col list: give both Alias\Alternative and System names */
as
( select ...

See the syntax diagram for the as-result-table in CREATE TABLE for naming columns using the list vs the naming in the SELECT statement:
<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhctabl.htm>

As for the desired output... Not sure of any apparent need to create a[n intermediate] table with the data. Directly obtaining the data for the reporting while allowing the query possibly to implement with a temporary table is possible; i.e. no need for a CREATE TABLE.

Regardless, the following should suffice [along with giving explicit data type and length; generally desirable with CREATE TABLE], although coded below, using the scalar function RTRIM [or TRIM(TRAILING,...)] instead of the scalar function STRIP [or TRIM(BOTH,...) as shown, per the former more typically being the desired effect:

select
dec( char_length( rtrim( cat# ) ), 2 ) as "Length"
, dec( count(*), 9 ) as "count"
from FILE
where cat#<>''
group by
dec( char_length( rtrim( cat# ) ), 2 )
order by 1 desc
/* or: order by
dec( char_length( rtrim( cat# ) ), 2 ) desc */

Composed using a Common Table Expression (CTE), the requirement to specify the expression repeatedly [as shown in the above query] can be eliminated:

with
CntLen as
( select
dec( char_length( rtrim( cat# ) ), 2 ) as "Length"
from FILE
where cat#<>''
)
select
"Length"
, dec( count(*), 9 ) as "count"
from CntLen
group by "Length"
order by "Length" desc

Although explicitly stated just "one record per length", might a row need to appear for each possible "Length" value from one to twenty; i.e. produce a row with a count equal to zero, for any character_length value not represented across the rows of cat# data in FILE? If so, then perhaps the following is functional as coded; I can not test the use of the row values clause inside the table():

select ifnull("Length", nbr), ifnull("count", 0)
from
( select
dec( char_length( rtrim( cat# ) ), 2 ) as "Length"
, dec( count(*), 9 ) as "count"
from FILE
where cat#<>''
group by dec( char_length( rtrim( cat# ) ), 2 )
) as cntlen ("Length", "count") /* override AS-name above */
right outer join
table( values(dec(20, 2)), (19), (18), (17), (16)
, (15), (14), (13), (12), (11)
, (10), (09), (08), (07), (06)
, (05), (04), (03), (02), (01)
) as cntnbr ( nbr )
on "Length" = nbr
order by nbr desc