Unable to update stored lookup NSQL if schema changed

Document ID : KB000028813
Last Modified Date : 14/02/2018
Show Technical Document Details

Description: 

When trying to update NSQL for a lookup that had schema changes (custom table got dropped) it will not allow to update NSQL.

Steps to Reproduce:

1. Create Lookup based on NSQL 

2. Drop custom table that this lookup is based on 

3. Try to update NSQL for lookup

Expected Result: should be able to update NSQL for the lookup

Actual Result: not allowing, throwing an error.

Solution:

WORKAROUND:

1. Create a new look-up exactly like the old one with a different ID and 

proper SQL for oracle. 

2. Search for IDs of the queries in the database by searching for custom 

ones and determined the ID for the new NSQL and the original NSQL. 

select * from CMN_NSQL_Queries where Last_updated_by > 1 

order by last_updated_date Desc 

3. Update the Original NSQL with the NSQL text from the New working NSQL 

Update cmn_nsql_queries set NSQL_text = ( select NSQL_text from 

CMN_NSQL_Queries where ID = 5094040) 

Where id = 5018001 

4. Delete the New _test look-up via the Clarity UI

 

STATUS/RESOLUTION:

CLRT-73094 Resolved in CA PPM 14.1