CA Clarity Tuesday Tip: How to run SQL queries from Clarity?

[Shyam Beerakayala]

How to run SQL queries from Clarity even when you do not have database access?

The below steps can be used to run the sql queries (only select queries):

You need to have Clarity Administration access for this procedure.

From Administration:

1. Under Data Administration, click on Lookups

2. Click on New button to create a new lookup

3. Give a Lookup Name and Lookup Id

4. From Source, choose Dynamic Query option

5. Click Submit (Save and Continue in v13) button

6. In the Lookup Properties window, in the text box for Niku Query, enter your SQL, something like:
(the SQL has to be select queries only)

select * from cmn_install_history

or

select * from cmn_install_history where install_id = 'database'

7. Click on Save and Continue button

8. Click on Save and Continue button again in the Parent Window

9.From the Browse Window, click on the Preview button

This will open a popup window with the query results in a grid.

10. Once you are done with this, you can delete the lookup from the Lookup list page

Shyam Beerakayala
Principal Support Engineer

[Deactivated User]

Thanks for posting that.

If your admin has restricted your access so that you do not have access to lookups there is an option:
Create a NSQL query and a portlet from that. In v13 you can create also system data provider based portlets on user portlet page.
More work than lookups, but doable.

Martti K.

[System]

Thanks for the great tip Shyam! :grin:

[Anon Anon]

Thanks for posting this, I knew this was possible for a while now, I just wasn't able to find it documented anywhere.

[Anon Anon]

Great tip. The neat thing about this is that you can write the query in ANSI format. In other words, no need to wrap your query in NSQL.

This is a quick way to verify your SQL code for a GEL script or creating a Portlet. It works in v13 also!

Juan

[Anon Anon]

Can this method be applied if we want to run GEL scripts as well? Or if i should be asking how can I run/test GEL scripts.

[Anon Anon]

cshah wrote:

Can this method be applied if we want to run GEL scripts as well? Or if i should be asking how can I run/test GEL scripts.

1) Nope - the comment earlier in the thread about GEL was really just saying that you can "test" SQL* that you might want to execute in a GEL script using this trick. ( * - when you don't have access to the actual database).

2) You can run GEL from the command line (eg Windows CMD) or via a .BAT file in Windows. You need to set up all the XOG client download to get the right stuff on your PC to do this. The various documents in the GEL.ZIP file attached to the FAQ THREAD on the General Discussion board has some good examples in it that should get you started.