SQL Case Statement

bigbadcon

Hi guys,

Im hoping this is an easy one...not for me though

I have 10 possible location fields in my items table and I want to display them in the following format

1-2-3-4-5-6-7-8-9-10

SELECT
	CASE Location1 WHEN null THEN '' ELSE Location1  END + CASE Location2 WHEN null THEN '' ELSE '-'+Location2 END +
    CASE Location3 WHEN null THEN '' ELSE '-'+Location3 END + CASE Location4 WHEN null THEN '' ELSE '-'+Location4 END + 
    CASE Location5 WHEN null THEN '' ELSE '-'+Location5 END + CASE Location6 WHEN null THEN '' ELSE '-'+Location6 END + 
    CASE Location7 WHEN null THEN '' ELSE '-'+Location7 END + CASE Location8 WHEN null THEN '' ELSE '-'+Location8 END + 
    CASE Location9 WHEN null THEN '' ELSE '-'+Location9 END + CASE Location10 WHEN null THEN '' ELSE '-'+Location10 END as Location,
	FROM Items

It works fine when I have all 10 fields with data in them but when there is a null value in the field it doesnt display anything...


What I want it to do is to display 1-2-3-4-5-6 if there are only values in the Locations 1-6.

Just had a brain wave....is it parenthesis related and if so where do they go?

Any ideas?

Thanks

kayos

No need for a case statement

SELECT 
	COALESCE(Location1,'') + 
	COALESCE('-' + Location2,'') +
	COALESCE('-' + Location3,'') +
	COALESCE('-' + Location4,'') +
	COALESCE('-' + Location5,'') +
	COALESCE('-' + Location6,'') +
	COALESCE('-' + Location7,'') +
	COALESCE('-' + Location8,'') +
	COALESCE('-' + Location9,'') +
	COALESCE('-' + Location10,'')  AS Location

Just as an aside this smells of poor schema design....

Also this is your query fixed for you

SELECT
	CASE 
		WHEN Location1 IS null THEN '' 
		ELSE Location1  
	END + 
	CASE 
		WHEN Location2 is null THEN '' 
		ELSE '-'+Location2 
	END +
    CASE 
		WHEN Location3 IS null THEN '' 
		ELSE '-'+Location3 
	END + 
	CASE 
		WHEN Location4 IS null THEN '' 
		ELSE '-'+Location4 
	END + 
    CASE 
		WHEN Location5 IS null THEN '' 
		ELSE '-'+Location5 
	END + 
	CASE 
		WHEN Location6 IS null THEN '' 
		ELSE '-'+Location6 
	END + 
    CASE 
		WHEN Location7 IS null THEN '' 
		ELSE '-'+Location7 
	END + 
	CASE 
		WHEN Location8 IS null THEN '' 
		ELSE '-'+Location8 
	END + 
    CASE 
		WHEN Location9 IS null THEN '' 
		ELSE '-'+Location9 
	END + 
	CASE 
		WHEN Location10 IS null THEN '' 
		ELSE '-'+Location10 
	END as Location
	FROM 
		@tbl

Reason it was not working is you need to use IS NULL when testing for a null value the case statement you had written is effectively using = NULL which doesn't work (unless you have the option turned on to allow it, if it still exists!)

bigbadcon

Cool thanks for that...

Thats a much nicer solution.

There is a bit of a reek off the schema alright.Not my call though

Cheers

stephenlane80

bigbadcon wrote: »

Cool thanks for that...

Thats a much nicer solution.

There is a bit of a reek off the schema alright.Not my call though

Cheers

a bit of normalisation wouldnt go astray !

bigbadcon

The table usually has only one location field but its for a demo system so I had to add the possiblilty of up to 10 to cover any scenario we may come across.

Out of interest is putting a location Id in the Items table and a seperate locations table with all of the other data what you mean?

Gambler

not sure if this is relevant but when I want to do that I just use

Select IsNull(MyField, '') from MyTable

Works perfectly for me in MSSQL and works just as well for values IsNull(Myfield, 0)

bigbadcon

Dont think I could have used ISNULL cause I needed an ELSE to display the location along with the '-' if it was there.

Gambler

Ahhhh ok I see what you're trying to do there..

kayos

bigbadcon wrote: »

The table usually has only one location field but its for a demo system so I had to add the possiblilty of up to 10 to cover any scenario we may come across.

Out of interest is putting a location Id in the Items table and a seperate locations table with all of the other data what you mean?

As it stand if your 10 location columns turned out to be not enough you would have to expand your table to add more columns (just like you are having to do now to allow for more than one) then of course modify code to take them into account. Other down sides to your current schema is lack of decent indexing and space issues (should the 10 location columns be a fixed size datatype).

You have an Item table as it stands we can only guess you have a location table also. So to normalise this out what you would do is add an ItemLocation table

So in its most basic form your schema would be

Item
- ItemID (PK)
- Other item info

Location
- LocationID (PK)
- Other location info

ItemLocation
- ItemID(PK)
- LocationID(PK)

This allows you to keep your existing Item and Location Tables pretty much intact but provides a mapping of Items to locations.

As it stands if you were asked to write a query to show all the items available at a given location you would have a good bit of SQL to write. Using a table like ItemLocation makes these queries easy.

Also Indexing is now perfect and you would not be wasting space. Your code can be written once and no matter how many locations you need to allow for it will remain the same (well it should do :P).

Gambler wrote: »

not sure if this is relevant but when I want to do that I just use

Select IsNull(MyField, '') from MyTable

Works perfectly for me in MSSQL and works just as well for values IsNull(Myfield, 0)

Yup ISNULL() does the same as COALESCE but is not ANSI compliant. Also ISNULL() only takes one expression to evaluate where COALESCE can take any number. So the two below statements do the same thing but one is a lot easier to read

COALESCE(Col1, Col2, Col3, Col4, Col5, 0)
ISNULL(Col1, ISNULL(Col2,ISNULL(Col3,ISNULL(Col4,ISNULL(Col5,0)))))

bigbadcon wrote: »

Dont think I could have used ISNULL cause I needed an ELSE to display the location along with the '-' if it was there.

You can use ISNULL() in the statement I gave you JUST replace all the COALESCE's with ISNULL

I don’t actually use an else any where in the query, I just append – and the value of the Location column together and test the result for being NULL. Don’t forget NULL + anything = NULL unless you have changed the default option on the handling of NULL on the server (dont do that btw).

bigbadcon

kayos wrote: »

Don’t forget NULL + anything = NULL unless you have changed the default option on the handling of NULL on the server (dont do that btw).

I didnt forget it...I never knew it

That a mill for your help. Lots of gems in there...

Appreciate it