Re: How to deal with NULL values on dynamic queries?

Hi

Null + 1=null.
and
null + 'a' = null.

I expect that pVAL_COMENT is null (as you say).

===========================
create test environment.
===========================
create table nulltest (col1 varchar(10),col2 varchar(10));
insert into nulltest (col1,col2) values ('A','B');
insert into nulltest (col1) values ('A');
insert into nulltest (col2) values ('B');

select col1,col2 from nulltest;
select col1||col2 from nulltest;

postgres=# select col1,col2 from nulltest;
col1 | col2
------+------
A | B
A |
| B
(3 rows)

===========================
null make col1||col2 null
===========================
ex.
postgres=# select col1||col2 from nulltest;
?column?
----------
AB

(3 rows)

=============================================
even if we use quote_literal,we can not avoid tihs behavior.
=============================================
ex.
postgres=# select quote_literal(col1)||quote_literal(col2) from nulltest;
?column?
----------
'A''B'

(3 rows)

================================================
So we can use COALESCE() function to avoid this.
================================================
ex.
postgres=# select COALESCE(col1,'')||COALESCE(col2,'') from nulltest;
?column?
----------
AB
A
B
(3 rows)

ex2.
postgres=# select
quote_literal(COALESCE(col1,''))||quote_literal(COALESCE(col2,'')) from
nulltest;
?column?
----------
'A''B'
'A'''
'''B'
(3 rows)

Can you work around like this?
||quote_literal(COALESCE(pVAL_COMENT,''))

Thank you.

> Hi,
>
> I have a query that some values could be NULL, how can I deal with
> this problem?
>
> [code]
> EXECUTE 'INSERT INTO '
> || quote_ident(pNOME_VIEW)
> || '('
> || quote_ident(pCHAVE_1)
> || ', DAT_INICIO, DAT_FIM, COMENT) values ('
> || quote_literal(pVAL_CHAVE_1)
> || ', '
> || quote_literal(pVAL_CHAVE_2)
> || ', '
> || quote_literal(pVAL_CAMPO1)
> || ', '
> || quote_literal(pVAL_COMENT)
> || ')';
> [/code]
>
> The variable pVAL_COMENT could be NULL or have a value. How can I deal
> with this?
>
> Sorry the bad english.
>
> Best Regards,
>
>

--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================