Skip site navigation (1) Skip section navigation (2)

number to string conversion

From: "Freddy Villalba Arias" <fvillalba(at)madrid(dot)bilbomatica(dot)es>
To: "PostgreSQL JDBC Mailing List" <pgsql-jdbc(at)postgresql(dot)org>
Subject: number to string conversion
Date: 2004-04-30 10:38:15
Message-ID: 92EFB0BEDD24E9419E2CD9A2BD35DAEA0313EC@bmsrv001.madrid.bilbomatica.es (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi everybody,

 

I wanted to do something like the following:

 

SELECT 

    p.*, (SELECT c.* FROM CCAA c WHERE c.ccaa_id_ccaa = p.prov_id_ccaa) 

FROM 

    PROVINCIA p 

WHERE 

     (prov_id_ccaa = '@@@id_ccaa@@@' OR '' = '@@@id_ccaa@@@') AND 

     (upper(prov_ds_provincia) LIKE upper('%@@@descripcion@@@%') OR
'@@@descripcion@@@' = '')

 

...where anything between "@@@" is a token that is replaced by some
value at runtime.

 

There is a particular case: null values. In those cases, you'd get a
query like this:

 

SELECT 

    p.*, (SELECT c.* FROM CCAA c WHERE c.ccaa_id_ccaa = p.prov_id_ccaa) 

FROM 

    PROVINCIA p 

WHERE 

     (prov_id_ccaa = '' OR '' = '') AND 

     (upper(prov_ds_provincia) LIKE upper('xxx') OR 'xxx' = '')

 

Being prov_id_ccaa a numeric column, in ORACLE and Access, this wouldn't
pose a problem, but it PostgreSQL it does (it throws the error: ERROR:
invalid input syntax for type numeric: ")

 

The only solution I can think of is converting prov_id_ccaa to a string
value (something like to_char in ORACLE, for instance)

 

How can I do that (in PostgreSQL)?

 

Can anybody else think of a better solution?

 

Regards,

Freddy.

Responses

pgsql-jdbc by date

Next:From: Freddy Villalba AriasDate: 2004-04-30 10:59:19
Subject: Re: number to string conversion
Previous:From: Dave CramerDate: 2004-04-28 16:37:56
Subject: Re: v3 from the ground up

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group