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