× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.