Hi everyvody,
Sorry. Didn’t take me too long to find this:
SELECT
p.*, (SELECT c.ccaa_ds_ccaa FROM CCAA c WHERE c.ccaa_id_ccaa = p.prov_id_ccaa) as ds_ccaa
FROM
PROVINCIA p
WHERE
(to_char(prov_id_ccaa, 'FM9999999999999999') = '1' OR
'1' = '') AND
(upper(prov_ds_provincia) LIKE upper('%%') OR
'' = '')
… which will do it for my 16-digit integer.
HOWEVER, it’s quite awkward since you have to create a specific mask depending on the precision of the numeric field.
Is there some way (maybe a unique mask) to convert any integer number to string without having to know the precision?
Cheers,
Freddy.
-----Mensaje original-----
De: Freddy Villalba Arias
Enviado el: viernes, 30 de abril de 2004 12:38
Para: PostgreSQL JDBC Mailing List
Asunto: [JDBC] number to string conversion
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.