Prev: How to change Enterprise server edition to Workgroup server edition ?
Next: String functions in SQL
From: Frank Swarbrick on 7 Oct 2009 20:08
First of all I want to acknowledge that I do not believe that the following
should have been done in the first place. And perhaps I can influence those
on the project to realize what a bad idea it was to go this direction. That
being said, I wonder if this issue can be addressed without changing the
database design...

We have a "maintenance" database where we store fairly generic maintenance
records. By maintenance I refer to the action of changing a column in a
table and storing the old value and the new value in this separate
maintenance table. This table is then used to report on changes that have
been made to various tables. So a report may look something like this:

ACCOUNT NUMBER FIELD DESCR OLD VALUE NEW VALUE
123456789 OPEN DATE 10/29/2009 9/17/2008

Depending on what FIELD DESCR is (and of course FIELD DESCR stored as a code
in the database) OLD_VALUE and NEW_VALUE may hold integers, monetary values,
dates, freeform text, etc.

In and of itself I don't believe this to be a bad idea. But I'm sure I'll
here from someone if it is.

The problem I'm dealing with is someone seems to have "co-opted" this
"maintenance table" for something that I'm not sure it should be used for.
Then again I don't (yet) know much about the project. All I know is that a
programmer has written the following query:

SELECT NEW_VALUE
FROM MAINT.MAINT_IDS M2
JOIN MAINT.MAINT_ACTIONS M3
ON M2.MASTER_ID = M3.MASTER_ID
JOIN MAINT.MAINT_ACTION_DETAILS M4
ON M3.ACTION_ID = M4.ACTION_ID
WHERE ID_TYPE_CODE = 'CIF'
AND ID_AFFECTED = ?
AND MAINT_TYPE_CODE = 'SARFILING'
AND FIELD_CODE = 'DATE'
AND DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS
AND DATE(NEW_VALUE) >= CURRENT DATE - 90 DAYS
FETCH FIRST 1 ROW ONLY


I believe his goal is to get SQLCODE = 0 if there is at least one result and
SQLCODE = 100 if there is not. (He's gone home for the day and I'm being
impatient which is why I am missing some possibly relevent information.)

Anyway, depending on which ID_AFFECTED parameter we are querying on we may
get a result or we may get the following error:
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007

After some research I have found that there are cases where even though
FIELD_CODE = 'DATE', the value in NEW_VALUE is not a valid date.
Specifically one has '8/20/209' when it obviously should have '8/20/2009'.

I have no idea *where* this data came from. Obviously it needs some "data
validation" done to it before being dumped in to this database. Still, I am
curious if there is anything in my query that could be done in order to,
umm, not get this error? Maybe that's not even what I should be doing,
but... I'm kind of thinking of something similar to how COALESCE deals with
nulls by returning something a different value. So here maybe the invalid
date could be somehow converted to a valid date, like perhaps CURRENT DATE?

I dunno. Probably there's no point in even worrying about it; we should fix
the data. And I have to think that's where we'll end up. Still, having
this may actually help us find the bad data. How could I do a query that
returns be all cases where NEW_VALUE is not a valid date?

I guess I should ask the question I don't want to ask... Based on what I've
explained so far (and feel free to ask for more details), is this an example
of the dreaded "Entity Attribute Value" database? And is it a bad thing in
this case? I had a big part in designing this database, but I felt at the
time that it was the appropriate way to go. And honestly I still feel that
way. It's generic enough to be flexible for many types of "maintenance
reports" yet specific enough to not just be a "data dump" type of database.

Perhaps I should show you what the various tables are...

CREATE TABLE MAINT_TYPES (
CODE VARCHAR(10) NOT NULL,
ADD_CHG_DELETE CHAR(1) NOT NULL,
DESCRIPTION VARCHAR(25) NOT NULL,
SHORT_NAME VARCHAR(25),
VERIFY_REQ_YN CHAR(1) DEFAULT 'Y' NOT NULL
);
ALTER TABLE MAINT_TYPES
ADD CONSTRAINT MAINT_TYPES_PK PRIMARY KEY (CODE, ADD_CHG_DELETE);
ALTER TABLE MAINT_TYPES
ADD CONSTRAINT MAINT_TYPES_CK1 CHECK (MAINT.MAINT_TYPES.ADD_CHG_DELETE
in ('A','C','D'));
ALTER TABLE MAINT_TYPES
ADD CONSTRAINT MAINT_TYPES_CK2 CHECK (MAINT.MAINT_TYPES.VERIFY_REQ_YN in
('Y','N'));

CREATE TABLE MAINT_ID_TYPE (
CODE VARCHAR(10) NOT NULL,
DESCRIPTION VARCHAR(25) NOT NULL,
SHORT_NAME VARCHAR(25)
) ;
ALTER TABLE MAINT_ID_TYPE
ADD CONSTRAINT MAINT_ID_TYPE_PK PRIMARY KEY (CODE);

CREATE TABLE MAINT_IDS (
MASTER_ID DECIMAL(12) NOT NULL,
ID_TYPE_CODE VARCHAR(10) NOT NULL,
ID_AFFECTED VARCHAR(40) NOT NULL,
DESCRIPTION VARCHAR(50)
);
ALTER TABLE MAINT_IDS
ADD CONSTRAINT MAINT_ID_PK PRIMARY KEY (MASTER_ID, ID_TYPE_CODE);
ALTER TABLE MAINT_IDS
ADD CONSTRAINT MAINT_ID_FK2 FOREIGN KEY (ID_TYPE_CODE)
REFERENCES MAINT_ID_TYPE (CODE);

CREATE TABLE MAINT_ACTIONS (
ACTION_ID DECIMAL(12) NOT NULL GENERATED ALWAYS AS IDENTITY,
MASTER_ID DECIMAL(12) NOT NULL,
MAINT_TYPE_CODE VARCHAR(10) NOT NULL,
ADD_CHG_DELETE CHAR(1) NOT NULL
);
ALTER TABLE MAINT_ACTIONS
ADD CONSTRAINT MAINT_ACTIONS_PK PRIMARY KEY (ACTION_ID);
ALTER TABLE MAINT_ACTIONS
ADD CONSTRAINT MAINT_ACTIONS_FK2 FOREIGN KEY (MAINT_TYPE_CODE,
ADD_CHG_DELETE)
REFERENCES MAINT_TYPES (CODE, ADD_CHG_DELETE);

CREATE TABLE MAINT_ACTION_DETAILS (
DETAIL_ID DECIMAL(12) NOT NULL GENERATED ALWAYS AS IDENTITY,
ACTION_ID DECIMAL(12) NOT NULL,
FIELD_CODE VARCHAR(10) NOT NULL,
OLD_VALUE VARCHAR(200),
NEW_VALUE VARCHAR(200)
);
ALTER TABLE MAINT_ACTION_DETAILS
ADD CONSTRAINT MAINT_ACTION_DETAILS_PK PRIMARY KEY (DETAIL_ID);
ALTER TABLE MAINT_ACTION_DETAILS
ADD CONSTRAINT MAINT_ACTION_DETAILS_FK1 FOREIGN KEY (ACTION_ID)
REFERENCES MAINT_ACTIONS (ACTION_ID);
ALTER TABLE MAINT_ACTION_DETAILS
ADD CONSTRAINT MAINT_ACTION_DETAILS_FK2 FOREIGN KEY (FIELD_CODE)
REFERENCES MAINT_FIELDS (CODE);

With some trepidation I wait for your opinion.

Thanks,
Frank

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403

From: Lennart on 8 Oct 2009 06:05
On 8 Okt, 02:08, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
wrote:
> First of all I want to acknowledge that I do not believe that the following
> should have been done in the first place.  And perhaps I can influence those
> on the project to realize what a bad idea it was to go this direction.  That
> being said, I wonder if this issue can be addressed without changing the
> database design...
>
> We have a "maintenance" database where we store fairly generic maintenance
> records.  By maintenance I refer to the action of changing a column in a
> table and storing the old value and the new value in this separate
> maintenance table.  This table is then used to report on changes that have
> been made to various tables.  So a report may look something like this:
>
> ACCOUNT NUMBER  FIELD DESCR   OLD VALUE    NEW VALUE
>      123456789    OPEN DATE   10/29/2009   9/17/2008
>
> Depending on what FIELD DESCR is (and of course FIELD DESCR stored as a code
> in the database) OLD_VALUE and NEW_VALUE may hold integers, monetary values,
> dates, freeform text, etc.
>
> In and of itself I don't believe this to be a bad idea.  But I'm sure I'll
> here from someone if it is.
>
> The problem I'm dealing with is someone seems to have "co-opted" this
> "maintenance table" for something that I'm not sure it should be used for..
> Then again I don't (yet) know much about the project.  All I know is that a
> programmer has written the following query:
>
> SELECT NEW_VALUE                              
> FROM MAINT.MAINT_IDS M2                        
> JOIN MAINT.MAINT_ACTIONS M3                    
>      ON M2.MASTER_ID = M3.MASTER_ID                  
> JOIN MAINT.MAINT_ACTION_DETAILS M4            
>      ON M3.ACTION_ID = M4.ACTION_ID                  
> WHERE ID_TYPE_CODE = 'CIF'
>   AND ID_AFFECTED = ?
>   AND MAINT_TYPE_CODE = 'SARFILING'
>   AND FIELD_CODE = 'DATE'
>   AND DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS
>   AND DATE(NEW_VALUE) >= CURRENT DATE - 90 DAYS    
> FETCH FIRST 1 ROW ONLY        
>

One thing that strikes me is, what happens if db2 decides to evaluate
DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS before FIELD_CODE = 'DATE'?
Won't you (or rather the missing developer) run into situations like:

DATE('This for sure is not a valid date') <= CURRENT DATE - 1
DAYS


/Lennart
From: Frank Swarbrick on 8 Oct 2009 11:07

--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


n 10/8/2009 at 4:05 AM, in message
<49272539-a5cc-49ce-8550-d3177da96f5d(a)s6g2000vbp.googlegroups.com>,
Lennart<erik.lennart.jonsson(a)gmail.com> wrote:
> On 8 Okt, 02:08, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
> wrote:
>> First of all I want to acknowledge that I do not believe that the
> following
>> should have been done in the first place. And perhaps I can influence
> those
>> on the project to realize what a bad idea it was to go this direction.
> That
>> being said, I wonder if this issue can be addressed without changing the
>> database design...
>>
>> We have a "maintenance" database where we store fairly generic
> maintenance
>> records. By maintenance I refer to the action of changing a column in a
>> table and storing the old value and the new value in this separate
>> maintenance table. This table is then used to report on changes that
> have
>> been made to various tables. So a report may look something like this:
>>
>> ACCOUNT NUMBER FIELD DESCR OLD VALUE NEW VALUE
>> 123456789 OPEN DATE 10/29/2009 9/17/2008
>>
>> Depending on what FIELD DESCR is (and of course FIELD DESCR stored as a
> code
>> in the database) OLD_VALUE and NEW_VALUE may hold integers, monetary
> values,
>> dates, freeform text, etc.
>>
>> In and of itself I don't believe this to be a bad idea. But I'm sure
> I'll
>> here from someone if it is.
>>
>> The problem I'm dealing with is someone seems to have "co-opted" this
>> "maintenance table" for something that I'm not sure it should be used
> for.
>> Then again I don't (yet) know much about the project. All I know is
> that a
>> programmer has written the following query:
>>
>> SELECT NEW_VALUE
>> FROM MAINT.MAINT_IDS M2
>> JOIN MAINT.MAINT_ACTIONS M3
>> ON M2.MASTER_ID = M3.MASTER_ID
>> JOIN MAINT.MAINT_ACTION_DETAILS M4
>> ON M3.ACTION_ID = M4.ACTION_ID
>> WHERE ID_TYPE_CODE = 'CIF'
>> AND ID_AFFECTED = ?
>> AND MAINT_TYPE_CODE = 'SARFILING'
>> AND FIELD_CODE = 'DATE'
>> AND DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS
>> AND DATE(NEW_VALUE) >= CURRENT DATE - 90 DAYS
>> FETCH FIRST 1 ROW ONLY
>>
>
> One thing that strikes me is, what happens if db2 decides to evaluate
> DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS before FIELD_CODE = 'DATE'?
> Won't you (or rather the missing developer) run into situations like:
>
> DATE('This for sure is not a valid date') <= CURRENT DATE - 1
> DAYS

Can that occur? In Cobol that would never occur because terms are evaluated
in order and short circuit once the predicate is determined to be false.

Frank

From: Lennart on 8 Oct 2009 11:53
On 8 Okt, 17:07, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
wrote:
> --
>
> Frank Swarbrick
> Applications Architect - Mainframe Applications Development
> FirstBank Data Corporation - Lakewood, CO  USA
> P: 303-235-1403
>
> n 10/8/2009 at 4:05 AM, in message
> <49272539-a5cc-49ce-8550-d3177da96...(a)s6g2000vbp.googlegroups.com>,
>
>
>
> Lennart<erik.lennart.jons...(a)gmail.com> wrote:
> > On 8 Okt, 02:08, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
> > wrote:
> >> First of all I want to acknowledge that I do not believe that the
> > following
> >> should have been done in the first place.  And perhaps I can influence
> > those
> >> on the project to realize what a bad idea it was to go this direction.  
> > That
> >> being said, I wonder if this issue can be addressed without changing the
> >> database design...
>
> >> We have a "maintenance" database where we store fairly generic
> > maintenance
> >> records.  By maintenance I refer to the action of changing a column in a
> >> table and storing the old value and the new value in this separate
> >> maintenance table.  This table is then used to report on changes that
> > have
> >> been made to various tables.  So a report may look something like this:
>
> >> ACCOUNT NUMBER  FIELD DESCR   OLD VALUE    NEW VALUE
> >>      123456789    OPEN DATE   10/29/2009   9/17/2008
>
> >> Depending on what FIELD DESCR is (and of course FIELD DESCR stored as a
> > code
> >> in the database) OLD_VALUE and NEW_VALUE may hold integers, monetary
> > values,
> >> dates, freeform text, etc.
>
> >> In and of itself I don't believe this to be a bad idea.  But I'm sure
> > I'll
> >> here from someone if it is.
>
> >> The problem I'm dealing with is someone seems to have "co-opted" this
> >> "maintenance table" for something that I'm not sure it should be used
> > for.
> >> Then again I don't (yet) know much about the project.  All I know is
> > that a
> >> programmer has written the following query:
>
> >> SELECT NEW_VALUE                              
> >> FROM MAINT.MAINT_IDS M2                        
> >> JOIN MAINT.MAINT_ACTIONS M3                    
> >>      ON M2.MASTER_ID = M3.MASTER_ID                  
> >> JOIN MAINT.MAINT_ACTION_DETAILS M4            
> >>      ON M3.ACTION_ID = M4.ACTION_ID                  
> >> WHERE ID_TYPE_CODE = 'CIF'
> >>   AND ID_AFFECTED = ?
> >>   AND MAINT_TYPE_CODE = 'SARFILING'
> >>   AND FIELD_CODE = 'DATE'
> >>   AND DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS
> >>   AND DATE(NEW_VALUE) >= CURRENT DATE - 90 DAYS    
> >> FETCH FIRST 1 ROW ONLY        
>
> > One thing that strikes me is, what happens if db2 decides to evaluate
> > DATE(NEW_VALUE) <= CURRENT DATE - 1 DAYS before FIELD_CODE = 'DATE'?
> > Won't you (or rather the missing developer) run into situations like:
>
> >        DATE('This for sure is not a valid date') <= CURRENT DATE - 1
> > DAYS
>
> Can that occur?  In Cobol that would never occur because terms are evaluated
> in order and short circuit once the predicate is determined to be false.
>

I realize by your signature that this is probably not db2 for LUW that
we are dealing with. I have no idea how db2 for other platforms works,
so this might not hold. The principle is that you tell the DBMS what
you want it to do, and the optimizer decides how that should be done,
amongst other things in what orde the predicates should be evaluated.
To conclude, yes it can happen (as mentioned, might not hold for your
platform).


/Lennart

From: --CELKO-- on 8 Oct 2009 21:28
A trick I have is a look-up table with one column of type DATE, and
one or more columns of various date-as-string CHAR(n) columns. Build
a century with a spreadsheet and a text editor in 10 minutes or less.
OUTER JOIN to it and the bad strings are NULLs.
 |  Next  |  Last
Pages: 1 2
Prev: How to change Enterprise server edition to Workgroup server edition ?
Next: String functions in SQL