covert oracle decfloat format

ultra...@gmail.com

unread,

Mar 13, 2013, 9:56:23 AM3/13/13

to

I am trying to convert some oracle sql to db2.

The current issue is:

to_char(trunc(root_ah_runtime/3600), 'FM999999990') || ':' || to_char(trunc(mod(root_ah_runtime,3600)/60), 'FM00') || ':' || to_char(mod(root_ah_runtime,60), 'FM00')

returns

QL20476N The "DECFLOAT_FORMAT" function was invoked with an invalid format
string "FM999999990". SQLSTATE=22018

and I can't find any documentation on the conversion.

Has anyone seen this before and have a solution?

Thank you!

Peter H. Coffin

unread,

Mar 13, 2013, 4:31:03 PM3/13/13

to

On Wed, 13 Mar 2013 06:56:23 -0700 (PDT), ultra...@gmail.com wrote:
> I am trying to convert some oracle sql to db2.
>
> The current issue is:
>
> to_char(trunc(root_ah_runtime/3600), 'FM999999990') || ':' || to_char(trunc(mod(root_ah_runtime,3600)/60), 'FM00') || ':' || to_char(mod(root_ah_runtime,60), 'FM00')
>
> returns
>
> QL20476N The "DECFLOAT_FORMAT" function was invoked with an invalid format
> string "FM999999990". SQLSTATE=22018
>
>
> and I can't find any documentation on the conversion.

to_char is synonym for varchar_format.

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html

I note no format string that starts with "FM". There is an FF, which
might be close to what you want.

Me, I'm a great believer in actual time & date types.

--
On Usenet, webforums and blogs (oh my!) the rational explanation spawns
its own infinite number of irrational explanations.
-- Andrews' Modification of Staples' Observation

TheBoss

unread,

Mar 13, 2013, 8:08:08 PM3/13/13

to

ultra...@gmail.com wrote in
news:8ab1b80c-2c88-414f...@googlegroups.com:

> I am trying to convert some oracle sql to db2.
>
> The current issue is:
>
> to_char(trunc(root_ah_runtime/3600), 'FM999999990') || ':' ||
> to_char(trunc(mod(root_ah_runtime,3600)/60), 'FM00') || ':' ||
> to_char(mod(root_ah_runtime,60), 'FM00')
>
> returns
>
> QL20476N The "DECFLOAT_FORMAT" function was invoked with an invalid
> format string "FM999999990". SQLSTATE=22018

You missed the first character "S" from the error code SQL20476N.
It can be found here:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r8/topic/com.ibm.db2.luw.mess
ages.sql.doc/doc/msql20476n.html

A bit strange that in your case it says the DECFLOAT_FORMAT function is
invoked, as - like Peter Coffin already mentioned - the to_char is
synonym with the VARCHAR_FORMAT, not DECFLOAT_FORMAT.

>
>
> and I can't find any documentation on the conversion.
>
> Has anyone seen this before and have a solution?
>
> Thank you!
>

"FM" is a valid datetime format element in Oracle as can be seen in
following Oracle doc (see table 2-15):

docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

As described, it specifies that a value should be returned with no
leading or trailing blanks.

The relevant doc for DB2 describing datetime format elements for the
VARCHAR_FORMAT scalar function doesn't specify the "FM":

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%
2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0053653.html

So it seems this is something that causes DB2's Oracle compatibility to
be a little less than 100% ;-)

You didn't specify the exact DB2 level, but you might also be
interested in this APAR that seems loosely related:

http://www-01.ibm.com/support/docview.wss?uid=swg1IC73585

HTH.

Cheers!

--
Jeroen

TheBoss

unread,

Mar 13, 2013, 8:13:24 PM3/13/13

to

TheBoss <The...@invalid.nl> wrote in
news:XnsA183B8E224...@213.75.12.10:

By the way, I guess this might be something Serge Rielau would be
interested in, so may be Helmut can forward this to him?...

--
Jeroen

Helmut Tessarek

unread,

Mar 13, 2013, 9:18:16 PM3/13/13

to

On 13.03.13 20:13 , TheBoss wrote:
> By the way, I guess this might be something Serge Rielau would be
> interested in, so may be Helmut can forward this to him?...

Serge is no longer in SQL compiler development.

Until now I haven't had a reason to figure out who his successor is. :-)
I'll ask him tomorrow.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

TheBoss

unread,

Mar 14, 2013, 8:41:44 PM3/14/13

to

Helmut Tessarek <tess...@evermeet.cx> wrote in news:khr8gp$30f$1
@news.albasani.net:

> On 13.03.13 20:13 , TheBoss wrote:
>> By the way, I guess this might be something Serge Rielau would be
>> interested in, so may be Helmut can forward this to him?...
>
> Serge is no longer in SQL compiler development.

I know, but I bet he didn't loose interest.
According to his LinkedIn profile he is the Lead Architect for Oracle
Compatibility and he still writes interesting articles on related subjects
on his blog and on DevWorks:
https://plus.google.com/109247092865859825789/posts
http://www.ibm.com/developerworks/data/library/techarticle/dm-
0907oracleappsondb2/

And on Facebook is stated that one of his main goals in life is to save the
world from Oracle, so there you go! ;-)

>
> Until now I haven't had a reason to figure out who his successor is. :-)
> I'll ask him tomorrow.
>

Thanks Helmut, I knew we could count on you :-)

--
Jeroen

Helmut Tessarek

unread,

Mar 14, 2013, 9:43:43 PM3/14/13

to

On 14.03.13 20:41 , TheBoss wrote:
> I know, but I bet he didn't loose interest.
> According to his LinkedIn profile he is the Lead Architect for Oracle
> Compatibility and he still writes interesting articles on related subjects
> on his blog and on DevWorks:
> https://plus.google.com/109247092865859825789/posts
> http://www.ibm.com/developerworks/data/library/techarticle/dm-
> 0907oracleappsondb2/

Ok, let me rephrase that. He is no longer with DB2.

> And on Facebook is stated that one of his main goals in life is to save the
> world from Oracle, so there you go! ;-)

I'm not on Facebook, but I definitely agree with his goal! :-)