Convert string to number in SQL query

ajm1811 · #1 27th January 2023, 00:46

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.

Code:

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

Code:

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.

bdittmar · #2 28th January 2023, 13:56

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.

Code:

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