Right now I'm just trying to bring this group into the *20th* century
(kicking and screaming, if necessary).
Don't expect you are the only one! I've just discovered a brand new
(December 2010) physical file (DDS described) with a bunch of numeric dates
and times.
Because I needed to calculate with these dates and compare them with the
current date or timestamp, I had to create several absolutely unnecessary
views whose use may slow down performance because of these numeric date
fields.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jerry C. Adams
Gesendet: Monday, 14. February 2011 18:07
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Flat File to SQL-DDL
Birgitta,
I thought something like that would be the answer. Right now I'm just
trying to bring this group into the *20th* century (kicking and screaming,
if necessary). I'll tighten things up later when I have more time (as if!).
I'll also look into Alan's IDate function, as Luis suggested (been meaning
to do that for years, anyway, but swamps and alligators, you know).
Jerry C. Adams
IBM i Programmer/Analyst
When we played softball, I'd steal second, then feel guilty and go back.
-Woody Allen
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, February 14, 2011 10:47 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: Flat File to SQL-DDL
First there is no equivalent for EDTCDE or EDTW in SQL DDL.
You may consider convert the numeric dates into real dates and specify the
date format you want.
The easiest way may be to create SQL views where the numeric dates are
converted:
Create View MySchema/MyView
As (Select a.*, date(Digits(NumDate1 concat '000000')) as MyDate1,
Date(Digits(NumDate2 concat '000000')) as MyDate2, ...
From MyTable)
Select MyDate1, MyDate2, ....
From MyView
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jerry C. Adams
Gesendet: Monday, 14. February 2011 17:19
An: Midrange-L
Betreff: Flat File to SQL-DDL
First, I'm on V5R1.
Second, I'm dealing with a legacy application ported years ago to the AS/400
(!) from either a System/34 or /36.
Third, there are no external table definitions except the ones that I have
created. I prefer using SQL-DDL, but I have never been about to find an
equivalent for DDS's EDTCDE and EDTWRD.
DDL's edit [equivalent to EDTCDE(J)] is find for 90-95% of the cases.
However, since I'm dealing with legacy files, the dates are 8.0 numeric
(ccyymmdd), and they get displayed as, for example, 20,110,214 in queries.
Is there any way to display dates using an edit word, such as (' - -
')? Or even something like EDTCDE(Y)? I use date data types when creating
new tables, but converting these legacy tables is, for the time being, out
of the question.
I RTFM's and could not find anything so I'm surmising that the answer is
"You're SOL." But asking is worth a shot.
Jerry C. Adams
IBM i Programmer/Analyst
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
midrange.com
