cancel
Showing results for 
Search instead for 
Did you mean: 

TO_INTEGER not returning a number

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

Thanks Lars,

I'm able to get the result only with a SP by using a table variable for the inner query. but not in a SQL Console.

lt_presesult = 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';

SELECT * FROM :lt_presesult WHERE "ThisShouldBeANumber" < 1950;

Best regards

former_member182114
Active Contributor
0 Kudos

Run it with:

WITH lt_presesult  as (

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'

)

SELECT * FROM lt_presesult WHERE "ThisShouldBeANumber" < 1950;

Regards, Fernando Da Rós

Answers (1)

Answers (1)

former_member182114
Active Contributor
0 Kudos

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