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

creating view - conditional testing in construct

From: richard terry <rterry(at)pacific(dot)net(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: creating view - conditional testing in construct
Date: 2009-10-22 23:21:35
Message-ID: 200910231021.35766.rterry@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: vikas vashistaDate: 2009-10-23 06:27:57
Subject: Re: user defined data type
Previous:From: Nathaniel TrelliceDate: 2009-10-22 14:40:22
Subject: Asynchronous commands

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