Previous Topic

Next Topic

Book Contents

Book Index

Generate Text

The Generate Text tool can be used to generate any text that contains table and/or column names. You specify a “skeleton text” specification – AQT will substitute (and repeat) the table and column names at the places you specify.

This can be used for generating:

This tool is accessed by Tools > Generate Text from the Database Explorer. The Generate Text tool operates on the contents of the Object Display and Details Display of the Database Explorer, so make sure these are populated.

Generate Text is a very powerful tool. Once you become familiar with how it works, it can save you a large amount of time.

The best way of understanding how this works is by an example. In the text window type Table name is $2 then click on Generate. The following text will be generated:

Table name is Cust_Orders

Table name is Customer_Contact

Table name is Customer_Details

Table name is Order_Details

Table name is Order_Line

Table name is Part_Details

Notice that where you coded $2 the table name has been substituted, and this line has been repeated for every table in the table list.

For other example of the use of this, click on the Examples drop-down list in the Generate Text Window, and select one of the items. These will show you the types of things you can generate with this utility (though they may not necessarily be examples of working code).

The substitutional parameters you can specify are as follows. These relate to the values displayed in the grids in the Database Explorer:

Parameter

Description

Note

$1

Schema name

value in Database Explorer

$2

Table name

value in middle grid

$3

Column name

value in right grid

$4 - $9

Type, Length, Scale etc

other columns in right grid

$-3

Column names with underscores replaced with minus signs

For COBOL users only. This will give you the Cobol host variables names.

$0

Number of table/column names to be generated.

 

$n

Sequential number of the table/column name

 

$fn

A function

see the following Functions section for options

<t>

Tab character

 

Text generation options

There are a couple of options for the way the text is generated. The examples demonstrate the use of this. Alternatively you can try them to see their effect.

Repeat What?

This option is only relevant when your text consists of more than one line. It specifies what to repeat when you code, say, a $3 in a line – just the line containing the $3, or the entire text.

Delimit Generated Names With

Set the delimiter to be used when a parameter is substituted and repeated. Options are:

Options

Result

Notes

newline

each item is placed on a new line

 

comma plus newline

when using this option you won't get a comma after the last item

This can be useful when generating a comma-delimited set of names. It is preferable to use this rather than putting a comma in your skeleton text

comma

actually comma plus blank

 

tab

 

Useful if you are generating a list of names to be pasted into Excel.

none

 

 

Functions

You can use the $fn keyword to specify functions to do simple string manipulation and conditional processing. The syntax of this is:

$fn(function, parm1, parm2, parm3, parm4)

The number of parms required depends on the function. Strings do not need to be in quotes.

The following functions have been implemented:

$fn(lcase,x)

converts x to lower case

$fn(ucase,x)

converts x to upper case

$fn(scase,x)

converts x to sentence case

$fn(trim,x)

removes leading and trailing blanks

$fn(repl,x,y,z)

replaces all occurrences of string y in x to z

$fn(min,x,i)

returns the minimum of x and i

$fn(max,x,i)

returns the maximum of x and i

$fn(left,x,i)

returns the left-most i characters of x

$fn(right,x,i)

returns the right-most i characters of x

$fn(mid,x,i,j)

returns a substring from x, starting at i, length j

$fn(instr,x,y,z,w)

returns z if x contains string y, otherwise returns w

$fn(ifblank,x,y,z)

returns y if x is blank, otherwise returns z

$fn(ifnum,x,y,z)

returns y if x is a numeric data type, otherwise returns z

$fn(ifeq,x,y,z,w)

returns z if x = y, otherwise returns w

$fn(decode,x,comp1,val1,comp2,val2,val3)

if x=comp1, returns val1. if comp2=val2, returns val2. Up to 8 sets of value can be compared. If no values matched, returns the final value. Example: $fn(decode,$4,varchar,c,integer,i,datetime,d,other)

$fn(after,x,y,spos,n)

Searches string x for the nth occurrence of string y and returns the text after this occurrence of string y.

spos gives the start position for the search. If negative, the search will be done from right to left starting from the end of the string. spos is optional, if not given a value of 1 will be used.

n is optional and defaults to 1.

If string y is not found anywhere in string x, all of string x is returned. If string y is found, but occurs less than n times, the last position where string y is found will be used.

Examples:

$fn(after,$2,"_") returns text after the first "_"

$fn(after,$2,"_",-1,2) returns text after 2nd "_" from right

$fn(before,x,y,spos,n)

Similar to after, except that the text before the location of the search text is returned

<delim>spec

This allows you to change the delimiter while your script is running. spec can take values <cr> (new line), <b> (blank) <tab> tab.

Example: <delim><cr>

Notes:

Save / Retrieve

These options can be used to Save/Retrieve your text generation specification.

Font

This allows you to set the Font in the text box to non-proportional (eg. fixed width) font. This is useful if you want to line up multiple lines (such as when you are generating code for Cobol).

Generate to File

Use this if you want to output the text generation directly to a file. This is useful if your output is large (> 64 kb), as text larger than this cannot be displayed in the result text-box.

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.