SQL query (hoho)

[Famous Graham]

Quickie - is it possible to "ignore" fields if they're empty?

ie, if I want to

SELECT people.homeaddress1, people.homeaddress2, people.homeaddress3 etc etc

but not get homeaddress3 if it happens to be null (some will have data, some won't).

I know I can do a text string search on double delimiters to the resultant output, but it strikes me as "safer" if I can do it at source.

[shoggoth1]

Do you not have it in your where clause, something like ' where <blah> not null '. If it's a string field you might also need something like 'and <blah> != '' '

Sorry, just re-read the original post. The above will omit the entire record.

Edited by shoggoth1 on Monday 8th March 16:58

[Famous Graham]

No, cos that would exclude the row itself.


eta -

Edited by Famous Graham on Monday 8th March 16:58

[zac510]

You could concatenate them together so it looks like only one field.

Assuming that one of the three will have data in it at any time.

[130R]

Select a, b, c, d from ...

Some return values are null so you get 2 return values that are non null: 'something', 'something else'. Which values did you get back?

Edited by 130R on Monday 8th March 17:00

[strudel]

Unless you're not returning it as a table, surely a blank record won't return anything anyway? You could put an inline if in if you want a reformat at query time.

[deevee]

But if you manage to do this, then you'll have variable format records for whatever you want to do with the data next - even if you combine it into a delimited text field, you'll have a different number of delimiters for one format or the other. Is it not better to keep a blank/null field or "" delimeter pair and null it out/deal with it at the final step? Obviously, not knowing what purpose the data is for!

[Famous Graham]

Good point, should have said.

It's just address data. But not everyone needs 3 lines for their address, particularly when there are separate town, county & postcode fields.

Assume for a second that there's ONLY address fields in the record, if I did a SELECT ALL and just dumped it straight out to a CSV for producing labels, there will be occassions where there'll be a blank line on the label, due to address3, or sometimes even address2 as well, being empty.

In the past, I've run a find and replace on the field delimiter (ie, |3 Atoll Crescent||Edinburgh| - find the || and replace it with a single |).

So there's no need to sort this out from a "end result" persepctive. I'm just curious as it seems more "clean" to exclude the empty fields in the very beginning.

[Don]

You will not be able to do this.

Each tuple returned by a SQL statement is of the same format.

This has been true of every SQL database I have known - if someone can come up with one that's different I will stand corrected but I have never, ever seen a variable structure returned by a SQL query.

However - I take it you have an address and you don't want to output missing lines?

Something like this might be what you want

SELECT (CASE ISNULL(ADDRESSLINE1,'') WHEN '' THEN '' ELSE ADDRESSLINE1 + '\r\n') +
(CASE ISNULL(ADDRESSLINE2,'') WHEN '' THEN '' ELSE ADDRESSLINE2 + '\r\n') +
(CASE ISNULL(ADDRESSLINE3,'') WHEN '' THEN '' ELSE ADDRESSLINE3 + '\r\n') AS ADDRESS
FROM ADDRESSTABLE
WHERE...

[130R]

Famous Graham said:

I'm just curious as it seems more "clean" to exclude the empty fields in the very beginning.

Usually you are mapping the results of a SQL query to variables. If you execute the query:

select a, b, c, d from xyz

and get back one value: 'Smith' (because 3 of the 4 were null) this would generally not be very helpful because you have no idea what parameter the resulting value 'Smith' maps to. Hence why you cannot do this (or at least I am not aware of any way you can do this)

[deevee]

Famous Graham said:

In the past, I've run a find and replace on the field delimiter (ie, |3 Atoll Crescent||Edinburgh| - find the || and replace it with a single |).

Think this might be your best bet then Graham - all the data processing I've done before with name and address data has used 'shuffle up' type reformatters to sort out blank lines (and other nasties) before the data goes off to the printers.

[Famous Graham]

130R said:

Famous Graham said:

I'm just curious as it seems more "clean" to exclude the empty fields in the very beginning.

Usually you are mapping the results of a SQL query to variables. If you execute the query:

select a, b, c, d from xyz

and get back one value: 'Smith' (because 3 of the 4 were null) this would generally not be very helpful because you have no idea what parameter the resulting value 'Smith' maps to. Hence why you cannot do this (or at least I am not aware of any way you can do this)

A good point.

Also, cheers Don, I might give that a go.

Deevee - aye, thought it might be the case, but considered it worth investigating

[SteveTusc]

ISNULL will be the nearest assuming your next field will always be populated

ISNULL(Address3,Address4)as "whatever you want"

That said you can expand this to look to address5 if both are Null.

Depends what you want to do with the data I guess.

[Planet Claire]

My SQL isn't that good to help directly, but if you are using the data for a mail merge then, assuming you are using Word, you can "ignore blank records". Surely this would overcome your problem, or is it a bit more complex than that?

I use Business Objects on a daily basis and are often asked to produce mail merge data. Some address fields are blank but that doesn't matter because they don't get "printed" anyway during the mail merge.