Re: creating view - conditional testing in construct - Mailing list 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@yahoo.co.uk]
> Sent: 23 October 2009 13:43
> To: rterry@pacific.net.au; pgsql-novice@postgresql.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@pacific.net.au>
> To: pgsql-novice@postgresql.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
>