Обсуждение: 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.
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.
Freddy Villalba Arias wrote: > There is a particular case: null values. [...] Use PreparedStatement.setNull(). -O
Eh, sorry, on rereading this it's more than just "how do I set a null
parameter"..
Freddy Villalba Arias wrote:
> 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: ")
Well, that makes sense, '' isn't a valid numeric constant. You probably
want to use NULL for this "no value" case, not try to shoehorn it into a
varchar value (i.e. have "... OR ? IS NULL"). But that might have a
different meaning if your schema allows NULLs in those columns.
Alternatively, modify the query based on the actual @@@id_cca@@@ and
@@@descripcion@@@ values being used. You can remove one or both branches
of the AND condition based on their values, entirely on the application
side.
Either way, this is probably better asked on pgsql-sql.
-O