Thread: creating view - conditional testing in construct

creating view - conditional testing in construct

From

richard terry

Date:

22 October 2009, 23:30:56

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

Re: creating view - conditional testing in construct

From

Nathaniel Trellice

Date:

23 October 2009, 11:43:23

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
forclarity 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
acomma and/or a space between the fields--you can add the field delimiter only if the next field is non-NULL/empty and
onlyif 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

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

Re: creating view - conditional testing in construct

From

"Mohlomi Moloi"

Date:

23 October 2009, 12:18:46

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

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

Re: creating view - conditional testing in construct

From

richard terry

Date:

23 October 2009, 22:31:48

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
>

Re: creating view - conditional testing in construct

From

richard terry

Date:

24 October 2009, 09:45:38

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
>

Re: creating view - conditional testing in construct

From

Casey Allen Shobe

Date:

25 October 2009, 16:32:15

On Sat, Oct 24, 2009 at 5:41 AM, richard terry <rterry@pacific.net.au> wrote:

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.

select coalesce (v.title, '') || ' ' ||
   coalesce (v.firstname, '') || ' ' ||
   coalesce (v.surname, '') || '( ' ||
coalesce (v.occupation, '') || ') ' ||
   coalesce (v.organisation, '') || ' ' ||
   coalesce (v.branch, '') as summary

from vworganisationsemployees as v;

But probably you want to use case, because with this approach you will still get an extra space padding if something is null. With case, you can handle that more elegantly, as already described.

Cheers,

Casey Allen Shobe

casey@shobe.info