Convert char to integer in INSERT using IIF and similar

Question

I use the insert statement to convert the BDE table (source) to the Firebird (destination) table using IB Datapump. Thus, the INSERT statement is passed through the values ​​of the source tables through the parameters. One parameter of the source field is alphanum (SOURCECHAR10 char(10) , it contains mostly integers and needs to be converted to an integer in the destination column (integer type) NEWINTFLD . If SOURCECHAR10 not numeric, I want to assign 0 to NEWINTFLD .

I use IIF and SIMILAR to check if a string is numeric, and assign 0 if not numeric, as follows:

 INSERT INTO "DEST_TABLE" (......, "NEWINTFLD",.....) VALUES(..., IIF( :"SOURCECHAR10" SIMILAR TO '[[:DIGIT:]]*', :"SOURCECHAR10", 0),..) 

However, for each numeric string, I still get conversion errors (DSQL error code = -303) .

I tested only with constants in the IIF result fields, for example SOURCECHAR10" SIMILAR TO '[[:DIGIT:]]*', 1, 0) , and it works like this: SOURCECHAR10 in the IIF true result field generates an error. Any ideas, how to get around this?

Answer 1

When your request is completed, the analyzer will notice that the second use :"SOURCECHAR10" used in the place where an integer is expected. Therefore, it will always convert the contents: SOURCECHAR10 to an integer for this position, even if it is not used, if the string is not integer.

Actually, Firebird does not use :"SOURCECHAR10" as parameters, but does your connection library convert it into two separate fill parameters ? , and the type of the second placeholder will be INTEGER. Thus, the conversion occurs before the actual request is executed.

Perhaps this solution (I did not test it, could contain syntax errors) in order to use something like ( NOTE : see the second example for the correct solution):

 CASE WHEN :"SOURCECHAR10" SIMILAR TO '[[:DIGIT:]]*' THEN CAST(:"SOURCECHAR10" AS INTEGER) ELSE 0 END 

This does not work, since it is interpreted as a listing of the parameter itself, see the CAST () element "Field input fields"

If this does not work, you can also try adding an explicit cast to VARCHAR around :"SOURCECHAR10" to make sure the parameter is correctly identified as VARCHAR:

 CASE WHEN :"SOURCECHAR10" SIMILAR TO '[[:DIGIT:]]*' THEN CAST(CAST(:"SOURCECHAR10" AS VARCHAR(10) AS INTEGER) ELSE 0 END 

Here, the internal cast is applied to the parameter itself, the external cast is applied when the CASE expression evaluates to true