CalcObject Function Search()
CalcObject Function Search()
SEARCH() is a number-valued function that performs a limited FIND
operation within the context of a Calc Object.
Overview of SEARCH() function
The general case for the SEARCH() function is:
<statement> SEARCH(arg1,arg2,SEARCH()) <op> SEARCH(arg1,arg2,SEARCH)) ...
that is:
1) Multiple statements containing SEARCH()
2) Multiple SEARCH() functions per statement
3) SEARCH() function may be nested 1 level in arg3
The SEARCH() function has three forms:
Form 1: Evaluate scalar compare operator
Form 2: Evaluate EXISTS operator
Form 3: Evaluate scalar aggregate calculation
Form 1: Scalar compare operator
|
Form 1 of the SEARCH() function will perform any of the basic scalar compare operations, modified
by the SOME/ANY/ALL modifiers, against a collection.
SEARCH() returns a non-zero value if the search expression has been
satisfied; it returns zero if the search condition has not been not satisfied.
|
Syntax:
SEARCH(<value>,<search expression>,<outer reference filter>)
<value> is the value to be searched for; it can be numeric or
alpha; but it must match the type of the <search expression>.
<value> can be a constant, field or variable;
<search expression> is in the form:
<collection>
<table name> - if <table name> is omitted, the global collection is assumed;
<field name>
<operator> {=, EQ, <>, NE, >, GT, >=, GE, <, LT, <=, LE}
[<modifier>] {SOME | ANY | ALL}
<outer reference filter> this parameter can optionally be a string that specifies
an outer reference condition; if this parameter is present, then both the
<search expression> and the <outer reference filter> must be true before the
SEARCH() function returns non-zero.
If this parameter is not being used, it is specified as numeric zero.
---------------------------------------
SQL Example [Query 49 from Queries-NestedSelect.txt]
---------------------------------------
SELECT EMPNUM, GRADE*1000
FROM STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM PROJ
GROUP BY CITY, PTYPE
HAVING PROJ.CITY = STAFF.CITY)
---------------------------
TQL For Nested SELECT
---------------------------
FILL A PROJ
SORT A PROJ CITY PTYPE
CREATETEMPTABLE
DATA BASE NAME IS TT1
TD ""."TT1" AS T99 100 Records TEMP
""."TT1"."TBUDGET" AS T99C1
""."TT1"."CITY" AS T99C2
CREATECALCOBJECT CO1 PROJ
RETURN
EVERY CITY PTYPE
CALC TOTALBUDGET = BUDGET[SUM]
PRINT TOTALBUDGET CITY
CREATEALIAS TT1 CITY PROJ.CITY
CREATEALIAS TT1 TBUDGET TOTALBUDGET (CO1)
GETCALC A PROJ NULL CO1 TT1
DESTROYALIAS TT1 CITY
DESTROYALIAS TT1 TBUDGET
---------------------------
TQL For Outer SELECT
---------------------------
FILL A TT1
FILL B STAFF
CREATECALCOBJECT CO2 STAFF
RETURN
CALC G1000 = GRADE * 1000
IF SEARCH(G1000,'A TT1 TBUDGET > ANY','TT1.CITY = STAFF.CITY') NE 0
PRINT EMPNUM G1000
ENDIF
Form 2: EXISTS operator
|
Form 2 evaluates the EXISTS and NOT EXISTS operators against a collection.
SEARCH() returns a non-zero value if the search expression has been
satisfied; it returns zero if the search condition has not been not satisfied.
|
Syntax:
SEARCH(0,<EXISTS expression>,<outer reference filter>)
<search expression> is in the form:
<collection>
<table name> - if <table name> is omitted, the global collection is assumed;
[<field name>] - optional
<operator> {EXISTS, NOT EXISTS}
<outer reference filter> this parameter can optionally be a string that specifies
an outer reference condition; if this parameter is present, then both the
EXISTS condition and the <outer reference filter> must be true before the
SEARCH() function returns non-zero.
If this parameter is not being used, it is specified as numeric zero.
The SEARCH() function for EXISTS/NOT EXISTS with an outer reference
filter is optimized if the outer reference filter has the form:
<local-reference-1> <op-1> <outer-reference-1> AND
<local-reference-2> <op-2> <outer-reference-2> AND ...
that is:
. all <local-reference> and <outer-reference> are simple references;
. each phrase contains exactly one <local-reference> and one <outer-reference>
. <op> is {=, <>, >, <, >=, <=}
. logical op is AND
---------------------------------------
SQL Example [Query 11 from Queries-Having.txt]
---------------------------------------
SELECT PNUM, SUM(HOURS) FROM WORKS
GROUP BY PNUM
HAVING EXISTS (SELECT PNAME FROM PROJ
WHERE PROJ.PNUM = WORKS.PNUM AND
SUM(WORKS.HOURS) > PROJ.BUDGET / 200);
---------------------------
TQL For Nested SELECT
---------------------------
FILL A PROJ
---------------------------
TQL For Outer SELECT
---------------------------
FILL B WORKS
SORT B WORKS PNUM
CREATECALCOBJECT CO1 "WORKS"
RETURN
EVERY PNUM
IF SEARCH(0,'A PROJ PNUM EXISTS','PROJ.PNUM = WORKS.PNUM AND WORKS.HOURS[SUM] GT (PROJ.BUDGET/200)') NE 0
PRINT PNUM WORKS.HOURS[SUM]
ENDIF
GETCALC B WORKS NULL CO1
Form 3: Scalar aggregate calculation
|
Form 3 of the SEARCH() function will perform any of the basic scalar compare operations
against a collection and calculate an aggregate on those that meet the condition.
SEARCH() returns a non-zero value if the compare operation has been
satisfied; it returns zero if the compare operation has not been not satisfied.
|
Syntax:
SEARCH(<value>,<search expression>,0)
<value> is the value to be searched for; it can be numeric or
alpha; but it must match the type of the <search expression>.
<value> can be a constant, field or variable;
<search expression> is in the form:
<collection>
<table name> - if <table name> is omitted, the global collection is assumed;
<field name>
<operator> {=}
<assignment> {SET v = f[aggregate], CALC v = f[aggregate]}
---------------------------------------
SQL Example [Exsalgo sample query]
---------------------------------------
SELECT ph_datetime, pcp_compname, ph_attributevalue
FROM PERF_HISTORY ph
LEFT JOIN PERF_COMP ON pcp_compkey = ph_compkey
WHERE pcp_classkey = 207 AND ph_propertykey = 10
and ph_datetime = (SELECT max(ph_datetime)
FROM PERF_HISTORY sub1
LEFT JOIN PERF_COMP ON pcp_compkey = ph_compkey
WHERE ph_propertykey = 10
and sub1.ph_compkey = ph.ph_compkey)
---------------------------
Evaluate OUTER Select without HAVING into A (for example)
---------------------------
---------------------------
Evaluate INNER Select without HAVING;
---------------------------
FIND B PERF_SUB1 where PH_PROPERTYKEY = 10
MATCH B SUB1 THRU PH_COMPKEY WITH PERF_COMP ON PCP_COMPKEY
RELATEX B ...
---------------------------
GetCalc() OUTER Select with HAVING clause on VDATE
---------------------------
CREATECALCOBJECT CO2 A "RETURN // -
IF SEARCH(PERF_HISTORY.PH_COMPKEY,'B SUB1.PH_COMPKEY = SET VDATE = SUB1.PH_DATETIME[MAX]',0) NE 0 // -
IF PH_DATETIME =VDATE // -
PRINT <SELECT List ...> //
GETCALC A NULL RD CO2
