| Author |
Topic |
|
wotrac
Yak Posting Veteran
98 Posts |
 Posted - 2005-01-16 : 13:52:16
|
| HiI am trying to create a view on an address table.I want to return one field containing the complete postal address.The fields are address, town, county, postcode.Sometimes the town/county or postcode fields will have no data in them and therefore I want to omit those fields when I concatenate everthing.I tried using a case statement, but that didn't appear to work.Any suggestions |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-16 : 14:20:48
|
| You probably have NULLs throwing this off. All you need to do for concatenation is use the "+" operator and ISNULL to account for the NULL values.SELECT ISNULL(address,'') + ISNULL(town,'') + ISNULL(county,'') + ISNULL(postcode,'')FROM addressHaving said that, this is probably a display issue handled better at the presentation level. Why are you wanting to handle it at the data layer?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-01-16 : 14:33:01
|
| I am trying to simplify a mail merge for the users.A view seemed the simplest way to clean up the data, so that they only have to worry about merging one field, without worrying about missing data |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-16 : 14:56:59
|
| If you are going to have them clean the data, then they need to know what's missing, right? I would think a good old spreadsheet would be the best tool for this. In that case, just do a normal select, drop it into Excel, have them clean it up, then import it back in. How are they going to know what's missing if you made it all one line? That seems to leave more room for error, not less.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-01-16 : 15:05:42
|
| The problem comes from the way the data is stored in the underlyingtables.Some companies have only one line for address and no town or county.When this is displayed using the mail merge address block, you get blank lines on some addresses.We wanted to get rid of the blank lines |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-01-16 : 15:07:41
|
| which brings me to my next questionI want to add carriage returns on valid fields nu not on empty ones |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-16 : 20:02:30
|
| Your last two statements and the original post don't make sense together. You want a view on an address table with all the fields concatenated. That doesn't make sense, but hey you can do it. You want to use it for a mail merge and add carriage returns on valid fields, but not on empty ones. So, are you now talking about the stored procedure you're going to use for producing the mail merge from the view? Sounds to me like you're wanting to create a mail merge in Access using the view in SQL Server. Am I very far off on this one??? Either way, pay very close attention:1. My name is MeanOldJedi.2. I'm waving my hand in front of your face.3. I'm telling you this is the wrong path and you are going to think before you do this.4. You are telling me this is the wrong path and you are going to think before you do this.5. Now you are going to remember the following things you mentioned to me:--You want users to clean this. Give them the recordset in Excel.--You want to use this for a mail merge. You can't mail to addresses without cities and states.--This is a BAD idea. Get the mail merge to work with the table you have AFTER you clean the data.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-16 : 20:41:35
|
| SELECT address + coalesce(char(13) + char(10) + town,'') + coalesce(char(13) + char(10) + county,'') + coalesce(char(13) + char(10) + postcode,'')FROM address==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-01-19 : 12:55:32
|
| Hi nrThats almost what I want, but I am still gettingblank fields in my mail merge, where no town or countyhas been entered into the field.I should point out that this is quite a common itemwhen entering addresses for place like London, where countydoes not exist. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-19 : 13:25:45
|
| When you say no entry do you mean null or blank?trySELECT address + case when rtrim(coalesce(town,'')) = '' then '' else char(13) + char(10) + town end+ case when rtrim(coalesce(county,'')) = '' then '' else char(13) + char(10) + county end+ case when rtrim(coalesce(postcode,'')) = '' then '' else char(13) + char(10) + postcode endFROM address==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-01-19 : 13:28:28
|
| Could be either |
|
|
|
|
concatenation