PSTREEDEFN Error

Today, I ran into an error importing a PeopleTools tree into an instance of PeopleSoft.  Probably, the problem was that I was using an old version of PeopleTools (8.46).

If this were a production system, the fix would be to upgrade PeopleTools to 8.51.  But, this is for testing purposes, and I kind of want to keep the old version so that I can make sure things run with it.

Here is the error message that I received from Data Mover:

SQL error. Stmt #: 0  Error Position: 0  Return: 1400 - ORA-01400: cannot insert NULL into ("SYSADM"."PSTREEDEFN"."PT_ALLVALUEAUDOPT")

With all that said, you probably don’t want to follow these directions.  (This is a don’t try at home moment.)  I don’t even know why I am posting this.  Maybe it will help some one with SQL syntax or something.  Most likely, I will need it again for a later update into this same instance.

First, I moved my PSTREEDEFN table off to a different name:

ALTER TABLE PSTREEDEFN
RENAME TO PSTREEDEFN_ORIG;
[\sourcecode]

Then, I created a copy of the original table just so that I could force the data mover script to run without breaking the original table:

[sourcecode language="sql"]
CREATE TABLE PSTREEDEFN
AS
SELECT * FROM PSTREEDEFN_ORIG;
[\sourcecode]

Next, I dropped the offending field from the table:

[sourcecode language="sql"]
ALTER TABLE PSTREEDEFN DROP COLUMN PT_ALLVALUEAUDOPT;
[\sourcecode]

Then, I ran the data mover script, which this time ran to success because the field was not there.

Now, it is time to see what changed.  At first, I tried to find new rows:

[sourcecode language="sql"]
SELECT * FROM PSTREEDEFN A
WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN_ORIG B
WHERE A.SETID = B.SETID
AND A.SETCNTRLVALUE = B.SETCNTRLVALUE
AND A.TREE_NAME = B.TREE_NAME
AND A.EFFDT = B.EFFDT);
[\sourcecode]

When that came back with no new rows, I opened the DMS data file and determined which tree row changed.  And then, I could compare the two tables with this SQL:

[sourcecode language="sql"]
SELECT 'N', A.*, ' ' FROM PSTREEDEFN A
WHERE TREE_NAME = 'QUERY_TREE_HR'
UNION
SELECT 'Y', A.* FROM PSTREEDEFN_ORIG A
WHERE TREE_NAME = 'QUERY_TREE_HR';
[\sourcecode]

When I noticed that the Node_Count and the LastUpdDttm where the only two fields, I just simply updated the original table with the new values:

[sourcecode language="sql"]
UPDATE PSTREEDEFN_ORIG
SET NODE_COUNT = 2030, LASTUPDDTTM = (
SELECT MAX(LASTUPDDTTM) FROM
PSTREEDEFN A WHERE TREE_NAME = 'QUERY_TREE_HR'
)
WHERE TREE_NAME = 'QUERY_TREE_HR';
[\sourcecode]

Finally, we drop our copy of the table:

[sourcecode language="sql"]
DROP TABLE PSTREEDEFN;
[\sourcecode]

And, rename the table back:

[sourcecode language="sql"]
ALTER TABLE PSTREEDEFN_ORIG RENAME TO PSTREEDEFN;
[\sourcecode]

That may not be the best way to get the job done, but it got my problem fixed!

Update:

It happened again before I could publish this post.  The second time, I just altered the table to add a default to the field.  This way, the DMS script didn't have to know about the field, but the field didn't have to be removed from the table:

[sourcecode language="sql"]

ALTER TABLE PSTREEDEFN

MODIFY PT_ALLVALUEAUDOPT DEFAULT 'D';

Resources

SQL Table Rename
Java2s.com: Changing the Default Value of a Column