0

I have table called DART_STG1 in Netezza Database. The table has a varchar column. I am trying to use the below SQL to convert the varchar into a number, but it always throws an error.

Code

SELECT DISTINCT TO_NUMBER(M12,'99G99') 
FROM   DART_STG1 
WHERE  M12 IS NOT NULL;

Throws an error:

ERROR [HY000] ERROR: Bad numeric input format

What does this error mean?

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
Teja
  • 13,214
  • 36
  • 93
  • 155
  • 2
    What are the results of SELECT DISTINCT M12 FROM DART_STG1 WHERE M12 IS NOT NULL;? Going by the error message, my guess is one or more of the records have data that can't be converted, so maybe you have to do some data massaging to get it to convert properly. – Brian Aug 28 '13 at 14:46
  • What do you mean by data massaging? – Teja Aug 28 '13 at 14:56
  • I don't get any output when I execute that SQL query. – Teja Aug 28 '13 at 14:56
  • I have both NOT NULL and NULL values. – Teja Aug 28 '13 at 17:20

2 Answers2

1

The PDA (Netezza) Conversion functions page provide examples, and together with the Template patterns, you can choose the appropriate format.

However I'm getting the same error for my data set. I'm suspecting it's something about the formatting of the values in STORE_NUMBER is what Netezza doesn't like.

orbanbalage
  • 187
  • 1
  • 9
0

The error:

ERROR [HY000] ERROR: Bad numeric input format

Is caused because you feeding letters into TO_NUMBER. You are feeding it 99G99 which is not a number.

The program tries to tell you it's not a number by telling you that the numeric input format is bad, as the error very clearly says.

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
  • 1
    How to use the TO_NUMBER function for converting string to integer? – Teja Aug 28 '13 at 17:29
  • 1
    to_number(STORE_NUMBER, '999') - even this returns an error. The actual question here is "What should be the proper input format?" We all know what the error message means in English. – KalC Sep 10 '13 at 16:02