Help on Query

I have a table that has a field "Judge Name" that may contain names in the
format "J Jones" or "M Smith". I need to change that format to "Jones J" and
"Smith M". I am trying the following query thinking I can use that in an
Update query but I am getting the error:
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression."
I don't understand this? I am trying to say that if the second character is
a space, the name is in the old format and needs to be converted.
=============== Query ===================
IF (Select Substring([Judge Name], 2,1) From Test) = ' '
BEGIN
Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
Name],1)
From Test
END
Else
Select [Judge Name] From Test
========================================
=Wayne
If you want us to solve the problem, ,please post DDL+ sample data+ expected
result.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J"
>and "Smith M". I am trying the following query thinking I can use that in
>an Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
> is a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Here's one problem:

> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
You must limit the number of rows returned to one for this to work, or
better: look up EXISTS in Books Online.
Anyway, guessing from your post you need the CASE expression. Look it up in
Books Online.
Try this (untested, since you haven't posted DLL and sample data):
select case
when Substring([Judge Name], 2,1)
then RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' +
LEFT([Judge
Name],1)
else [Judge Name]
end
from Test
ML
http://milambda.blogspot.com/|||You are trying to use IF in a way that simply is not how it works.
Try something along these lines:
SELECT CASE WHEN Substring([Judge Name], 2,1) = ' '
THEN RIGHT([Judge Name], LEN([Judge Name])-2)
+ ' '
+ LEFT([Judge Name],1)
ELSE [Judge Name]
END as NewJudgeName
FROM Test
Roy
On Thu, 23 Feb 2006 04:26:40 -0700, "Wayne Wengert"
<wayneSKIPSPAM@.wengert.org> wrote:

>I have a table that has a field "Judge Name" that may contain names in the
>format "J Jones" or "M Smith". I need to change that format to "Jones J" an
d
>"Smith M". I am trying the following query thinking I can use that in an
>Update query but I am getting the error:
>"Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
>expression."
>I don't understand this? I am trying to say that if the second character is
>a space, the name is in the old format and needs to be converted.
>=============== Query ===================
>IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
>Name],1)
> From Test
> END
>Else
> Select [Judge Name] From Test
> ========================================
=
>|||How about :
update test
set [Judge Name] =
RIGHT([Judge Name], LEN([Judge Name])-2) +
' ' + LEFT([Judge Name],1)
where Substring([Judge Name], 2,1) = ' '
No case statements or procedural logic should be needed if this is all you
are trying to do.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> I have a table that has a field "Judge Name" that may contain names in the
> format "J Jones" or "M Smith". I need to change that format to "Jones J"
and
> "Smith M". I am trying the following query thinking I can use that in an
> Update query but I am getting the error:
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression."
> I don't understand this? I am trying to say that if the second character
is
> a space, the name is in the old format and needs to be converted.
> =============== Query ===================
> IF (Select Substring([Judge Name], 2,1) From Test) = ' '
> BEGIN
> Select RIGHT([Judge Name], LEN([Judge Name])-2) + ' ' + LEFT([Judge
> Name],1)
> From Test
> END
> Else
> Select [Judge Name] From Test
> ========================================
=
>|||Thanks. I was using a wrong approach.
Wayne
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:etO11NIOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> How about :
> update test
> set [Judge Name] =
> RIGHT([Judge Name], LEN([Judge Name])-2) +
> ' ' + LEFT([Judge Name],1)
> where Substring([Judge Name], 2,1) = ' '
> No case statements or procedural logic should be needed if this is all you
> are trying to do.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:e7zkFwGOGHA.140@.TK2MSFTNGP12.phx.gbl...
> and
> is
>