Product Documentation

FairCom RTG V3 Update Guide

Previous Topic

Next Topic

Use Row Value Constructors with Comparisons in Query

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

Previous Topic

Next Topic

Easier Full-Text Search (FTS) MATCH Operator Syntax

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.

TOCIndex