asked on

IF ELSE.....SELECT Query.

I am using MS SQL 2005 and trying to format some postal sectors.

If the length of the postal sector is greater than 4 I want to take out the spaces of the first 4 characters and if the length is less than 4 i want to replace the spaces in the first 2 characters but I keep getting the error message below:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone help me on this?

Regards,
James.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

james1977

ASKER

Oh sorry, didn't realise I hadn't given you the query!

BEGIN TRAN
IF ((SELECT LEN(PostalSector) FROM PostalSectors) > 4)
UPDATE PostalSectors
SET PostalSector = REPLACE(LEFT(PostalSector,4),' ','')
SELECT PostalSector FROM PostalSectors
ROLLBACK TRAN

I tried what you sugested and I get the error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.

Thanks for your input so far.
James.

SOLUTION

Sham Haque

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

james1977

ASKER

That seems to have done it, thanks, I will have a closer look tomorrow then award points, but at a quick glance it looks good. I'll have to do some research on the case statement.

Thanks again
James.

Sham Haque

hi james - glad you got the solution:

aneesh's suggestion of CASE was correct.

a CASE statement is like an IF, but has specific syntax and can be used in 2 ways - simple or searched - the example above is searched.
You can use it in a SELECT clause, but also as here in an UPDATE (replacing a SELECT or a value), but also in a WHERE clause, or even an ORDER BY to custom-order a result set!

james1977

ASKER

Thanks for your help guys, i've split the points then as I doubt I would have got it to work without your help as well gbshahaq.

I've used what you both said and come up with this solution that formats my postal sectors perfectly.

UPDATE PostalSectors
SET PostalSector = REPLACE(PostalSector, ' ', '')
Go

UPDATE PostalSectors
SET postalsector =
(CASE
WHEN LEN(PostalSector)=5 THEN LEFT(PostalSector,4) + ' ' + RIGHT(PostalSector,1)
WHEN LEN(PostalSector)=4 THEN LEFT(PostalSector,3) + ' ' + RIGHT(PostalSector,1)
WHEN LEN(PostalSector)=3 THEN LEFT(PostalSector,2) + ' ' + RIGHT(PostalSector,1)
END)

Thanks again
James.