CODEWITHSUNDEEP
sqlnetezza
Hongfa Wu
Hongfa Wu

Reputation: 104

How to convert a COBOL string with implied decimal to decimal type in Netezza?

See I have a COBOL column like this

05  AMOUNT      PIC 999V99.

Here the V means decimal is implied. So value 123.45 will be represented as 12345

In order to convert it back to decimal(5,2) in Netezza, I tried the following

CAST('12345' AS DECIMAL(5,2))  --This will cause overflow, of course
CAST('12345' AS DECIMAL(5))/100 --Works, but looks awkward

Does any one know a better way to convert '12345' back to decimal(5,2) in Netezza?

Thanks

Upvotes: 2

Views: 805

Answers (2)

Hongfa Wu
Hongfa Wu

Reputation: 104

I use the method of converting it to pack decimal and then to numeric type. I noticed an extra character is needed when converting to PACKED DECIMAL. The extra character can be any character.

Prefix TOOLKIT.SQLEXT. is where our extension function installed.

SELECT  TOOLKIT.SQLEXT.PACKEDDECIMAL2STR(TOOLKIT.SQLEXT.NUM2PACKEDDEC('12345'||' ') ,2)::NUMERIC(5,2);

Result:

123.45

SELECT  TOOLKIT.SQLEXT.PACKEDDECIMAL2STR(TOOLKIT.SQLEXT.NUM2PACKEDDEC('-12345'||' ') ,2)::NUMERIC(5,2);

Result:

-123.45

Upvotes: 1

Niederee
Niederee

Reputation: 4295

Try this:

select cast(substr(a.field,1,3)||'.'||substr(a.field,4,2) AS DECIMAL(5,2))
from (select '12345' as field) a

Upvotes: 0

Related Questions