Thread: VIEW or Stored Proc - Is this even possible?

VIEW or Stored Proc - Is this even possible?

From
Stephen.Thompson@bmwfin.com
Date:
Hello All,

I am wondering if this is possible and how.

I have a table with fields A, B, C, D and E. Any of these can contain nulls.
What I need to do is to be able to perform a select that removes nulls. 

EG:

if A = 'A' B = NULL C = NULL D = 'D' E = 'E'

what I would like returned is 

A, D, E, NULL, NULL.


Can anyone suggest a way to do this? I will need to select from this and
hopefully add som simple conditions.

Would a VIEW be the way to go or will I have to use a stored procedure?


Regards,


Stephen.
x 5125




-------------------------------------------------------------------------------------------

Copyright material and/or confidential and/or privileged information may be contained in this e-mail and any attached
documents. The material and information is intended for the use of the intended addressee only.  If you are not the
intendedaddressee, or the person responsible for delivering it to the intended addressee, you may not copy, disclose,
distribute,disseminate or deliver it to anyone else or use it in any unauthorised manner or take or omit to take any
actionin reliance on it. To do so is prohibited and may be unlawful.   The views expressed in this e-mail may not be
officialpolicy but the personal views of the originator.  If you receive this e-mail in error, please advise the sender
immediatelyby using the reply facility in your e-mail software, or contact postmaster@bmwfin.com.  Please also delete
thise-mail and all documents attached immediately.  
 
Many thanks for your co-operation.

BMW Financial Services (GB) Limited is registered in England and Wales under company number 01288537.
Registered Offices : Europa House, Bartley Way, Hook, Hants, RG27 9UF
------------------------------------------------------------------------------------------


Re: VIEW or Stored Proc - Is this even possible?

From
Richard Huxton
Date:
On Wednesday 19 Feb 2003 12:56 pm, Stephen.Thompson@bmwfin.com wrote:
> I have a table with fields A, B, C, D and E. Any of these can contain
> nulls. What I need to do is to be able to perform a select that removes
> nulls.
>
> if A = 'A'
>   B = NULL
>   C = NULL
>   D = 'D'
>   E = 'E'
>
> what I would like returned is
>
> A, D, E, NULL, NULL.

You could do this in 7.3 with table functions (there's a good article on
techdocs.postgresql.org about them).

Don't suppose you could give more of an explanation - it sounds like an odd
thing to do.

--  Richard Huxton


Re: VIEW or Stored Proc - Is this even possible?

From
Stephen.Thompson@bmwfin.com
Date:
Hello,

Thanks for your suggestion I will look into this further.

The reason this issue exists is to do with address details. What we have is
an address table based upon the PAF address structure. IE house number,
house name, street, locality etc. The software that we are using to perform
a mail merge will not remove blank lines from the address so we can end up
with an address label looking as:

My House

Main Street

Any Town
County


My soultion requires the database to change the position of fields to remove
blank elements from the address list. I would then see on the address label:

My House
Main Street
Any Town
County
<null>
<null>

This would be far more preferable.

Regards,

Stephen.


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 19 February 2003 18:11
To: Stephen.Thompson@bmwfin.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] VIEW or Stored Proc - Is this even possible?


On Wednesday 19 Feb 2003 12:56 pm, Stephen.Thompson@bmwfin.com wrote:
> I have a table with fields A, B, C, D and E. Any of these can contain
> nulls. What I need to do is to be able to perform a select that removes
> nulls.
>
> if A = 'A'
>   B = NULL
>   C = NULL
>   D = 'D'
>   E = 'E'
>
> what I would like returned is
>
> A, D, E, NULL, NULL.

You could do this in 7.3 with table functions (there's a good article on 
techdocs.postgresql.org about them).

Don't suppose you could give more of an explanation - it sounds like an odd 
thing to do.

--  Richard Huxton


-------------------------------------------------------------------------------------------

Copyright material and/or confidential and/or privileged information may be contained in this e-mail and any attached
documents. The material and information is intended for the use of the intended addressee only.  If you are not the
intendedaddressee, or the person responsible for delivering it to the intended addressee, you may not copy, disclose,
distribute,disseminate or deliver it to anyone else or use it in any unauthorised manner or take or omit to take any
actionin reliance on it. To do so is prohibited and may be unlawful.   The views expressed in this e-mail may not be
officialpolicy but the personal views of the originator.  If you receive this e-mail in error, please advise the sender
immediatelyby using the reply facility in your e-mail software, or contact postmaster@bmwfin.com.  Please also delete
thise-mail and all documents attached immediately.  
 
Many thanks for your co-operation.

BMW Financial Services (GB) Limited is registered in England and Wales under company number 01288537.
Registered Offices : Europa House, Bartley Way, Hook, Hants, RG27 9UF
------------------------------------------------------------------------------------------


Re: VIEW or Stored Proc - Is this even possible?

From
Richard Huxton
Date:
On Thursday 20 Feb 2003 10:09 am, Stephen.Thompson@bmwfin.com wrote:
> Hello,
>
> Thanks for your suggestion I will look into this further.
>
> The reason this issue exists is to do with address details. What we have is
> an address table based upon the PAF address structure. IE house number,
> house name, street, locality etc. The software that we are using to perform
> a mail merge will not remove blank lines from the address so we can end up
> with an address label looking as:
>
> My House
>
> Main Street
>
> Any Town
> County

Ah - what an irritating bit of software.

I'd be tempted to write a merged_address() function to return the whole thing
as a multi-line field if your mailmerge can handle that. It would do
something like:

SELECT COALESCE(street || '\n','') || COALESCE(town || '\n','') ||
COALESCE(county || '\n','') FROM my_addr;

and then trim the trailing '\n'.

Failing that, seeing as you're only ever going to have a few fields to deal
with you could define a mailing_list view which hard-coded address lines, but
you're going to end up with nested CASE elements.

--  Richard Huxton


Re: VIEW or Stored Proc - Is this even possible?

From
Stephen.Thompson@bmwfin.com
Date:
Thanks for that Richard, 

I will try your suggestions. I'll let you know my results.

Regards,

Stephen.



-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: 20 February 2003 11:08
To: Stephen.Thompson@bmwfin.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] VIEW or Stored Proc - Is this even possible?

On Thursday 20 Feb 2003 10:09 am, Stephen.Thompson@bmwfin.com wrote:
> Hello,
>
> Thanks for your suggestion I will look into this further.
>
> The reason this issue exists is to do with address details. What we have
is
> an address table based upon the PAF address structure. IE house number,
> house name, street, locality etc. The software that we are using to
perform
> a mail merge will not remove blank lines from the address so we can end up
> with an address label looking as:
>
> My House
>
> Main Street
>
> Any Town
> County

Ah - what an irritating bit of software.

I'd be tempted to write a merged_address() function to return the whole
thing 
as a multi-line field if your mailmerge can handle that. It would do 
something like:

SELECT COALESCE(street || '\n','') || COALESCE(town || '\n','') || 
COALESCE(county || '\n','') FROM my_addr;

and then trim the trailing '\n'.

Failing that, seeing as you're only ever going to have a few fields to deal 
with you could define a mailing_list view which hard-coded address lines,
but 
you're going to end up with nested CASE elements.

--  Richard Huxton


-------------------------------------------------------------------------------------------

Copyright material and/or confidential and/or privileged information may be contained in this e-mail and any attached
documents. The material and information is intended for the use of the intended addressee only.  If you are not the
intendedaddressee, or the person responsible for delivering it to the intended addressee, you may not copy, disclose,
distribute,disseminate or deliver it to anyone else or use it in any unauthorised manner or take or omit to take any
actionin reliance on it. To do so is prohibited and may be unlawful.   The views expressed in this e-mail may not be
officialpolicy but the personal views of the originator.  If you receive this e-mail in error, please advise the sender
immediatelyby using the reply facility in your e-mail software, or contact postmaster@bmwfin.com.  Please also delete
thise-mail and all documents attached immediately.  
 
Many thanks for your co-operation.

BMW Financial Services (GB) Limited is registered in England and Wales under company number 01288537.
Registered Offices : Europa House, Bartley Way, Hook, Hants, RG27 9UF
------------------------------------------------------------------------------------------