TO_INTEGER not returning a number

[#1]

Hello,

I select values that I know are numbers from a varchar column that also contains entries that are not number. I would like to keep only certain values in a where clause but I keep getting the following error:

"SAP DBTech JDBC: [339]: invalid number:  [6930] attribute value is not a number"

If I run the query without the where clause, all the values are converted to numbers.

SELECT * FROM ( SELECT ca."AttributeId", cas."AttributeSubsetId", TO_INTEGER(ca."AttributeValue") AS "ThisShouldBeANumber" FROM "SAP_IU_EA"."sap.iu.ea.core.configuration.data::Customizing.Attribute" ca LEFT JOIN "SAP_IU_EA"."sap.iu.ea.core.configuration.data::Customizing.AttributeSubset" cas ON ca."AttributeSubsetId" = cas."AttributeSubsetId" WHERE cas."Description" = 'Construction Year' ) WHERE "ThisShouldBeANumber" < 1950;

SELECT ca."AttributeId", cas."AttributeSubsetId", ca."AttributeValue" FROM "SAP_IU_EA"."sap.iu.ea.core.configuration.data::Customizing.Attribute" ca LEFT JOIN "SAP_IU_EA"."sap.iu.ea.core.configuration.data::Customizing.AttributeSubset" cas ON ca."AttributeSubsetId" = cas."AttributeSubsetId" WHERE cas."Description" = 'Construction Year' AND TO_INTEGER(ca."AttributeValue") = 1950;

both of those query fails with the same error. Is there limitations regarding Data Type Conversion Functions?

Best regards

[Former Member]

Hi Nick,

First try with same condition or EQUAL or LESS THAN 1950.

The problem can be found on bad data sometimes.

Regards, Fernando Da Rós