Re: Philosophical question...define table to be load from external data with integer or decimal fields...

On 22-Jan-2014 12:05 -0800, Charles Wilt wrote:

Perhaps this will help explain my quandry...

The data source provided DDL for Oracle and MS SQL Server...

So I've got
--Oracle
create table mytbl
(myfld1 number(5) not null
, myfld2 number(2) not null
, myfld3 number(10) not null
, myDate varchar2(10) null
<...>

--MS SQL Server
create table mytbl
(myfld1 integer not null
, myfld2 integer not null
, myfld3 integer not null
, mydate varchar(10) null
<...>

The hard copy documentation has
Data
Element Record Type/
Name Position Length Picture
Myfld1 1-5 N/5 9(5)
Myfld2 6-7 N/2 9(2)
Myfld3 8-17 N/10 9(10)
Mydate 18-26 N/8 9(8)

Note: The data is coming as a pipe delimited text file, so the
"Record Position" above really doesn't apply.

Now, I've already decided to define mydate as an actual date :) Now
I'm trying to decide how to define myfld1, myfld2, myfld3.

My personal preference for new internal stuff is always integer if
I'm dealing with whole numbers. I've never really dealt with a
"legacy" data feed. Now I can certainly do legacy to match...but
should I?

So painful even to read such disparate declarations! The myfld3 as
integer looks like a potential problem; an overflow lurking.? The
hard-copy doc for Mydate is quite odd for its picture relative to the
noted positions; i.e. clearly eight digits, but nine positions.? Did
they reserve a position for a sign on the /date/ values, but none for
any of those prior? Does the extra byte identify the formatting [about
where to find YYYY vs MM vs DD] of the particular numeric date, but
inappropriately tacked onto the Mydate layout instead of represented by
an additional 1-byte field? Given that neither of the other databases
used a DATE data type, I wonder if perhaps your choosing DATE might not
bode well; e.g. use of numeric values that can not be represented as an
actual date, yet for which the effect of selection and ordering may
still be necessary because those non-date values have special meaning
[more meanings than what just the NULL value provides in any given
context].?

--
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.