Clarity

Hello All,

In 14.2 we had been using a Dynamic look up for testing SQL queries and never had any issues with saving it as we would never actually use it anywhere else.

We recently upgraded to 14.4 and now I can only create a Dynamic Lookup and save it once. Essentially making it impossible to change the query for the lookup without making a new lookup.

This is the error i receive -

Error ODF-0001: Unable to register dynamic query lookup:

Could not retrieve or register the nsql::NsqlLookupHandler::updateNsql()

=>Can't update lookup: TEST as it's either system or in use.

Any ideas how to fix this? It's quite time consuming to create a new dynamic lookup anytime i want to test a sql query?

Yesterday I experienced that same with the Community test Sandbox which has been updated from 14.2.

Until now I thought more that it is a feature of the heavily modified system.

I tried the same on prod sandbox which is a fresh install and did not experience that at all.

Yes it is a pain.

What I was doing was just testing and using the lookup in question just as a query tool, so it was not a big deal to delete it in order to enter a modified version of the query in a new lookup. I don't think it allowed any kind of modification (not even changing a letter from small to capital).

Open a case with support.

There was some odd-behaviour with dynamic lookups in 14.3 ; discussed here ; Problem to change a dynamic lookup query

That isn't the same problem though (the 14.3 issue involved not having the 'Save' option in the application), but there might be similar underlying "problems"?  GUESSing here of course, we need someone from CA-development to comment really.

Implementing fixes which cause other problems does not sound very professional.

Just wondering if there was a difference with the other problem with between upgrade and fresh install.

I am not in front of a 14.4 environment so can't test this myself, but are you able to update the lookup through XOG or is it the same error?

-Sankhadeep

We are on 14.3, where edits to Look-ups are also now not possible.

Try XOG'ing out the Lookup, modify the XML output and XOG back in again.

Had to do this yesterday on 14.3 and worked for me.

Note, always do on testing environment first.

Just tested on 14.3 and don't get an error messages but I cannot adjust the lookup query anymore. :-(

After continue it does not display the new field.

Did you edit the query in the XML output file from the XOG?  And the XOG back into your test environment?

I'm also confused why you are testing a sql query this way (ie via a look-up).  I generally write my Studio Queries as:

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:INV:D.CODE:CODE@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:D.NAME:NAME@

from

(select

i.code,

i.name

FROM     INV_INVESTMENTS I

) d

where      @FILTER@

Above code has not been tested, just using as an example.

This way I can pull out the real query

select

i.code,

i.name

FROM     INV_INVESTMENTS I

and run in query tool like TOAD, SQLDeveloper which I'm used to and then do my QA on the data.

Hi R.McIntire - Did any of the responses help answer your question? If so please mark as Correct Answer. Thanks!

As long as people keep getting different results with this in the same version I do not think there can be declared an answer.

I'm currently working with CA Support on this issue. No solution as of yet.

As a side note, clearing the system cache temporarily resolves the issue; but it eventually shows up again.

The common reason for using lookups for queries is that you do not have database access and cannot run the queries in a query tool. Using a lookup allows you to see the results which NSQL query preview does not.

Tested dynamic lookup query modification on my 14.3.

No problem. That is an upgrade so inconsistent result compared with the Community sandboxes where I had the problem with the upgrade, but not with fresh install.

No problem with my upgraded 14.4 either.