Re: creating view - conditional testing in construct

Alternatively you can use COALESCE in your concatenation, this way even 
NULL/empty fields are catered and will be part of summary.

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

-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice





-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice