Function conversions in Filter Statements

Top  Previous  Next

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

 

 

See Also: MATCH, INSTRING, NULL