NOTE: I'm sorry for neglecting this message for so long. A lot has been going on...

Prayer Request: After sixteen and a half years of marriage, I was legally divorced on February 12th of 2020 against my will. I feel the State of Arkansas made an incorrect decision that day. In addition, I feel the trial was not conducted properly; however, I chose to not appeal.

Regardless of what the State of Arkansas says, I still believe in my heart that I am married to Jennifer Ann. Please pray in Jesus' name for her return.

I was training for May 2020 Ironman in Tulsa, OK but it was cancelled due to COVID. I am now training for 2020 IM Flordia in November. I've been having some weird back issues that seem to be causing side effects. I may get better though. I can do the IM swim in a local lake, but I'm not sure if I'm up to the Ocean...

I do have a bit of a problem with my temper and I have other troubles; however, I still believe that Jesus Christ is my Lord and Savior and I plea to him for his mercy regardless of what others might think of me.

Even though I presently live in Northwest Arkansas, I watch Bishop T.D. Jakes' Potter's House sermons on YouTube each Sunday Morning. That dude can seriously preach!!! Also, the musicians at Potters House are awesome. Check it out!!!

I hope to return to First Baptist Fayetteville some day but I'm not sure if that is in the near or distant future...
This site is designed to work with *modern* HTML5 browsers like Microsoft Edge
IE10 or later also works pretty good. As of April 23, 2016 Firefox and Chrome still don't:
  1. seem to have official support for GridBased Layout but it seems to be comming soon
  2. seem to support JPEG-XR even though I believe it is a formal ISO Standard under an open format: ISO/IEC 29199-2:2012
Other browsers may also have variying support, but I *personally* consider Microsoft Edge, Chrome and FireFox the major browser vendors of present time.

Peoplesoft: Non-Exact EffDt Can Misplace Related Data

Peoplesoft: Non-Exact EffDt Can Misplace Related Data

Once again, I was working on a PeopleSoft project. While working on a page that had three "related" tables, I made an interesting error that temporarily caused to me misplace some data.

The three tables were all related by two keys. One of the keys was a string proprietary to my employer and the other key was Effective Date (EffDt). I needed to get rid of some old and unused columns in the main table of the three.

I eliminated the columns with ease and saved the table, but then I had to build the table according to current PeopleSoft practice. Of course when I built the table, I got an error explaining that my current settings wouldn't allow PeopleSoft to drop tables that had data in them. Then, I tried alter by table rename with the drop columns option, but that also gave me an error. Eventually, I decided to nuke the table by recreating it. This of course blew away all of my data in the table, but I expected that.

Since there wasn't much data to recover, I decided to use a combination of copy/paste and manual typing to put the data back in the main table. When I got the data back in, I was pleased to find that the level 0 data on my page was mostly correct. Then, I was not so happy when I noticed that my level 1 data was gasp "missing."

I knew I had not messed with any of the data in the other two tables, so I couldn't understand where my level 1 data went. After all, I hadn't even touched my level 1 tables. Just to make sure, I looked in my version of Oracle SQL Developer and found that the data was still there.

I couldn't figure out what gave, so I played around with the keys of the main table for a while. Unfortunately, that didn't work. Apparently, I had not done anything serious to damage the keys that relate all three of the tables.

Eventually, it dawned on me that there must be a non-visible error in the data for the table keys. Even though the key data looked okay to me, I thought I must have missed some: special characters; extra spaces; or, invisible characters. After checking the keys for a while, I realized the problem had to be the EffDt field.

I ran an SQL statement similar to the following and the cause of the error was apparent:

         select * from tableOfInterest where EffDt = '3-SEP-2015';
(Table name and date were actually different in my environment)

When I got around to running the above mentioned SQL statement, I believe I had only one row with a date of 3-SEP-2015 actually show up with several others that didn't. That clued me in on the fact that Oracle SQL Developer was probably not showing me the entire dateTime value for my EffDt column. Some of the rows apparently had different times of days for the EffDt column even though they all had the same date.

When I realized the problem, I simply ran a few SQL update statements to correct the unmatched EffDt values. In my case, I didn't have much data and I was in a test environment, so I could simply do something similar to this:

            update tableOfInterest set EffDt = '3-SEP-2015';
            update relatedTable1 set EffDt = '3-SEP-2015';
            update relatedTable2 set EffDt = '3-SEP-2015';

Once the EffDt values were all matching, my Level 1 data reappareed on the page I was working with, and I no longer had to be in panic mode. Fortunately, I didn't break anything afterall...


No external sources were used for this blog post.

©2015 - Shawn Eary
This post is copyright (where allowable) by Shawn Eary and is released under the Free Christian Media Licence (FCML). Content from authors other than Shawn Eary maintain the copyright and license rules that were imposed by the original authors.