Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id D1C90137A187 for ; Fri, 3 Feb 2012 14:34:56 -0400 (AST) Received: from na3sys009aog123.obsmtp.com ([74.125.149.149]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1RtNyJ-0003xf-KA for pgsql-admin@postgresql.org; Fri, 03 Feb 2012 18:34:56 +0000 Received: from mail-yx0-f178.google.com ([209.85.213.178]) (using TLSv1) by na3sys009aob123.postini.com ([74.125.148.12]) with SMTP ID DSNKTywowfFVEFiKsPnc3Zm0sitWURjVramg@postini.com; Fri, 03 Feb 2012 10:34:55 PST Received: by mail-yx0-f178.google.com with SMTP id m12so2023011yen.37 for ; Fri, 03 Feb 2012 10:34:41 -0800 (PST) Received: by 10.236.161.103 with SMTP id v67mr12940542yhk.87.1328294081544; Fri, 03 Feb 2012 10:34:41 -0800 (PST) Received: from [152.18.145.8] (dhcp-8.rbh232-1.fs.unca.edu. [152.18.145.8]) by mx.google.com with ESMTPS id n24sm10137799yhj.13.2012.02.03.10.34.40 (version=SSLv3 cipher=OTHER); Fri, 03 Feb 2012 10:34:41 -0800 (PST) Message-ID: <4F2C28CD.5010809@gmail.com> Date: Fri, 03 Feb 2012 13:34:53 -0500 From: jdmorgan User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1 MIME-Version: 1.0 To: Matheus de Oliveira CC: pgsql-admin@postgresql.org Subject: Re: case statement to cath nulls on joined tables References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------040208010205080300090006" X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201202/16 X-Sequence-Number: 39076 This is a multi-part message in MIME format. --------------040208010205080300090006 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > 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 > 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 --------------040208010205080300090006 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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> 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




-- 
Derek @ NEMAC
--------------040208010205080300090006--