2 Replies Latest reply on Jul 28, 2010 5:27 AM by Alexander.Rabenstein

    SQL which shortcut item opens which query

    Rookie

      Hi,

       

      I need a SQL statement which show me which shortcutitem is linked to which query, to find queries not used anymoore.

       

      I found the shortcuts in table ui_shortcut_item, but I was not able to find out how that table is connected to the queries.

      What I have seen is that there is a column ui_data containing something like this when the shortcuts links to a query:

       

      <?xml version="1.0" encoding="utf-8"?><QueryResultsViewInit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><LaunchAsNewWindow>false</LaunchAsNewWindow><DefinitionGuid>712ce19d-cad7-4edb-85d2-c8682e23383a</DefinitionGuid><DefinitionType>Query</DefinitionType><DefinitionUsage>System</DefinitionUsage><DefinitionTitle>Eigene Calls im Status With Customer / With 3rd Line</DefinitionTitle><QvIsTelephonyAware>false</QvIsTelephonyAware><IsTelephonyAware>false</IsTelephonyAware><QvIsTelephonyAutoAnswer>false</QvIsTelephonyAutoAnswer><IsTelephonyAutoAnswer>false</IsTelephonyAutoAnswer></QueryResultsViewInit>

       

      I was able to extract that guid with an select:

      select sys.xmltype(ui_data).extract('/QueryResultsViewInit/DefinitionGuid/text()').getStringVal() DefinitionGuid from ui_shortcut_item where ui_component = 'QueryTool';

       

      But I dont to which table this guid references?