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
>

Help on deviceInfo in rs.Render please...

How can I set my margins and all my page setup with this string please?
deviceInfo = String.Format("Margins', Scale')
Thanks,
TrintTrint,
Unfortunately there does not appear to be any way to specify DeviceInfo
with rs.Render. This is an issue for us because we cannot specify the
delimiter to be used for CSV.
Bob
"trint" wrote:
> How can I set my margins and all my page setup with this string please?
> deviceInfo = String.Format("Margins', Scale')
> Thanks,
> Trint
>|||So, I have no way of setting up the way my page is to be rendered?
thanks,
Trint
bobhug wrote:
> Trint,
> Unfortunately there does not appear to be any way to specify
DeviceInfo
> with rs.Render. This is an issue for us because we cannot specify the
> delimiter to be used for CSV.
> Bob
> "trint" wrote:
> > How can I set my margins and all my page setup with this string
please?
> > deviceInfo = String.Format("Margins', Scale')
> > Thanks,
> > Trint
> >
> >|||Sorry, my previous post was a mistake :>(. Please ignore it.
Bob
"bobhug" wrote:
> Trint,
> Unfortunately there does not appear to be any way to specify DeviceInfo
> with rs.Render. This is an issue for us because we cannot specify the
> delimiter to be used for CSV.
> Bob
> "trint" wrote:
> > How can I set my margins and all my page setup with this string please?
> > deviceInfo = String.Format("Margins', Scale')
> > Thanks,
> > Trint
> >
> >|||You use the DeviceInfo parameter. It is an XML string that has the settings
for the rendering extension. The properties supported are documented in
Books Online.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"trint" <trinity.smith@.gmail.com> wrote in message
news:1106585573.383643.243230@.c13g2000cwb.googlegroups.com...
> So, I have no way of setting up the way my page is to be rendered?
> thanks,
> Trint
> bobhug wrote:
>> Trint,
>> Unfortunately there does not appear to be any way to specify
> DeviceInfo
>> with rs.Render. This is an issue for us because we cannot specify the
>> delimiter to be used for CSV.
>> Bob
>> "trint" wrote:
>> > How can I set my margins and all my page setup with this string
> please?
>> > deviceInfo = String.Format("Margins', Scale')
>> > Thanks,
>> > Trint
>> >
>> >
>|||I know...but what I want to know is if the problem of 'emf' not
printing the footer of my report, when rendered to an 'emf', can
somehow be overcome by an xml code of some type besides what I have in
there now?:
deviceInfo =String.Format(@."<DeviceInfo>" +
"<OutputFormat>{0}</OutputFormat>" +
"<Toolbar>False</Toolbar>" +
"<PageHeight>55.82cm</PageHeight>" +
"<PageWidth>40.26cm</PageWidth></DeviceInfo>","emf");
thanks,
Trint

Help Needed For Date format in SSRS 2005

Hi experts,
I am working on SQL server 2005 reporting services and i am getting a
problem.
I am developing a report in which i am taking two parameters
one is FromDate and second is ToDate and i have changed thier Data
type as Date Time.
So it is giving callender control in reports. And default values for
both parameter is todays system date.
Now I want these dates in dd/mm/yyyy format so i changed the setting
of my system for the required date format.
In parameter selection box date format is correct it is comming in dd/
mm/yyyy format.
But again I am using a text box in the report body which tell us a
message that this report is contains the data between these dates.
For this I am using the expression
="The following data is for the period between " & Parameters!
FromDate.Value & " and " & Parameters!ToDate.Value
So here i am getting these dates in the mm/dd/yyyy format.
I have tried this also
="The following data is for the period between " & Parameters!
FromDate.Label & " and " & Parameters!ToDate.Label
The no date is comming in the text box.
I have tried Cdate function and other functions in DateTime function
which are available in the reporting services property box, But i am
not finding the solution for this problem.
So if any body is having any idea about this then please help me.
Any help wil be appriciated.
Regards
DineshHi Dinesh,
Make sure the report language is set to Australia (or any other language
that supports this format by default) , you find this under the properties
dialog box when you only select the form (not any controls on it).
Cheers
Matt
"Dinesh" wrote:
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>|||try something like:
Parameters!ToDate.Value.ToString("dd/mm/yyyy")
or
DateTime.Parse(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
or
CDate(Parameters!ToDate.Value).ToString("dd/mm/yyyy")
I don't remember which one works or not
good luck!
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1189587299.387328.305020@.50g2000hsm.googlegroups.com...
> Hi experts,
> I am working on SQL server 2005 reporting services and i am getting a
> problem.
> I am developing a report in which i am taking two parameters
> one is FromDate and second is ToDate and i have changed thier Data
> type as Date Time.
> So it is giving callender control in reports. And default values for
> both parameter is todays system date.
> Now I want these dates in dd/mm/yyyy format so i changed the setting
> of my system for the required date format.
> In parameter selection box date format is correct it is comming in dd/
> mm/yyyy format.
> But again I am using a text box in the report body which tell us a
> message that this report is contains the data between these dates.
> For this I am using the expression
> ="The following data is for the period between " & Parameters!
> FromDate.Value & " and " & Parameters!ToDate.Value
> So here i am getting these dates in the mm/dd/yyyy format.
> I have tried this also
> ="The following data is for the period between " & Parameters!
> FromDate.Label & " and " & Parameters!ToDate.Label
> The no date is comming in the text box.
> I have tried Cdate function and other functions in DateTime function
> which are available in the reporting services property box, But i am
> not finding the solution for this problem.
> So if any body is having any idea about this then please help me.
> Any help wil be appriciated.
> Regards
> Dinesh
>