On 18/03/2009 8:29 AM, anjela patnaik wrote:
> Hello all,
> I have a table with multiple columns. I'd like to construct a select
> statement that will concat two column values if the second field is non-empty
> and also only return one row.
>
> I noticed the concat operator is not supported. I also had trouble with ||.
> For some reason it always returned {}.
>
> The fields request and request2 contain a TCL script. Now, request2 is not
> always populated. It's only populated if the request variable contents exceed
> 4k bytes.
>
> I'm using min because I only want to grab the first row. Note that doing
> rowid < 2 didn't work in the where clause.
>
> set y [db2 eval {SELECT min(request) from MYTBL where interface=$::n_int and
> operation=$::n_op and ocrelease=$::n_ver and products=$::n_prods} ]
>
> This doesn't work well.
>
> set y [db2 eval {SELECT request||request2 from MYTBL where interface=$::n_int
> and
> operation=$::n_op and ocrelease=$::n_ver and products=$::n_prods} ]
>
Problem 1 ("concat two column values if the second field is non-empty")
I assume non-empty means non-NULL -- the alternative (non-zero length)
should not be a problem.
The SQL that you need is something like
SELECT request || coalesce(request2, '') from MYTBL
You can use ifnull instead of coalesce if you like.
See http://www.sqlite.org/lang_corefunc.html#ifnull
Problem 2 ("and also only return one row")
min(request) returns the lowest value of request, not the value of
request from the row with the smallest rowid. Which do you really want?
Problem 3 ("I also had trouble with ||. For some reason it always
returned {}")
I know nothing of TCL. I suspect that "returned {}" means "returned NULL".
(Anything || NULL) produces NULL. Examples:
sqlite> select 'x' || NULL;
sqlite> select coalesce('x' || NULL, 'zzzzzz');
zzzzzz
Using coalesce in the Problem 1 solution above avoids this problem:
sqlite> select 'x' || coalesce(NULL, ''); -- simulate request2 is NULL
x
sqlite> select 'x' || coalesce('y', ''); -- simulate request2 = 'y'
xy
sqlite>
HTH,
John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
