Skip site navigation (1) Skip section navigation (2)

Re: creating view - conditional testing in construct

From: richard terry <rterry(at)pacific(dot)net(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: creating view - conditional testing in construct
Date: 2009-10-23 22:28:44
Message-ID: 200910240928.44802.rterry@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-novice
On Friday 23 October 2009 22:53:33 Mohlomi Moloi wrote:
> Alternatively you can use COALESCE in your concatenation, this way even
> NULL/empty fields are catered and will be part of summary.

Can you give me a sample?

Thanks for replying  everyone.

regards 

richard

> 
> Regards,
> 
> Hlomiza
> 
> -----Original Message-----
> From: Nathaniel Trellice [mailto:naptrel(at)yahoo(dot)co(dot)uk]
> Sent: 23 October 2009 13:43
> To: rterry(at)pacific(dot)net(dot)au; pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] creating view - conditional testing in construct
> 
> Hi Richard,
> 
> The simplest way that I can think of to do it would be to use a
> conditional expression in the creation of your view. The 'CASE'
> expression would fit the bill (see section 9.16.1 of the 8.4.1 manual).
> Slightly altering your conventions for clarity you might do it like
> this:
> 
> CREATE vwMyView VIEW AS
>   vworganisationsemployees.fk_organisation,
>   vworganisationsemployees.fk_branch,
>   [a bunch more columns]
> -- start of summary
>   (vworganisationsemployees.title
> 
>    || ' ' || vworganisationsemployees.firstname
>    || ' ' || vworganisationsemployees.surname
> 
>    [a bunch more concatenated summary fields]
> 
>    || (CASE WHEN vworganisationsemployees.fk_address IS NULL
> 
>        THEN ''
>        ELSE (vworganisationsemployees.fk_street
> 
>                   || ' ' || vworganisationsemployees.fk_suburb)
> 
>        END)
>   )
>   AS summary
> -- end of summary
> 
> 
> There are more elegant ways using stored procedures to do this kind of
> thing (especially the string concatenation with a comma and/or a space
> between the fields--you can add the field delimiter only if the next
> field is non-NULL/empty and only if you've already had a non-NULLempty
> entry), but this should get you going.
> 
> Nathaniel
> 
> 
> ----- Original Message ----
> From: richard terry <rterry(at)pacific(dot)net(dot)au>
> To: pgsql-novice(at)postgresql(dot)org
> Sent: Fri, 23 October, 2009 0:21:35
> Subject: [NOVICE] creating view - conditional testing in construct
> 
> Hi all,
> 
> I'm struggling to find the syntax to create a view in this situation.
> 
> I'm joining a table to an existing view
> 
> 
> create vwMyView as
> 
>        vworganisationsemployees.fk_organisation,
>         vworganisationsemployees.fk_branch,
>         vworganisationsemployees.fk_employee,
>         vworganisationsemployees.fk_person,
>          vworganisationsemployees.fk_address,
> ** I want all these fields to end up as as field called, say summary
>     (vworganisationsemployees.title ||' '::text) ||
>         (vworganisationsemployees.firstname ||' '::text)  ||
>         (vworganisationsemployees.surname ||'( '::text)  ||
>         (vworganisationsemployees.occupation ||') '::text) ||
>         (vworganisationsemployees.organisation ||' '::text) ||
>         (vworganisationsemployees.branch ||' '::text)  as summary
> 
> so far so good, as all the employees of the organisations will always
> contain
> the data, however in the vwOrganisationsEmployees, some rows will not
> contain
> the address of the branch  ie fk_address is null, and the street and
> suburb
> fields are null.
> 
> So at this point in the query it works ok, but I want also to add the
> address
> of the branch into the summary field, and in some records there is no
> fk_address and hence no street or suburb.
> 
> so I want to be able to conditionally test if fk_address is null, if it
> is,
> then keep adding the street, suburb, postcode to the field which ends up
> being
> called summary.
> 
> Any help appreciated.
> 
> Regards
> 
> Richard
> 

In response to

Responses

pgsql-novice by date

Next:From: Mary AndersonDate: 2009-10-23 23:59:28
Subject: How do you get the year from a postgresql DATE?
Previous:From: Mohlomi MoloiDate: 2009-10-23 11:53:33
Subject: Re: creating view - conditional testing in construct

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group