[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4627
  • Last Modified:

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.
0
james1977
Asked:
james1977
  • 3
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello james1977,

can u post the Query,

CASE WHEN LEN(PostalSector) > 4 THEN LEFT (PostalSector,4)  ELSE LEFT(PostalSector, 2) END





Aneesh R
0
 
james1977Author Commented:
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.

0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
try this:

UPDATE PostalSectors
set postalsector =
(case
when len(PostalSector)>4
then replace(substring(postalsector,1,4),' ','')
else replace(substring(postalsector,1,2),' ','')
end)

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
james1977Author Commented:
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.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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!

0
 
james1977Author Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now