User Tools

Site Tools


clarion_functions_used_in_sql_filter_statements.htm
Navigation:  SQL Drivers >====== Function conversions in Filter Statements ====== Previous pageReturn to chapter overviewNext page

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.

NoteBox.jpg

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')”

See Also: MATCH, INSTRING, NULL

clarion_functions_used_in_sql_filter_statements.htm.txt · Last modified: 2021/04/15 15:57 by 127.0.0.1