Parse_Parameter

The method Parse_Parameter compares a column value against a parameter (query style) and returns the result. There are 3 interfaces. The methods for VARCHAR2 and NUMBER type column values work the same way . The method for the data type DATE has an additional parameter where the date value can be compared against a specific format.

Interfaces

Parse String/Number type column value

Parse Date type column value

Parse String/Number type column value

Parse_Parameter(column_ IN VARCHAR2,
                parameter_ IN VARCHAR2)  RETURN VARCHAR2
Parse_Parameter(column_ IN NUMBER,
                parameter_ IN VARCHAR2)  RETURN VARCHAR2

Purpose

This function compares a column value against a parameter (query style) and returns the result, 'TRUE', 'FALSE' or 'ERROR'.

Parameters

Name Description
column_ The column in a select expression to compare values by.
parameter_ Values to parse into a where expression, 'Query dialog' style.

Returns

Returns 'TRUE' if the comparison matches, 'FALSE' if it doesn't match, and 'ERROR' if there was a 'compilation error'. Comparison is data type sensitive.

Pragma

WNDS, WNDPS.

Comments

This interface is useful when simplifying advanced where expressions, especially in reports. The comparison is 'Query Dialog' style, which means that one can combine different methods of comparison. F ex:

  1. Items separated by ; will be combined using OR
  2. Items including % or _ will be matched using LIKE operator
  3. Items including .. (double dot) will be compared using the BETWEEN operator. Although not generally correct, it does allow for the second value being smaller than the first.
  4.  Data type in the comparison is always determined by the first parameter, the 'column'. If the 'column' is a number, then numeric comparison will be used which differs slightly from string comparison.

Although easy to use, the performance for this method when used on big tables will  be poor because indexes can not be used.

Example

a_ VARCHAR2(2000) := 'ABC;DEF;K..M';
b_ VARCHAR2(2000) := '0..12;32%'
CURSOR get_rec IS
   SELECT a,b,c
   FROM &VIEW
   WHERE Report_SYS.Parse_Parameter(a, a_) = 'TRUE'
   AND Report_SYS.Parse_Parameter(b, b_) = 'TRUE';

Parse Date type column value

Parse_Parameter(column_ IN DATE,
                parameter_ IN VARCHAR2,
                format_ IN VARCHAR2 DEFAULT 'DATE' ) RETURN VARCHAR2

Purpose

This function compares a column value against a parameter (query style) and returns the result, 'TRUE' or 'FALSE'.

Parameters

Name Description
column_ The column in a select expression to compare values by.
parameter_ Values to parse into a where expression, 'Query dialog' style.
format_ An optional value  for special date format comparisons.
Format specification:> 'DATE' - Compare date, string format is Report_SYS.date_format_
'TIME' - Compare time, string format is Report_SYS.time_format_
'DATETIME' - Compare both date and time, string format is Report_SYS.datetime_format_
Default value is 'DATE'39;39;39;39;

Returns

Returns 'TRUE' if the comparison matches, 'FALSE' if it doesn't match, and 'ERROR' if there was a 'compilation error'. Comparison is datatype and possibly format sensitive.

Pragma

WNDS, WNDPS.

Comments

This interface is useful when simplifying advanced where expressions, especially in reports. The comparison is 'Query Dialog' style, which means that one can combine different methods of comparison. F ex:

  1. Items separated by ; will be combined using OR
  2. Items including % or _ will be matched using LIKE operator
  3. Items including .. (double dot) will be compared using the BETWEEN operator. Although not generally correct, it does allow for the second value being smaller than the first.
  4. Data type in the comparison is always date, as determined by the first parameter, the 'column'. The extra parameter may be used when the comparison should be performed using a specific format. Acceptable formats are DATE, TIME and DATETIME and the default or 'unrecognized' value is 'DATE'. If the actual format is needed, it is publicly declare in package Report_SYS as constants: date_format_, time_format_ and date_time_format_. (for parameters to be validated and entered correctly in the report order dialog  and arrive to the report method in a proper format, it is necessary that the corresponding format is set for the parameter, e.g. 'DATATYPE=DATE/DATETIME^)

Although easy to use, the performance for this method when used on big tables will  be poor because indexes can not be used.

Example

d_ VARCHAR2(2000) := '1999-12-31;2000-01-01..2001-01-01'
CURSOR get_rec IS
   SELECT a,b,c,d
   FROM &VIEW
   WHERE Report_SYS.Parse_Parameter(d, d_, 'DATE') = 'TRUE';
or

d_ VARCHAR2(2000) := '1999-12-31;2000-01-01..2001-01-01'
CURSOR get_rec IS
   SELECT a,b,c,d
   FROM &VIEW
   WHERE Report_SYS.Parse_Parameter(d, d_) = 'TRUE';