Re: creating view - conditional testing in construct - Mailing list pgsql-novice

On Saturday 24 October 2009 09:28:44 richard terry wrote:

I've just implemented nathanial's solution and it solves my problem - thanks -
but I'd still be interested in sampel of using COALESCE.

Regards

richard

> 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
>