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