Progress
Version 9
Product Update Bulletin
QUOTER Function
Converts the specified data type to CHARACTER and encloses the results in quotes when necessary.
The QUOTER function is intended for use in QUERY-PREPARE where a character predicate must be created from a concatenated list of string variables to form a WHERE clause. In order to process variables, screen values, and input values so that they are suitable for a query WHERE clause, it is often necessary to enclose them in quotes. For example, European-format decimals and character variables must always be enclosed in quotes. You can use the QUOTER function to meet that requirement.
The QUOTER function has the following syntax:
SYNTAX
expression
An expression in the data type that you want to convert to character and enclose with quotes.
quote-char
Either a single or double quote, enclosed in the opposite: "'" or '"'. The default is double quote. Passing ? for this argument results in double quotes.
null-string
The string you want for UNKNOWN: the word NULL or "" for example. The default is an unquoted ? character.
For example, the following:
Would produce this prepare-string:
NOTES
- To address the situation where an UNKNOWN value in a list of concatenated strings could cause the entire string to be UNKNOWN and the QUERY-PREPARE to fail, the QUOTER function does not return UNKNOWN if the <expression> argument is UNKNOWN. Instead, it returns a known character value consisting of an UNQUOTED question-mark, by default, or the 3rd argument, if it is present.
Also, in this situation, a quoted question-mark is not used because it is interpreted as string data in a WHERE clause. After the concatenation is complete, Progress supplies a normal question mark.
For example, the following:
Would produce this prepare-string:
However, giving the 3rd parameter as
"NULL"
producesNULL
rather than?
.- For noncharacter data types, if <expression> is of type DECIMAL, INTEGER, DATE , and so on, the following occurs:
- The <expression> is converted to character and enclosed in quotes. The conversion is similar to the EXPORT format. DATE types, however, always have the 4-digit year.
- Data types with no DISPLAY format like MEMPTR and LVARBINARY return the same question mark as UNKNOWN.
- If a data type is of type RAW, it is converted to base 64.
For example, the following:
Would produce this prepare-string:
This is especially important for European format decimals that look like 12,34 and would not compile in the above statement unless they are enclosed in quotes.
- If <expression> is of data type CHARACTER, internal quotes are doubled. If the first and last byte are already quotes, then it is assumed that the quoting has already been done, and no further quotes are applied.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |