CMS Snapshot Analysis - Run Analyzer - Fatal Error - SQL Error - PeopleSoft - PSOFT DISCRIMINATION DATA null value in column parent id

This page is a troubleshooting guide for the problem where PeopleSoft Analysis fails while running the content Enrichment job with the below error :

select 01_local.PSOFT_CONTENT_ENRICHMENT()
INF: 2015-09-09 23:09:03:     [SQL] select 01_local.PSOFT_DISCRIMINATION_DATA()
ERR: 2015-09-09 23:09:20:     Error while executing Batch: select 01_local.PSOFT_DISCRIMINATION_DATA()
Query failed because:
ERROR: null value in column "parent_id" violates not-null constraint
  Detail: Failing row contains (28300, 1600500, null, Subscription, TIME_DEVICE_TASK_VALS_FULLSYNC.TimeDeviceTaskValsFullsync.Subscr..., PPLSOFT, 399302383).
  Where: SQL statement "insert into psoft_objects (object_id,object_type,object_name,object_fullname,object_lastupd,object_checksum,parent_id)
  select t1.idkey,t1.objtyp,t1.keynam,t4.fullname
    ,coalesce((select upd.infval from psoft_wk_lastupd upd where upd.idobj = t1.idkey),'')
    ,coalesce((select upd.infval from psoft_wk_checksum upd where upd.idobj = t1.idkey),0)
    ,case when T1.objtyp < 1600100 then 0 else (select T2.idparent from keypar T2 where T2.idkey = t1.idkey) end
  from keys t1
    join objpro t2 on (t2.idobj = t1.idkey)
    join objfulnam t4 on (t4.idobj = t1.idkey)
  where t2.idpro = L_IDPRO"
PL/pgSQL function psoft_discrimination_data() line 126 at SQL statement
Batch entry 0 select 01_local.PSOFT_DISCRIMINATION_DATA() was aborted.  Call getNextException to see the cause.

Note - If this error has occurred while running the vanilla Analysis then it is expected to get the same error while running the analysis of project as well since the project as is an extension of the Vanilla (it includes all Vanilla objects). This page is applicable for Vanilla as well as Project analysis.

1. Deliver Vanilla/Project code through DMT

2. Create content enrichment job

3. Run analysis

1. Relevant  input
2. To check whether the parent is not extracted or not analyzed, see check whether the parent is actually missing in .uax file.
3. Identify the uax file which contains the object that is causing the failure
   
4. Search the analysis log for any errors or warnings with respect to this file
   
5. Other Cases

Relevant input

1. Sherlock export

2. Screenshot of the error

Check whether the parent is actually missing in .uax file

From the error message we are sure that the Failing Query is in the file PSoft_SQLDiscriminationData.sql at line 126. If we read the error it means that there is a NULL inserted for column Parent_ID of table psoft_objects.
Run the below query on the Knowledge Base to check if the parent exists for the object -

select T2.idparent from keypar T2 where T2.idkey = <object_id>

If the above row returns 0 rows this confirms that the parent is either not extracted or not analyzed. If the query returns any rows then please contact CAST Technical Support with Relevant Input

Identify the uax file which contains the object that is causing the failure

From the error message take the object_id and execute the below query on the Knowledge Base.

select * from keys where idkey = <object_id>

Take the full name of the object and search the string in all the uax files using any application such as NotePad++ as shown below.

The uax file that contains this string is the file that is not well generated. In the above example, the object CNV_SUM_01.7 is found in file 13.1116.uax
If you do not find the string then contact CAST Technical Support with Relevant Input

Search the analysis log for any errors or warnings with respect to this file

Go to the UI analysis log and search for the file name identified in the above step. To find the location of this log please refer to the page CAST Management Studio - Information - How to find logs

In the example taken in this page the logs must contain any warnings/errors with respect to file 13.1116.uax

After solving the error/warning message in hand navigate to the page listed below

• CMS Snapshot Analysis - Run Analyser - Warnings - PeopleSoft with extension - unresolved reference xx -1 used in property link callee

If the error/warning that you have encountered in your log is not listed in this page then please contact CAST Technical Support with Relevant Input.

 Other Cases

 If you are not able to find reason for your problem in this page then please contact CAST Technical Support with Relevant Input.

Reference Ticket - 3554