Re: creating view - conditional testing in construct

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
>