I'm trying to use SQL more for my daily chores. The midrange list members
replied so quickly I barely lost a step.
Thanks again.
Jack
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, February 05, 2014 4:26 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
