Good evening tools people.

I'm trying to query a table on a string value but I want to treat it as numerical so I can select the correct range of records. At the moment it would select records where the value was greater than 960 for example, it's also selecting records where the value is 98.

Is there a way to process that value as numeric in my query? I've searched these forums and found something I thought might help which led me to the below but the compiler doesn't like it generating a type mismatch error.



long v_wfid

select ttocm999.toid, CAST(ttocm999.wfid AS INTEGER) :v_wfid
from ttocm999 for update
where ttocm999.comp between :comp.from and :comp.to and
ttocm999.codt between :codt.from and :codt.to and
:v_wfid between :wfid.from and :wfid.to
selectdo
dal.destroy.object("ttocm999")
commit.transaction()
endselect


this one compiles but then I get a runtime error


select ttocm999.toid
from ttocm999 for update
where ttocm999.comp between :comp.from and :comp.to and
ttocm999.codt between :codt.from and :codt.to and
CAST(ttocm999.wfid AS INTEGER) between :wfid.from and :wfid.to
selectdo
dal.destroy.object("ttocm999")
commit.transaction()
endselect


Any help you can give would be much appreciated.

I'm trying to query a table on a string value but I want to treat it as numerical so I can select the correct range of records.
At the moment it would select records where the value was greater than 960 for example, it's also selecting records where the value is 98.

Is there a way to process that value as numeric in my query? I've searched the forums and found something I thought might help which led me to the below but the compiler doesn't like it generating a type mismatch error.


CAST expression
With the CAST expression you can assign a type to a parameter.

Syntax
<cast expression>
::= CAST ( Parameter AS <parameter cast type> )

<parameter cast type>
::= INTEGER | REAL | DATE | TIMESTAMP | STRING | RAW
Syntactical restrictions
The value of Parameter is restricted to the name of a column. A syntax error will result when any other type of expression is used.

Semantics
The cast operator assigns the type <parameter cast type> to the <parameter>. The cast expression itself will also be of type <parameter cast type>.

Examples
The following CAST expression assigns the type integer to the parameter param.

CAST ( :param AS INTEGER )
The following CAST expression assigns the type raw to the parameter param.

CAST ( :param AS RAW )
Resolving type conflicts on parameters
The cast operator is used to properly type parameters in case of ambiguities or in case of possible type conflicts.

In the following example, both param1 and param2 cannot be typed, because each type is comparable to itself.

:param1 = :param2
This problem can be resolved using the CAST expression.

:param1 = CAST( :param2 AS STRING )
In the following example the first comparison types param as date, while the second comparison types it as real.

:param = hiredate or :param = 0
Using the CAST expression this can be resolved.

:param = hiredate or CAST( :param AS DATE ) = 0



this one compiles but then I get a runtime error

Regards