Thread: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

From

Ennio-Sr

Date:

17 October 2008, 17:56:23

Hi all,

after upgrading from pg_7.4 to pg_8.3  when I run this command:

psql finanza -c "SELECT titolo, var, pr_chius, pr_aper, \
diff_aper_ch*(-1) as \"diff. ass.\", \
to_number(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \
from ult_qq having pr_chius>pr_aper and pr_aper>0 \
order by titolo;"   | tee -a /home/ennio/.tmp/aaa

I get:

ERROR: function to_number(double precision, unknown) does not exist ...

Of course it worked very well under 7.4 ...

Can somebody suggest how to correct this?

Regards,
    Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.    \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]    (°|°)
 Ricevo solo messaggi Content-Type: plain/text (no html o multipart).   )=(
 !!! --> e-mail a mio nome via OE (M$) sono false  e infette <-- !!!

Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

From

Tom Lane

Date:

17 October 2008, 18:43:49

Ennio-Sr <nasr.laili@tin.it> writes:
> after upgrading from pg_7.4 to pg_8.3  when I run this command:

> psql finanza -c "SELECT titolo, var, pr_chius, pr_aper, \
> diff_aper_ch*(-1) as \"diff. ass.\", \
> to_number(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \

to_number works on text arguments.  I suppose you could make this work
like 7.4 by explicitly casting the expression to text, but the whole
thing seems like the wrong approach altogether.  What are you trying to
accomplish --- round the result to six decimal places?  If so, it'd be
better to just cast it to numeric(20,6) or some such.

            regards, tom lane

Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

From

"Morris, Roy"

Date:

17 October 2008, 18:46:49

I could be wrong (likely) but I just changed the format template to use '9999D0000' and it works fine.


-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Ennio-Sr
Sent: Friday, October 17, 2008 1:56 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How to transform pg_7.4 function 'to_number' to 8.4
equivalent?


Hi all,

after upgrading from pg_7.4 to pg_8.3  when I run this command:

psql finanza -c "SELECT titolo, var, pr_chius, pr_aper, \
diff_aper_ch*(-1) as \"diff. ass.\", \
to_number(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \
from ult_qq having pr_chius>pr_aper and pr_aper>0 \
order by titolo;"   | tee -a /home/ennio/.tmp/aaa

I get:

ERROR: function to_number(double precision, unknown) does not exist ...

Of course it worked very well under 7.4 ...

Can somebody suggest how to correct this?

Regards,
    Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.    \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]    (°|°)
 Ricevo solo messaggi Content-Type: plain/text (no html o multipart).   )=(
 !!! --> e-mail a mio nome via OE (M$) sono false  e infette <-- !!!

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

From

Ennio-Sr

Date:

19 October 2008, 10:47:46

* Tom Lane <tgl@sss.pgh.pa.us> [171008, 14:43]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > after upgrading from pg_7.4 to pg_8.3  when I run this command:
>
> > psql finanza -c "SELECT titolo, var, pr_chius, pr_aper, \
> > diff_aper_ch*(-1) as \"diff. ass.\", \
> > to_number(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \
>
> to_number works on text arguments.  I suppose you could make this work
> like 7.4 by explicitly casting the expression to text, but the whole
> thing seems like the wrong approach altogether.  What are you trying to
> accomplish --- round the result to six decimal places?  If so, it'd be
> better to just cast it to numeric(20,6) or some such.
>
[Sorry! I sent this to you privately yesterday]
-----
Hmm...I did that at least 3 years ago and do not have any comments in my
script as to why I wrote it that way. The aim was to print the result of
that division with a few decimal points: probably the 'to_number' was a
requirement of vers. 7.4 in order to display the result according to the
template.
Now I've made a few 'blind' attempts to change it but got different type
of errors! It will take me many efforts to resume my fundamental
knowledge of docs ;-) ... unless you could point me to some particular
chapter ...
Thanks for your time.
Regards,   Ennio
-----
In the meantime I managed to come out of the empasse: writing the
original query in the following way seems to work; however, I'm
sure that newer also means simpler, so there must be a simpler way to
write this query (i.e. a viable substitution for the old 'to_number')!

 SELECT \
    titolo, var, pr_chius, pr_aper, \
    diff_aper_ch*(-1) as \"diff. ass.\", \
    to_char(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000')  as \"sc. %\" \
 FROM \
    ult_qq \
 GROUP BY \
    titolo, var, pr_chius, pr_aper, diff_aper_ch*(-1)
 HAVING \
    pr_chius>pr_aper and pr_aper>0 \
 ORDER BY \
    by titolo;

Regards,
    Ennio
---------------
PS: Considering that my 'whole architecture' was working so smoothly,
would it be possible to go back to version 7.4?

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.    \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]    (°|°)
 Ricevo solo messaggi Content-Type: plain/text (no html o multipart).   )=(
 !!! --> e-mail a mio nome via OE (M$) sono false  e infette <-- !!!