|
| How to extract and reformat a non-standard date into a timestamp |
 |
Wed, 19 Mar 2008 16:42:08 EDT |
We are receiving a file that contains all character fields. We need to use SQL
to reformat those fields into numeric and time/date fields. The issue is with
one field that needs to contain a time stamp value.<br />
<br />
The field coming in is 255 bytes character with an entry similar to: '10/01/2006
12:00:00 AM ' and we need to convert that into a standard timestamp field, i.e.
'2006-10-01-00.00.00.000000'<br />
<br />
The real issue is we have been tasked to do this in a single SQL statement,
intermediate files are not allowed.<br />
<br />
Can anyone give me an idea of how to do this?<br />
<br />
Thanks,<br />
<br />
Roger Tower<br />
Sr. I-Series Developer<br />
Station Casinos<br />
Las Vegas, Nv.<br />
|
| Post Reply
|
| Re: How to extract and reformat a non-standard date into a timestamp |
 |
Wed, 19 Mar 2008 22:00:22 -050 |
Receiving file data that was previously stored in a database [SQL]
TABLE on a different system, which was then /exported/ as character
data? Is the "255 bytes character" describing an effective flat file,
across which all data for each row is contained, in perhaps either a
/delimited/ or /fixed/ format; i.e. again, is it an export formatted
character data?
Given the data is already character versus a database SQL data type,
it seems this is already an intermediate file. Is the goal to insert
data into a final SQL TABLE, selecting the transformed data from this
[import file] data?
Given so little detail about the origins and layout of the data [and
the file containing that data], an example with assumptions of what is
the scenario [using 45 vs 255 as presumed single column table], and a
three-column target TABLE. One assumption that complicates, is if the
seconds is ever other than zero, because *USA time does not account for
seconds.
<code>
Given:
create table qtemp.pf45 (f char (45) ccsid 37)
ftp /* get file_at_server qtemp/pf45.pf45 (replace */
-- indent five; four lines of pf45 data in /fixed/ format,
-- following a scale line
-- ....+....1....+....2....+....3....+....4....+
12345 -1234567.8910/01/2006 12:00:00 AM
-12345 1234567.89 10/16/2007 12:00:00 PM
115 1.15 01/15/2007 01:15:00 AM
1115 11.15 11/15/2007 11:15:00 PM
create table qtemp.final
(i smallint, n numeric(9, 2), t timestamp)
Then a single insert, assuming seconds are always zero and no
zero-suppress in the date or time:
insert into qtemp.final
( select cast(substr(f, 01, 11) as smallint) as i
, cast(substr(f, 12, 11) as numeric(9, 2)) as n
, timestamp( date( substr(f, 23, 10) )
, case
when substr(f, 34, 05) = '12:00'
then case when substr(f, 43, 02) = 'AM'
then '00:00 AM' else '12:00 PM' end
else substr(f, 34, 05) concat substr(f, 42, 03)
end ) as t
from qtemp.pf45
)
select * from qtemp.final
....+....1....+....2....+....3....+....4....+....5
I N T
12,345 1,234,567.89- 2006-10-01-00.00.00.000000
12,345- 1,234,567.89 2007-10-16-12.00.00.000000
115 1.15 2007-01-15-01.15.00.000000
1,115 11.15 2007-11-15-23.15.00.000000
******** End of data ********
Or using import, then create a view [not officially an /intermediate/
file, because there is no data] which is then named as the FROMFILE() on
a *FIXED format CPYFRMIMPF:
create view qtemp.fixed (f) as
( select substr(f, 01, 22) concat substr(f, 29, 04) concat
'-' concat substr(f, 23, 02) concat '-' concat
substr(f, 26, 02) concat '-' concat
, case
when substr(f, 34, 02) = '12'
then case when substr(f, 43, 02) = 'AM'
then '00' else '12' end
when substr(f, 43, 02) = 'PM'
then digits(decimal(substr(f, 34, 02)+12, 2, 0))
else substr(f, 34, 02)
end
concat '.' concat substr(f, 37, 02) concat '.00.00000'
from qtemp.pf45
)
select * from qtemp.fixed
....+....1....+....2....+....3....+....4....+..
F
12345 -1234567.892006-10-01-00.00.00.00000
-12345 1234567.89 2007-10-16-12.00.00.00000
115 1.15 2007-01-15-01.15.00.00000
1115 11.15 2007-11-15-23.15.00.00000
******** End of data ********
Source member data for field definition file:
..+....2....+....3....+....4....+....5
I 01 10
N 11 22
T 23 47
*END
****** END OF DATA *****
CPYFRMIMPF FROMFILE(QTEMP/FIXED) TOFILE(QTEMP/FINAL)
MBROPT(*REPLACE) RCDDLM(*EOR) DTAFMT(*FIXED)
RMVBLANK(*NONE) FLDDFNFILE(QGPL/QFLDDFN FIXED)
....+....1....+....2....+....3....+....4....+....5
I N T
12,345 1,234,567.89- 2006-10-01-00.00.00.000000
12,345- 1,234,567.89 2007-10-16-12.00.00.000000
115 1.15 2007-01-15-01.15.00.000000
1,115 11.15 2007-11-15-23.15.00.000000
******** End of data ********
</code>
Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever
and may not represent positions, strategies, nor views of my employer
roger.tower@stationcasinos.com wrote:
> We are receiving a file that contains all character fields. We
> need to use SQL to reformat those fields into numeric and time/date
> fields. The issue is with one field that needs to contain a time
> stamp value.
>
> The field coming in is 255 bytes character with an entry similar to:
> '10/01/2006 12:00:00 AM ' and we need to convert that into a standard
> timestamp field, i.e. '2006-10-01-00.00.00.000000'
>
> The real issue is we have been tasked to do this in a single SQL
> statement, intermediate files are not allowed.
>
|
| Post Reply
|
| Re: How to extract and reformat a non-standard date into a |
 |
Thu, 20 Mar 2008 12:51:49 EDT |
Chuck,<br />
<br />
The file is coming from an extract of a PC database, type unknown to me, that
ends up in an Microsoft SQL database as all 255 byte character format. I have
been told that portion of it is proprietary and can not be changed. <br
/>
<br />
The I-Series view of that file is represented below as viewed in DBU as FAS_ALL
in the fas_all.doc file attached.<br />
<br />
The format that has been specified in our data warehouse, located on an
I-Series, is below as FAS_ALLN as seen via DBU in the fas_all.doc file
attached..<br />
<br />
The goal is to run a single SQL statement as part of a package to move this file
from the MS/SQL to our data warehouse on the I-Series. <br />
<br />
The person previously working on this project ran the steps by hand, and has
recently been let go. All of his work has been lost.<br />
<br />
A simple Insert Into statement works for all the fields with the exception of
the timestamp field. I hope this helps you in understanding my issue.<br
/>
<br />
Thanks,<br />
<br />
|
| Post Reply
|
| Re: How to extract and reformat a non-standard date into a timestamp |
 |
Thu, 20 Mar 2008 15:31:09 -050 |
Read bottom-up to ignore my rant about the forum, avoid my
methodology and example setup, and to see what is the probable answer.
FWiW I am using NNTP to access messages, so I had to go to the web to
view the attachment. The /web-forum/ software apparently does not do a
complete job of interaction between NNTP and web usage.
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14061469&tsta
rt=0
By seeing that text, it seems to me that the NNTP to web conversion
tooling had noticed my <code> tag [I know that the tag was removed], but
also completely ignored the tag! The copy from plain text to HTML did
its best to destroy any recognition of a /plain text/ post. Argh! Any
wonder NNTPers hate HTML!?
Because my messages are being reformatted, I can only suggest that
you get Mozilla Thunderbird [or another news reader] to review my posts
via news://ibm.software.db2.os400 Albeit this message, it will not
matter too much.
Anyhow, specifically for my prior message, so it is more usable:
news://news.software.ibm.com:119/frsk25$2302u$1@news.boulder.ibm.com
Anyhow the given doc suggests the data was imported into a TABLE with
90 columns, each column [with one exception] a 255-byte char field. It
seems then that the original database was defined with a /string/ or a
/comment/ data type, that is presumably an effective VARCHAR(255). No
matter, the from-fields and presumably the data for each can be
inferred... for the most part, from what is stated, as being easily
transformed using a /simple/ insert-select-from whereby mapping
column-to-column is automatic. The *exception* being some timestamp
data, which because it is not in a form supported by the DB2 for i5/OS
SQL, requires a special transform.
One question still remains however, and that is about the data. Do
any of the date/timestamp have any seconds, or perhaps more likely the
time-portion of the string is not relevant, such that the time can and
should always be zero; also such that, the data type would do just as
well to be a DATE versus a TIMESTAMP.
create table qtemp.FAS_ALL
(PROP_CD VARCHAR(255) NOT NULL WITH DEFAULT
,Q68_K00001 VARCHAR(255) NOT NULL WITH DEFAULT
,ACE_B00001 VARCHAR(255) NOT NULL WITH DEFAULT
,ACE_R00001 VARCHAR(255) NOT NULL WITH DEFAULT
,ACQUI00001 VARCHAR(255) NOT NULL WITH DEFAULT
) -- 85 columns snipped; not relevant
create table qtemp.FAS_ALLN
(PROP_CD VARCHAR(255) NOT NULL WITH DEFAULT
,Q68_K00001 INT NOT NULL WITH DEFAULT
,ACE_B00001 INT NOT NULL WITH DEFAULT
,ACE_R00001 INT NOT NULL WITH DEFAULT
,ACQUI00001 TIMESTAMP NOT NULL WITH DEFAULT
) -- 85 columns snipped; not relevant
Sample data for the the copy:
insert into qtemp.FAS_ALL values(
'X', '1', '2', '3', '10/01/2006 12:00:00 AM' )
The simple insert would be, ideally, nothing more than:
INSERT INTO qtemp.FAS_ALLN SELECT * FROM qtemp.FAS_ALL
Unfortunately due to the requirement to transform at least one column
requires that at least one clause requires the column-list to be
specified.
From the original example of 12:00 AM going to time 00.00.00 it seems
reasonable to infer the time is not relevant, so I will suggest the
/simple/ insert you desire is as follows:
INSERT INTO qtemp.FAS_ALLN
(SELECT
PROP_CD
,Q68_K00001
,ACE_B00001
,ACE_R00001
,timestamp(left(ACQUI00001, 10), '00.00.00')
/* 85 columns snipped; not relevant */
FROM qtemp.FAS_ALL
)
If the DW TABLE used DATE instead of TIMESTAMP, then the timestamp()
function could be removed, so LEFT() would suffice.
If the data were to be retrieved directly from the PC database, the
statement to use to transform would instead be:
INSERT INTO qtemp.FAS_ALLN
(SELECT
PROP_CD
,Q68_K00001
,ACE_B00001
,ACE_R00001
,substr(ACQUI00001, 7, 4) concat '-' concat
substr(ACQUI00001, 1, 2) concat '-' concat
substr(ACQUI00001, 4, 2) concat '-00.00.00.00000'
/* 85 columns snipped; not relevant */
FROM qtemp.FAS_ALL
)
Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer
roger.tower@stationcasinos.com wrote:
> The file is coming from an extract of a PC database, type unknown to
> me, that ends up in an Microsoft SQL database as all 255 byte
> character format. I have been told that portion of it is proprietary
> and can not be changed.
>
> The iSeries view of that file is represented below as viewed in DBU
> as FAS_ALL in the fas_all.doc file attached.
>
> The format that has been specified in our data warehouse, located on
> an I-Series, is below as FAS_ALLN as seen via DBU in the fas_all.doc
> file attached..
>
> The goal is to run a single SQL statement as part of a package to
> move this file from the MS/SQL to our data warehouse on the I-Series.
>
> The person previously working on this project ran the steps by hand,
> and has recently been let go. All of his work has been lost.
>
> A simple Insert Into statement works for all the fields with the
> exception of the timestamp field. I hope this helps you in
|
| Post Reply
|
| Re: How to extract and reformat a non-standard date into a |
 |
Thu, 20 Mar 2008 18:21:16 EDT |
Chuck,<br />
<br />
That worked, perfect!<br />
<br />
Thank you very much.<br />
<br />
|
| Post Reply
|
|
|