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.
> -----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
> CREATE vwMyView VIEW AS
> [a bunch more columns]
> -- start of summary
> || ' ' || 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)
> 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.
> ----- 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
> ** 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
> the data, however in the vwOrganisationsEmployees, some rows will not
> the address of the branch ie fk_address is null, and the street and
> fields are null.
> So at this point in the query it works ok, but I want also to add the
> 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
> then keep adding the street, suburb, postcode to the field which ends up
> called summary.
> Any help appreciated.
In response to
pgsql-novice by date
|Next:||From: Mary Anderson||Date: 2009-10-23 23:59:28|
|Subject: How do you get the year from a postgresql DATE?|
|Previous:||From: Mohlomi Moloi||Date: 2009-10-23 11:53:33|
|Subject: Re: creating view - conditional testing in construct|