Using T-SQL to shift up blank fields in customer addresses

Question

Using T-SQL to shift up blank fields in customer addresses

tommill

SSC Journeyman

Points: 83
More actions
June 4, 2010 at 8:01 am

#233331

We have SQL Server as a back-end for a manufacturing system. I need to use T-SQL to create a view of our customer address table that can be easily read by our somewhat flaky reporting system, so all of the fields have to be populated correctly.
We have the following fields in each address record, but quite a few of them could contain 'nulls', so I need to use T-SQL to format before they are picked up by our reporting system.
The table name is 'customeraddresses', and the fields are 'name', 'street', 'city', 'county', 'postcode', 'country'.
It is quite common for one or more of these to have null values.
I need the report to be able to read in a series of columns from the view, such as 'A1', 'A2', 'A3', 'A4', 'A5', 'A6'. Normally, for a record with all address fields containing data, they would map as follows.
'A1' = 'name'
'A2' = 'street'
'A3' = 'city'
'A4' = 'county'
'A5' = 'postcode'
'A6' = 'country'
For example, if a record has values in 'name', 'street', 'city' and 'country', but nulls in 'county' and 'postcode', I need to end up with following result.
'A1' = 'name'
'A2' = 'street'
'A3' = 'city'
'A4' = 'country'
'A5' = 'xx'
'A6' = 'xx'
The 'xx' value is so that there is some data there, otherwise our reporting system will ignore the record as it cannot deal with nulls.
Can anyone suggest any code for this please?
I would be very grateful as I had tried a number of things but, as I am a newbie and only an occasional user, I haven't got very far.
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

Ouch... that really is something you should do in the front end, it's simple address formatting, don't you have developers that can handle those cases?

If not, well, you could do some select case to handle those special cases, but I really think this should be done in the front end, whatever reporting you are using must be able handle those special cases!

Cheers,

J-F

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Just guessing here, so correct me if I'm wrong. I'm assuming that you are doing sommething like

SELECT name, street, city, county, postcode, country

FROM customeraddresses

WHERE <some condition>

and that some of these columns may or may not contain NULLs that you want to replace by some other value. If that's correct, then what you want to accompish is rather trivial. All you need to do is change your query in the following manner:

SELECT

ISNULL(name, 'XX') AS 'name',

ISNULL(street, 'XX' ) AS 'street',

ISNULL(city, 'XX') AS 'city',

ISNULL(county, 'XX') AS 'county',

ISNULL(postcode, 'XX') AS 'postcode',

ISNULL(country, 'XX') AS 'country'

FROM customeraddresses

WHERE xyz

'

What ISNULL(a,b) does is that, should the first argument be NULL, it returns the second argument instead of the first. COALESCE(a,b,c,...) is a more versatile means of doing this, but is not required here (look it up in BOL if you are interested).

Hope this helps

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

tommill SSC Journeyman Points: 83 More actions · Answer 3

Thanks for the reply. Unfortunately, this did not resolve the 'shifting blank fields up' issue, but it gave me an idea whereby I could achieve that in the reporting system. It also introduced me to a couple of new T-SQL commands. Thanks again for your help.

tommill SSC Journeyman Points: 83 More actions · Answer 4

Thanks for your reply.

I did eventually manage to do this in the report writer, once I got around the problem of it ignoring any record that has a null value in any of its fields.

Thanks again.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

No problem, that's what we are here for.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]