Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

TO_INTEGER not returning a number

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

Tags:
replied

In order to apply the filter in your WHERE condition ALL the values in the respective columns will need to be converted to your integer value.

That includes those values that wouldn't show up after your join.

You can either change the integer you are comparing with to a string or you must ensure that there are actually only numbers in the column.

Pay special attention to formatted numbers in text fields  (e.g. 1,2345.32) as the TO_INTEGER function won't parse those correctly.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question