Create or edit the SQL query for an algorithm

Aug 14, 2017

Advanced users can use the Algorithm Implementation dialog box to create or edit the raw SQL query that comprises the logic for an algorithm in a custom report.

The Algorithm Implementation dialog box is split in two horizontally:

• Top part – A text editing box in which you create and edit the SQL query.
• Lower part – Displays the results of the query when you click Test SQL.

Open the Algorithm Implementation dialog box

You can open the Algorithm Implementation dialog box when you create a new custom report algorithm that you define yourself:

1. On the left side of the Custom Reports Manager screen, locate the algorithm group in which you want to create the new algorithm.
2. Right-click the algorithm group and from the shortcut menu, choose New Algorithm.
3. Work through the New Algorithm wizard in the normal way.
4. On the fifth page of the wizard, choose the Advanced option and then click Go to. This opens the Algorithm Implementation dialog box.

For custom report algorithms that were created using the Advanced option, you can open the Algorithm Implementation dialog box like this:

1. Locate the algorithm in the left side of the Custom Reports Manager screen.
2. Right-click and from the shortcut menu, choose Edit implementation.

Note: You cannot open the Algorithm Implementation dialog box for a custom report algorithm based on an algorithm supplied by Citrix.

Specifications

• The SQL query must be a SELECT statement that returns data relating to the applications that trigger the algorithm.
• The first column in the results set must be the application ID.
• You must include the {APP_IDS} tag, which is replaced at run time with the list of currently selected applications. Typically, you put this tag in the WHERE clause.
• If you use the AS syntax to give tables or columns an alias, the alias name must conform to the rules for regular identifiers regardless of whether it is enclosed in brackets ([ ]) or double quotation marks (“ “). For example, an alias name must not contain spaces or apostrophes (‘). See http://msdn.microsoft.com/en-us/library/aa223962(SQL.80).aspx for more information about regular identifiers.

For comprehensive documentation of the SELECT statement, see http://msdn.microsoft.com/en-us/library/aa259187(SQL.80).aspx.

Caution: Citrix cannot guarantee that queries you write will work in future versions of AppDNA, because the structure of the database may change from version to version.

Example

When you open the dialog box when creating a new algorithm, the top part of the dialog contains an example query as follows:

pre codeblock 1 SELECT mf.[application_id], mf.[long_filename] , mf.[target_path], mf.[version], mf.[version_number], mf.language 2 FROM [dbo].[msi_file] mf 3 4 WHERE 5 ( 6 mf.[language] NOT LIKE '%1033%' 7 AND 8 (mf.[language] != '0' 9 AND 10 mf.[language] is NOT null 11 AND 12 mf.[language] != '' 13 ) 14 ) 15 AND mf.application_id IN ( {APP_IDS} ) 16 ORDER BY mf.[application_id]

This retrieves all the applications that contain any files that are not US English.

Lines 1-2 – Selects six named columns from the dbo.msi_file table, which is given the alias mf.

Line 3 – The WHERE clause defines a filter that restricts the results to those that meet the following criteria:

• Lines 6-12 – The value in the language column does not match the language code for US English and is not blank, zero or Null.
• Line 15 – And the application ID is included in the list of currently selected application IDs that replace the {APP_IDS} tag at run time.
• Line 16 – Orders the results by the application ID.

This example provides a starting point only and is not meant to be prescriptive. You can use SQL Server Management Studio to browse the tables in the AppDNA database. However, note that Citrix cannot guarantee that queries you write will work in future versions of AppDNA because the structure of the database may change from version to version.