pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcase statement to cath nulls on joined tables
4+ messages / 3 participants
[nested] [flat]
* case statement to cath nulls on joined tables
@ 2012-02-03 12:45 John Morgan <jdmorgan@unca.edu>
2012-02-03 15:05 ` Re: case statement to cath nulls on joined tables Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: John Morgan @ 2012-02-03 12:45 UTC (permalink / raw)
To: pgsql-admin
Hello,
I am doing a left outer join between a polygon table and a data table.
Everything
returns fine from this join as expected. However, there are cases where
there isn?t data for a given polygon and this return as the expected null
in the result set. I would like to utilize something like a case statement
to catch the nulls and return a ?more friendly? message. I have tried
unsuccessfully to use the following logic:
*Select case value_of_interest null then ?more friendly message? else
value_of_interest end*
*From poly_table left outer join data_table one poly_table.common_id =
data_table.common_id*
If someone could let me know where I am going wrong it would be greatly
appreciated.
Cheers,
Derek
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: case statement to cath nulls on joined tables
2012-02-03 12:45 case statement to cath nulls on joined tables John Morgan <jdmorgan@unca.edu>
@ 2012-02-03 15:05 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2012-02-03 15:09 ` Re: case statement to cath nulls on joined tables Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Matheus de Oliveira @ 2012-02-03 15:05 UTC (permalink / raw)
To: John Morgan <jdmorgan@unca.edu>; +Cc: pgsql-admin
Try that:
SELECT address, CASE WHEN value_of_interest IS NULL THEN 'more friendly
message' ELSE value_of_interest END value_of_interest
From poly_table left outer join data_table one poly_table.common_id =
data_table.common_id
--
Matheus de Oliveira
On Fri, Feb 3, 2012 at 10:45 AM, John Morgan <jdmorgan@unca.edu> wrote:
> Hello,
>
> I am doing a left outer join between a polygon table and a data table.
> Everything
> returns fine from this join as expected. However, there are cases where
> there isn?t data for a given polygon and this return as the expected null
> in the result set. I would like to utilize something like a case statement
> to catch the nulls and return a ?more friendly? message. I have tried
> unsuccessfully to use the following logic:
>
> *Select case value_of_interest null then ?more friendly message? else
> value_of_interest end*
>
> *From poly_table left outer join data_table one poly_table.common_id =
> data_table.common_id*
>
> If someone could let me know where I am going wrong it would be greatly
> appreciated.
>
> Cheers,
>
> Derek
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: case statement to cath nulls on joined tables
2012-02-03 12:45 case statement to cath nulls on joined tables John Morgan <jdmorgan@unca.edu>
2012-02-03 15:05 ` Re: case statement to cath nulls on joined tables Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2012-02-03 15:09 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2012-02-03 18:34 ` Re: case statement to cath nulls on joined tables jdmorgan <jdmorgan@unca.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Matheus de Oliveira @ 2012-02-03 15:09 UTC (permalink / raw)
To: John Morgan <jdmorgan@unca.edu>; +Cc: pgsql-admin
Even better:
SELECT COALESCE(value_of_interest, 'more friendly message')
value_of_interest
From poly_table left outer join data_table one poly_table.common_id =
data_table.common_id
--
Matheus de Oliveira
On Fri, Feb 3, 2012 at 1:05 PM, Matheus de Oliveira <
matioli.matheus@gmail.com> wrote:
> Try that:
>
> SELECT address, CASE WHEN value_of_interest IS NULL THEN 'more friendly
> message' ELSE value_of_interest END value_of_interest
>
> From poly_table left outer join data_table one poly_table.common_id =
> data_table.common_id
>
>
> --
> Matheus de Oliveira
>
>
>
>
>
> On Fri, Feb 3, 2012 at 10:45 AM, John Morgan <jdmorgan@unca.edu> wrote:
>
>> Hello,
>>
>> I am doing a left outer join between a polygon table and a data table.
>> Everything
>> returns fine from this join as expected. However, there are cases where
>> there isn?t data for a given polygon and this return as the expected null
>> in the result set. I would like to utilize something like a case
>> statement
>> to catch the nulls and return a ?more friendly? message. I have tried
>> unsuccessfully to use the following logic:
>>
>> *Select case value_of_interest null then ?more friendly message? else
>> value_of_interest end*
>>
>> *From poly_table left outer join data_table one poly_table.common_id =
>> data_table.common_id*
>>
>> If someone could let me know where I am going wrong it would be greatly
>> appreciated.
>>
>> Cheers,
>>
>> Derek
>>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: case statement to cath nulls on joined tables
2012-02-03 12:45 case statement to cath nulls on joined tables John Morgan <jdmorgan@unca.edu>
2012-02-03 15:05 ` Re: case statement to cath nulls on joined tables Matheus de Oliveira <matioli.matheus@gmail.com>
2012-02-03 15:09 ` Re: case statement to cath nulls on joined tables Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2012-02-03 18:34 ` jdmorgan <jdmorgan@unca.edu>
0 siblings, 0 replies; 4+ messages in thread
From: jdmorgan @ 2012-02-03 18:34 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: pgsql-admin
Thanks Matheus. The coalesce method worked like a champ.
Cheers,
Derek
On 2/3/2012 10:09 AM, Matheus de Oliveira wrote:
> Even better:
>
> SELECT COALESCE(value_of_interest, 'more friendly message')
> value_of_interest
> From poly_table left outer join data_table one poly_table.common_id =
> data_table.common_id
>
> --
> Matheus de Oliveira
>
>
>
>
> On Fri, Feb 3, 2012 at 1:05 PM, Matheus de Oliveira
> <matioli.matheus@gmail.com <mailto:matioli.matheus@gmail.com>> wrote:
>
> Try that:
>
> SELECT address, CASE WHEN value_of_interest IS NULL THEN 'more
> friendly message' ELSE value_of_interest END value_of_interest
>
> From poly_table left outer join data_table one
> poly_table.common_id = data_table.common_id
>
>
> --
> Matheus de Oliveira
>
>
>
>
>
> On Fri, Feb 3, 2012 at 10:45 AM, John Morgan <jdmorgan@unca.edu
> <mailto:jdmorgan@unca.edu>> wrote:
>
> Hello,
>
> I am doing a left outer join between a polygon table and a
> data table.
> Everything
> returns fine from this join as expected. However, there are
> cases where
> there isn?t data for a given polygon and this return as the
> expected null
> in the result set. I would like to utilize something like a
> case statement
> to catch the nulls and return a ?more friendly? message. I
> have tried
> unsuccessfully to use the following logic:
>
> *Select case value_of_interest null then ?more friendly
> message? else
> value_of_interest end*
>
> *From poly_table left outer join data_table one
> poly_table.common_id =
> data_table.common_id*
>
> If someone could let me know where I am going wrong it would
> be greatly
> appreciated.
>
> Cheers,
>
> Derek
>
>
>
--
Derek @ NEMAC
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-03 18:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-03 12:45 case statement to cath nulls on joined tables John Morgan <jdmorgan@unca.edu>
2012-02-03 15:05 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2012-02-03 15:09 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2012-02-03 18:34 ` jdmorgan <jdmorgan@unca.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox