Overview

An Andi Tag Language is available to allow users to easily add parameters to queries. When the SQL statement runs with the added Tag, prompts will automatically display which provide the user an opportunity to enter values for configured parameters.

As an example, a SQL statement that needs to be modified prior to each run would be error prone and time consuming to constantly manually modify. By using Tags you can eliminate the unnecessary manual modifications to the SQL statement.

Note: Once a SQL statement has been customized, you can save it to a Custom Query Group and share it with other team members across the organization!

Parameter Entry Dialog

The Parameter Entry Dialog allows you to enter values for variables defined with the @var( ) tag. When the SQL statement runs, a dialog will be presented which allows you to enter one or more values for each of the @var( ) tags. There will be a tree node for each SQL statement, with each node containing the statement text and tags.

There are several options available to select from when working with variables. Right mouse click on any variable to be presented with the following options:

Include – Clause will be included when the SQL statement runs
Operator – Change the operator to an applicable data type for the selection
Check for Null – Add an additional “or <variable name> is null” clause
Check for Not-Null – Values entered will not be used and an “and <variable name> is null” expression will be used in its place
Add Values - Add values to the variable on the fly

Entering Parameter Values

To enter values for a variable double click on the variable, or right mouse click and select Add Values.

The Add Values text box is available to quickly enter in values to add to the variable. Simply type in the value desired, and press the Enter key on your keyboard to add the value. When entering multiple values you can continue to type the values desired and press Enter to add each value to the variable:

Note: Select the Add as Upper Case checkbox to automatically enter your text in upper-case

Entering More Than One Value

If more than one value is entered, an equals operator will be changed to an “in” when the SQL statement is generated. If your value is a character value, you can add one or more “%” signs and a SQL “like” clause will be generated:

Adding Values Using a SQL Statement

If desired, a SQL List Selection dialog is available to quickly create SQL and retrieve data to add as values. Selecting values is very similar to using the Query Editor: simply enter your SQL statement and run the statement to bring back the data for selection.

Select one or more values from the data retrieved to add it to the list of values. The dialog can be used multiple times to add as many values as desired from as many tables as needed. Once values are selected and accepted they will be added to the Parameter Entry.

@WHERE( ) Tag

In the following example, the where token will always be necessary before the “name_id > 500” clause as it will always be included in the generated SQL statement. If you used an @where( ) tag and neither of the @var( ) variables were included, the generated statement would be “select * from name name_id > 500” which is an invalid SQL syntax.

Coded WHERE Clause:

SELECT * FROM Name
WHERE Name_ID > 500
@var(last_name) @var(first_name)

The conditional @where( ) tag is used in place of a SQL where token when the only expressions provided are expressions generated by @var( ) tags. If no variables are included, the SQL where token will not be added to the generated statement.

Conditional WHERE Clause:

SELECT * FROM Name
@where() @var(last_name, include) 
@var(first_name)

@VAR( ) Tag

The @var( ) tag allows for the operators and values in an expression to be changed at run time without modifying the SQL statement. Users can then save SQL statements to Query Groups and rerun them as needed.

Parameter Name	Description	Valid Values	Optional	Default
Column-Name	The name of the column to use when generating the expression	A column name	No
Joiner	How this variable will be joined to an existing where clause expression. If this is the first expression after a where token, the joiner will not be used	and, or	Yes	and
Include	Should the variable be automatically included when generating the SQL statement?	include	Yes
Required	A value must be entered for the variable before the SQL statement can be run	required	Yes
Data-Type	Values entered for a char type will have single quotes added when the statement is generated. If num, additional numeric operators will be available (refer to Operator below).	char, num	Yes	char
Operator	The operator for the column to value comparison. An example would be first_name != ‘Joe’	Char and Num (=, !=) Num Only (=>, >,<,<=)	Yes	=
Null-Check	Should a null or not-null check be made? Example of a null generated clause would be “where (amount > 500 or amount is null)”	null, not_null	Yes
Default-Values	One or more comma separated values, enclosed in single quotes. An example would be “default=’SMITH,JONES’ “		Yes

@var( ) Example and Benefit

This example compares a hard-coded SQL statement to a SQL statement using @var( ) tags. The hard-coded statement must be modified each time before it can be run when changes are needed.

The second statement using the @var() Tag will automatically pop up the Parameter Entry dialog. You then have the option of using the values from the last run or making changes before re-running the SQL statement.

@CLOB( ) And @BLOB( ) Tag

A BLOB is a database column type which stores binary data like image files and audio / video clips. A CLOB file is used to store character data like Text and XML files. Without the Tag capabilities of Andi, inserting BLOB and CLOB types is not an easy task - especially with larger files.

With Andi Tags, you can now easily load the data using a simple SQL insert statement:

@blob(File-Path) or @clob(File-Path)

As an example, the following @blob Tag is used to insert a .jpg file:

INSERT INTO Images (name, content)

VALUES(@blob(/pictures/family.jpg)

@SQLXML( ) Tag

A native XML column type stores XML files as a native data type, without shredding the XML document into multiple tables or columns.

Insert XML Files

With the @SQLXML() Tag users can insert files into the native XML columns:

@sqlxml(filename=File-Path, binary)

Parameter Name	Description	Valid Values	Optional	Default
File-Path	The fully qualified path for the file to be loaded into the XML column	File path	No	-
Binary	Load the XML file without character set conversion	Binary	Yes	-

Insert Simple XML Fragment

If the filename parameter is not present, the first parameter will be interpreted as an XML string. This feature is meant for simple XML fragments. If errors occur, add the fragment to a file and use the filename parameter:

@sqlxml(xmlfragment)

@sqlxml(<document></document>)

@GROUP( ) Tag

The @GROUP( ) Tag requires the user to include at least one of the defined @var( ) variables before the SQL statement can be run.

Parameter Name	Description	Valid Values	Optional	Default
Joiner	How this variable will be joined to an existing where clause expression. If this is the first expression after a where token, the joiner will not be used	and, or	Yes	and
Multi-Selection	By default, only one selection is allowed. Adding multi allows the user to select one or more of the defined variables	multi	Yes	-
Minimum	The minimum number of items that must be selected before the tag will be considered valid	1, n	Yes	1
Variables	One or more variable definitions separated by commas.	@var( ) tags	No - At least two must be included	-

@group( ) Example

The first statement requires that the user include one of the two variables. In the second statement, the multi option requires that a minimum of two variables be included before the statement can be run.

@PAREN( ) Tag

The @PAREN() Tag provides two or more @var( ) tags which will be surrounded by parenthesis if both variables are included in the SQL statement. If no @var( ) variables are included, the parenthesis will not be included in the generated SQL statement.

Parameter Name	Description	Valid Values	Optional	Default
Joiner	How this variable will be joined to an existing where clause expression. If this is the first expression after a where token, the joiner will not be used	And, or	Yes	and
Variables	One or more variable definitions separated by commas	@var( ) tags	No - At least two must be included	-

@paren( ) Example

The parenthesis will be added to the generated SQL statement as shown as long as two or more variables have been included.

@MERGERESULTS( ) Tag

The @MERGERESULTS() Tag allows users to dynamically merge the results of multiple queries together.

To do so, create one or more separated statements and add @mergeResults() to the primary statement. The primary statement will determines the column names which will be returned in the result set and the order of the column names. The secondary statements will be merged into the original result set where column names match the primary statement. If a matching column name was not found a null is inserted.

@mergeresults( ) Example

In the following example, the column name order for the two statements is reversed. When the merge occurs, the secondary columns are merged according to the primary column order.

Note: The @mergeResults( ) cannot be used in conjunction with the @export( ) tag.

@EXPORT( ) Tag

The @EXPORT() Tag will export the result set from a Query to a file. The default tab delimited format of Andi is perfect for exporting the data for import into Excel, Word or another document formatter. If a statement completes successfully, a “Comments” will be displayed for each statement and will contain the export results.

Parameter Name	Description	Valid Values	Optional	Default
File-Path	A fully qualified path for the output file. If a parent directory/directories do not exist, they will be created	-	No	-
Export_clob_as_text	Export CLOB column values as text with the export file. If false, a file reference will be written to the file	True/False	Yes	True
File-Format	The format for the output file	tsv (tab delimited) del (delimited)	Yes	tsv
Delimiter	Delimiter to use for a format=del file (Not applicable to tsv)	One or more characters	Yes	'

@export( ) Example

The result of the result set export will be displayed in the results panel as comment tabs. You can run multiple exports at a time as shown:

Technical Reference Document: Andi Custom Tags