Re: AW: Embedded SQL formatting dates

On 02-Jan-2016 10:38 -0700, Booth Martin wrote:

The problem I am facing is that I want to group daily data by
week where the date is cyymmdd 7/0 in a many-row history file. I
haven't yet figured out how to use SQL's Week function with *cymd,

There is no such thing as *CYMD in SQL; the data type of the column or expression as the sole argument of the WEEK and WEEK_ISO scalars can be only one of a DATE, TIMESTAMP, or a character string [that can be implicitly cast to a DATE or TIMESTAMP] data type. The numeric data values formatted as digits representing CYYMMDD must be converted to a DATE data type to be referenced in either of those /week/ scalars.

or, alternatively, plug an RPG line in the middle of the group-by.

The line(s) of RPG code can be referenced in the GROUP BY, having been encapsulated by an EXTERNAL scalar User Defined Function (UDF), and previously defined to [for use by] the SQL using the CREATE FUNCTION (external scalar):
[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcfsce.htm]

Substringing the data is the only way I have found, and that
seems pretty ham-handed.

Be sure to read the replies within the thread having variations from the original "Subject:" which often gets mucked-up by non-English variants of "Re:"; e.g. as I have left in my subject-line, the "AW:". Birgitta offered an expression that rids of any substring (or SUBSTR) scalar; I replied with another as well, but posted since the followup quoted in this reply.

At the moment your suggestion of an intermediate file is looking
pretty good.

The Date Table [table of date values] methodology to correlate each possible CYYMMDD value to the equivalent actual DATE data-type values could also include the /week/ value [which need not even match either of the WEEK or WEEK_ISO scalar results], so as to prevent a requirement to calculate that result at run-time.

FWiW the following query, for example, can be used to populate such a table spanning all dates from both the 20th and 21st centuries:

with
date_table ( cyymmdd, date_val, week_val ) as
( select *
from table( values(
cast( 0000101 as numeric(7) )
, date( '1900-01-01' )
, zoned( week_iso( date( '1900-01-01' )), 2)
) ) as primed
union all
select case when date_val < '2000-01-01'
then '0' else '1' end
concat varchar_format(
cast(date_val + 1 day as timestamp), 'YYMMDD')
, date_val + 1 day
, zoned( week_iso( date_val + 1 day ), 2)
from date_table
where date_val < '2099-12-31'
)
select * from date_table