|
|
|
Use row value constructors for comparisons in query statements.
Example
ISQL> table rvc
COLNAME NULL ? TYPE LENGTH CHARSET NAME COLLATION
------- ------ --- ------ ------------ ---------
c1 INT 4
c2 INT 4
c3 INT 4
ISQL> select * from rvc;
C1 C2 C3
-- -- --
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
2 1 1
2 1 2
2 1 3
2 2 1
2 2 2
2 2 3
2 3 1
2 3 2
2 3 3
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
3 3 3
27 records selected
ISQL> create index rvc_idx on rvc(c1,c2,c3);
ISQL> select * from rvc where c1 > 2 and c2 > 2 and c3 > 2;
C1 C2 C3
-- -- --
3 3 3
1 record selected
ISQL> select * from rvc where (c1,c2,c3) > (2,2,2);
C1 C2 C3
-- -- --
2 2 3
2 3 1
2 3 2
2 3 3
2 3 1
2 3 2
2 3 3
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
3 3 3
13 records selected
select * from rvc where c1 < 2 and c2 < 2 and c3 < 2;
C1 C2 C3
-- -- --
1 1 1
1 record selected
select * from rvc where (c1,c2,c3) < (2,2,2);
C1 C2 C3
-- -- --
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
2 1 1
2 1 2
2 1 3
2 2 1
13 records selected
select * from rvc where c1 <= 2 and c2 <=2 and c3 <= 2;
C1 C2 C3
-- -- --
1 1 1
1 1 2
1 2 1
1 2 2
2 1 1
2 1 2
2 2 1
2 2 2
8 records selected
select * from rvc where (c1,c2,c3) <= (2,2,2);
C1 C2 C3
-- -- --
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
2 1 1
2 1 2
2 1 3
2 2 1
2 2 2
14 records selected
|
|
|
To perform a phrase match, the phrase must be enclosed in single quotation marks per FTS query syntax defined at the c-treeDB level. In SQL, that mandates two single quotes since the entire query is surrounded by a single quote (because it is a literal). Therefore, quotes need to be escaped in the standard way (that is double them).
(This implies the entire query is a phrase match since the final query is made by 3 single quotes, the phrase and 3 single quotes.)
SELECT * FROM docs WHERE document MATCH '(''faircom'' AND ''database'')'
For convenience, the FairCom DB SQL syntax now allows wrapping phrases within the FTS query literal with double quotes (that do not need to be escaped).
SELECT * FROM docs WHERE document MATCH '(“faircom” AND “database”)'
Both the old and the new methods (double quote or double single quotes) of specifying phrase search can be used for SQL FTS queries.
