clarion_functions_used_in_sql_filter_statements.htm
The following diagram shows in detail the conversion of supported Clarion functions with Clarion 6.3 and Clarion Win32 and prior versions to their SQL equivalents.
With the new PROP:ServerCaseInsensitive set to 0 in version 6.3, the SQL behavior is equivalent to Clarion 6.2
Clarion Code: | UPPER(var) |
6.3/7 SQL Drivers | Var |
6.3/7 Oracle | Var |
6.2 SQL Drivers | {fn UCASE(var)} |
6.2 Oracle | UPPER(var) |
Clarion Code: | LOWER(var) |
6.3/7 SQL Drivers | Var |
6.3/7 Oracle | Var |
6.2 SQL Drivers | {fn LCASE(var)} |
6.2 Oracle | LOWER(var) |
Clarion Code: | NOT var |
6.3/7 SQL Drivers | NOT var |
6.3/7 Oracle | NOT var |
6.2 SQL Drivers | NOT var |
6.2 Oracle | NOT var |
Clarion Code: | “SUB(var, 1, LEN(var) = constant” |
6.3/7 SQL Drivers | var LIKE 'constant%' |
6.3/7 Oracle | var LIKE 'constant%' |
6.2 SQL Drivers | var LIKE 'constant%' |
6.2 Oracle | var LIKE 'constant%' |
Clarion Code: | “MATCH(s1, s2, Match:Simple) <;> 0” |
6.3/7 SQL Drivers | s1 = s2 |
6.3/7 Oracle | s1 = s3 |
6.2 SQL Drivers | s1 = s4 |
6.2 Oracle | s1 = s5 |
Clarion Code: | “MATCH(UPPER(s1), UPPER(s2), Match:Simple) <;> 0” |
6.3/7 SQL Drivers | s1 = s2 |
6.3/7 Oracle | s1 = s3 |
6.2 SQL Drivers | {fn UCASE(s1)} = {fn UCASE(s2)} |
6.2 Oracle | UPPER(s1) = UPPER(s2) |
Clarion Code: | “MATCH(s1, s2, Match:Wild) <;> 0” |
6.3/7 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
6.3/7 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
6.2 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
6.2 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
Clarion Code: | “MATCH(UPPER(s1), UPPER(s2), Match:Wild) <;> 0” |
6.3/7 SQL Drivers | s1 LIKE s2 (with ? Replaced with _ and * with %) |
6.3/7 Oracle | s1 LIKE s2 (with ? Replaced with _ and * with %) |
6.2 SQL Drivers | {fn UPPER(s1)} LIKE s2 |
(s2 is upper cased and ? replaced with _ and * with %) | |
6.2 Oracle | UPPER(s1) LIKE s2 |
(s2 is upper cased and ? replaced with _ and * with %) | |
Clarion Code: | “MATCH(s1, s2, Match:Soundex) <;> 0” |
6.3/7 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
6.3/7 Oracle | SOUNDEX(s1) = s2 |
6.2 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
6.2 Oracle | SOUNDEX(s1) = s2 |
Clarion Code: | “MATCH(UPPER(s1), UPPER(s2), Match:Soundex) <;> 0” |
6.3/7 SQL Drivers | {fn SOUNDEX(s1)} = s2 |
6.3/7 Oracle | SOUNDEX(s1) = s2 |
6.2 SQL Drivers | {fn SOUNDEX({fn UPPER(s1)})} = {fn UPPER(s2)} |
6.2 Oracle | SOUNDEX(UPPER(s1)) = UPPER(s2) |
Clarion Code: | Constant with non-displayable character |
6.3/7 SQL Drivers | Non-displayable characters are converted to: |
{fn CHAR(ordinal value of character)} | |
6.3/7 Oracle | Non-displayable characters are converted to: |
CHAR(ordinal value of character) | |
6.2 SQL Drivers | Non-displayable characters are converted to: |
{fn CHAR(ordinal value of character)} | |
6.2 Oracle | Non-displayable characters are converted to: |
CHAR(ordinal value of character) | |
Clarion Code: | INSTRING('substring', var, instring(string, var,1,1) <;> 0 |
Clarion Code: | INSTRING('substring', var, instring(string, var,1) <;> 0 |
Clarion Code: | INSTRING('substring', var, instring(C, var) <;> 0 |
(where C is a single character string) | |
6.3/7 SQL Drivers | var LIKE '%string%' |
6.3/7 Oracle | var LIKE '%string%' |
6.2 SQL Drivers | var LIKE '%string%' |
6.2 Oracle | var LIKE '%string%' |
Clarion Code: | NULL(field) = 0 or <;> 1 |
6.3/7 SQL Drivers | field IS NOT NULL |
6.3/7 Oracle | field IS NOT NULL |
6.2 SQL Drivers | field IS NOT NULL |
6.2 Oracle | field IS NOT NULL |
Clarion Code: | NULL(field) = 1 or <;> 0 |
6.3/7 SQL Drivers | field IS NULL |
6.3/7 Oracle | field IS NULL |
6.2 SQL Drivers | field IS NULL |
6.2 Oracle | field IS NULL |
Clarion Code: | date fields |
6.3/7 SQL Drivers | {d yyyy-mm-dd} |
6.3/7 Oracle | “TO_DATE('dd-mm-yyy', 'DD-MM-YYYY')” |
6.2 SQL Drivers | {d yyyy-mm-dd} |
6.2 Oracle | “TO_DATE('dd-mm-yyy', 'DD-MM-YYYY')” |
Clarion Code: | time fields |
6.3/7 SQL Drivers | {t hh:mm:ss} |
6.3/7 Oracle | not supported by Oracle |
6.2 SQL Drivers | {t hh:mm:ss} |
6.2 Oracle | not supported by Oracle |
Clarion Code: | timestamp fields |
6.3/7 SQL Drivers | {ts yyyy-mm-dd hh:mm:ss} |
6.3/7 Oracle | “TO_DATE('dd-mm-yyy hh:mm:ss', 'DD-MM-YYYY HH:MI:SS')” |
6.2 SQL Drivers | {ts yyyy-mm-dd hh:mm:ss} |
6.2 Oracle | “TO_DATE('dd-mm-yyy hh:mm:ss', 'DD-MM-YYYY HH:MI:SS')” |
clarion_functions_used_in_sql_filter_statements.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1