Thread: Problems with to_number

Problems with to_number

From
Chrishelring
Date:
HI all,

below is the view i´ve tried to create on a table. The purpose was to do
some math on one of the columns (retning). The column is a double precision
number. The result is that the function is not recognized ("ERROR:  function
to_number(double precision, unknown) does not exist"). I´m a bit uncertain
on how the syntax is, so perhaps someone could point me in the right
direction?

thanks in advance,

Christian

CREATE OR REPLACE VIEW "husnr_view" ("KOMNR", "VEJKODE", "VEJNAVN",
"HUSNUMMER", "POSTNR", "POSTNAVN", "X", "Y", "RETNING", "TSTAND", "NKLASSE")
AS
  SELECT
    HUSNR.KOMNR KOMNR,
    HUSNR.VEJKODE VEJKODE,
    HUSNR.VEJNAVN VEJNAVN,
    HUSNUMMER,
    HUSNR.POSTNR POSTNR,
    HUSNR.POSTNAVN POSTNAVN,
    HUSNR.X X,
    HUSNR.Y Y,
    CASE
      WHEN TO_NUMBER(HUSNR.RETNING, '999g99')<20000
      THEN (20000-TO_NUMBER(HUSNR.RETNING, '999g99'))/111
      WHEN TO_NUMBER(HUSNR.RETNING, '999g99')>20000
      THEN (40000-(TO_NUMBER(HUSNR.RETNING, '999g99')-20000))/111
    END RETNING,
    HUSNR.TSTAND TSTAND,
    HUSNR.NKLASSE NKLASSE

  FROM rk_grundkort.husnr;

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problems-with-to-number-tp4477251p4477251.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Problems with to_number

From
Thomas Kellerer
Date:
Chrishelring wrote on 10.06.2011 22:45:
> HI all,
>
> below is the view i´ve tried to create on a table. The purpose was to do
> some math on one of the columns (retning). The column is a double precision
> number. The result is that the function is not recognized ("ERROR:  function
> to_number(double precision, unknown) does not exist"). I´m a bit uncertain
> on how the syntax is, so perhaps someone could point me in the right
> direction?

If the column is already a numeric data type there is no reason to use to_number() (which converts a character value to
anumber) 

CREATE OR REPLACE VIEW "husnr_view" ("KOMNR", "VEJKODE", "VEJNAVN",
"HUSNUMMER", "POSTNR", "POSTNAVN", "X", "Y", "RETNING", "TSTAND", "NKLASSE")
AS
   SELECT
     HUSNR.KOMNR KOMNR,
     HUSNR.VEJKODE VEJKODE,
     HUSNR.VEJNAVN VEJNAVN,
     HUSNUMMER,
     HUSNR.POSTNR POSTNR,
     HUSNR.POSTNAVN POSTNAVN,
     HUSNR.X X,
     HUSNR.Y Y,
     CASE
       WHEN HUSNR.RETNING < 20000 THEN (20000- HUSNR.RETNING)/111
       WHEN HUSNR.RETNING > 20000 THEN (40000- (HUSNR.RETNING -20000))/111
     END RETNING,
     HUSNR.TSTAND TSTAND,
     HUSNR.NKLASSE NKLASSE
   FROM rk_grundkort.husnr;