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