Does RS contain a Report Header section?

Hello,
I can see how to add a page header to a report in Reporting Services. But
is there a Report Header section? What I need to do is to display a reoprt
title on the Report Header, then force a new page after the Report Header
section and list my data. How can something like this be accomplished using
Reporting Services?
Thanks,
RichWe use a rectangle on the first page and put our intro page there, then
check the page break after property.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> Hello,
> I can see how to add a page header to a report in Reporting Services. But
> is there a Report Header section? What I need to do is to display a
> reoprt
> title on the Report Header, then force a new page after the Report Header
> section and list my data. How can something like this be accomplished
> using
> Reporting Services?
> Thanks,
> Rich|||Thank you (again). It is starting to make sense.
Rich
"Steve MunLeeuw" wrote:
> We use a rectangle on the first page and put our intro page there, then
> check the page break after property.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> > Hello,
> >
> > I can see how to add a page header to a report in Reporting Services. But
> > is there a Report Header section? What I need to do is to display a
> > reoprt
> > title on the Report Header, then force a new page after the Report Header
> > section and list my data. How can something like this be accomplished
> > using
> > Reporting Services?
> >
> > Thanks,
> > Rich
>
>|||Forgot to ask -- so I add a rectangle - I am guessing to the detail
section, then I place a page break there. I have placed 5 textboxes in a
list control. This report prints labels (real pain to set the column widths
a row spacings just right). I don't want to upset the spacings of the
textboxes because it took me a long time (and a lot of tree killing and ink
usage) to get the spacings just right in the detail section.
May I ask where I place the rectangle?
And to stretch this inch to a mile, I was just told that the labels have to
be all in uppercase. In my winform app there is a textbox property for
charactercasing. I did not see that in the RDL. I looked at the Globals
collection but nothing caught my attention for forcing upper case. I could
force upper case at the datasource level, but is there a way to specify/force
character casing at the Report Textbox level? How to do that?
Thank you for your help and patience. I really appreciate it.
Thanks,
Rich
"Steve MunLeeuw" wrote:
> We use a rectangle on the first page and put our intro page there, then
> check the page break after property.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> > Hello,
> >
> > I can see how to add a page header to a report in Reporting Services. But
> > is there a Report Header section? What I need to do is to display a
> > reoprt
> > title on the Report Header, then force a new page after the Report Header
> > section and list my data. How can something like this be accomplished
> > using
> > Reporting Services?
> >
> > Thanks,
> > Rich
>
>|||I think you would want the report header rectangle at the begining of the
report body. I see your point on the layout, nothing comes to mind...you
can have a zero height, but then it would be pretty darn hard to add your
textbox layout.
Here's what I'd try:
Make a backup copy, or use source control
Note the top property of the topmost item in the body
Cut everything in the body
Add two rectangles
Paste everything into the second rectangle
Use the up/down arrows with everything selected in the rectangle so you get
the same top spot noted before
set the page break property on the first rectangle
For the uppercase you have access to quite a bit.
try:
System.String.ToUpper(field!myField.Value)
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9B516761-4FB4-4ADF-9417-12904AB2889B@.microsoft.com...
> Forgot to ask -- so I add a rectangle - I am guessing to the detail
> section, then I place a page break there. I have placed 5 textboxes in a
> list control. This report prints labels (real pain to set the column
> widths
> a row spacings just right). I don't want to upset the spacings of the
> textboxes because it took me a long time (and a lot of tree killing and
> ink
> usage) to get the spacings just right in the detail section.
> May I ask where I place the rectangle?
> And to stretch this inch to a mile, I was just told that the labels have
> to
> be all in uppercase. In my winform app there is a textbox property for
> charactercasing. I did not see that in the RDL. I looked at the Globals
> collection but nothing caught my attention for forcing upper case. I
> could
> force upper case at the datasource level, but is there a way to
> specify/force
> character casing at the Report Textbox level? How to do that?
> Thank you for your help and patience. I really appreciate it.
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> We use a rectangle on the first page and put our intro page there, then
>> check the page break after property.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
>> > Hello,
>> >
>> > I can see how to add a page header to a report in Reporting Services.
>> > But
>> > is there a Report Header section? What I need to do is to display a
>> > reoprt
>> > title on the Report Header, then force a new page after the Report
>> > Header
>> > section and list my data. How can something like this be accomplished
>> > using
>> > Reporting Services?
>> >
>> > Thanks,
>> > Rich
>>|||Cool. You actually gave me another idea (BTW, right after I begged for help
on ucase, I did in search for uppercase and discover ucase
(field!myfld.Value).toupper and your method - well, at least I got
charactercasing covered :).
Anyway, my idea for the title page was to place the rectangle/control
(actually a textbox which will take a param because the title will change
based on selections) on top of the other textboxes. On the first page, the
data textboxes are not visible, then after the first page, the title textbox
is not visible but the data textboxes are visible. Does this sound possible?
I am thinking I could set the pagebreak on the rectangle and have an
expression to control the visibility of the controls. Any comments
appreciated if this is doable or not.
Thanks again,
Rich
"Steve MunLeeuw" wrote:
> I think you would want the report header rectangle at the begining of the
> report body. I see your point on the layout, nothing comes to mind...you
> can have a zero height, but then it would be pretty darn hard to add your
> textbox layout.
> Here's what I'd try:
> Make a backup copy, or use source control
> Note the top property of the topmost item in the body
> Cut everything in the body
> Add two rectangles
> Paste everything into the second rectangle
> Use the up/down arrows with everything selected in the rectangle so you get
> the same top spot noted before
> set the page break property on the first rectangle
> For the uppercase you have access to quite a bit.
> try:
> System.String.ToUpper(field!myField.Value)
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9B516761-4FB4-4ADF-9417-12904AB2889B@.microsoft.com...
> > Forgot to ask -- so I add a rectangle - I am guessing to the detail
> > section, then I place a page break there. I have placed 5 textboxes in a
> > list control. This report prints labels (real pain to set the column
> > widths
> > a row spacings just right). I don't want to upset the spacings of the
> > textboxes because it took me a long time (and a lot of tree killing and
> > ink
> > usage) to get the spacings just right in the detail section.
> >
> > May I ask where I place the rectangle?
> >
> > And to stretch this inch to a mile, I was just told that the labels have
> > to
> > be all in uppercase. In my winform app there is a textbox property for
> > charactercasing. I did not see that in the RDL. I looked at the Globals
> > collection but nothing caught my attention for forcing upper case. I
> > could
> > force upper case at the datasource level, but is there a way to
> > specify/force
> > character casing at the Report Textbox level? How to do that?
> >
> > Thank you for your help and patience. I really appreciate it.
> >
> > Thanks,
> > Rich
> >
> > "Steve MunLeeuw" wrote:
> >
> >> We use a rectangle on the first page and put our intro page there, then
> >> check the page break after property.
> >>
> >> Steve MunLeeuw
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> >> > Hello,
> >> >
> >> > I can see how to add a page header to a report in Reporting Services.
> >> > But
> >> > is there a Report Header section? What I need to do is to display a
> >> > reoprt
> >> > title on the Report Header, then force a new page after the Report
> >> > Header
> >> > section and list my data. How can something like this be accomplished
> >> > using
> >> > Reporting Services?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>|||I wouldn't do that, the text overlap will result in a compile warning and
cause layout problems in html format.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2CC85597-4934-4E07-B5FE-FD3DF1211985@.microsoft.com...
> Cool. You actually gave me another idea (BTW, right after I begged for
> help
> on ucase, I did in search for uppercase and discover ucase
> (field!myfld.Value).toupper and your method - well, at least I got
> charactercasing covered :).
> Anyway, my idea for the title page was to place the rectangle/control
> (actually a textbox which will take a param because the title will change
> based on selections) on top of the other textboxes. On the first page,
> the
> data textboxes are not visible, then after the first page, the title
> textbox
> is not visible but the data textboxes are visible. Does this sound
> possible?
> I am thinking I could set the pagebreak on the rectangle and have an
> expression to control the visibility of the controls. Any comments
> appreciated if this is doable or not.
> Thanks again,
> Rich
> "Steve MunLeeuw" wrote:
>> I think you would want the report header rectangle at the begining of the
>> report body. I see your point on the layout, nothing comes to mind...you
>> can have a zero height, but then it would be pretty darn hard to add your
>> textbox layout.
>> Here's what I'd try:
>> Make a backup copy, or use source control
>> Note the top property of the topmost item in the body
>> Cut everything in the body
>> Add two rectangles
>> Paste everything into the second rectangle
>> Use the up/down arrows with everything selected in the rectangle so you
>> get
>> the same top spot noted before
>> set the page break property on the first rectangle
>> For the uppercase you have access to quite a bit.
>> try:
>> System.String.ToUpper(field!myField.Value)
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:9B516761-4FB4-4ADF-9417-12904AB2889B@.microsoft.com...
>> > Forgot to ask -- so I add a rectangle - I am guessing to the detail
>> > section, then I place a page break there. I have placed 5 textboxes
>> > in a
>> > list control. This report prints labels (real pain to set the column
>> > widths
>> > a row spacings just right). I don't want to upset the spacings of the
>> > textboxes because it took me a long time (and a lot of tree killing and
>> > ink
>> > usage) to get the spacings just right in the detail section.
>> >
>> > May I ask where I place the rectangle?
>> >
>> > And to stretch this inch to a mile, I was just told that the labels
>> > have
>> > to
>> > be all in uppercase. In my winform app there is a textbox property for
>> > charactercasing. I did not see that in the RDL. I looked at the
>> > Globals
>> > collection but nothing caught my attention for forcing upper case. I
>> > could
>> > force upper case at the datasource level, but is there a way to
>> > specify/force
>> > character casing at the Report Textbox level? How to do that?
>> >
>> > Thank you for your help and patience. I really appreciate it.
>> >
>> > Thanks,
>> > Rich
>> >
>> > "Steve MunLeeuw" wrote:
>> >
>> >> We use a rectangle on the first page and put our intro page there,
>> >> then
>> >> check the page break after property.
>> >>
>> >> Steve MunLeeuw
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I can see how to add a page header to a report in Reporting
>> >> > Services.
>> >> > But
>> >> > is there a Report Header section? What I need to do is to display a
>> >> > reoprt
>> >> > title on the Report Header, then force a new page after the Report
>> >> > Header
>> >> > section and list my data. How can something like this be
>> >> > accomplished
>> >> > using
>> >> > Reporting Services?
>> >> >
>> >> > Thanks,
>> >> > Rich
>> >>
>> >>
>> >>
>>|||Back in the office. So I will try your previous suggestion and hope for the
best.
Thank you again for your help.
Rich
"Steve MunLeeuw" wrote:
> I wouldn't do that, the text overlap will result in a compile warning and
> cause layout problems in html format.
> Steve MunLeeuw
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2CC85597-4934-4E07-B5FE-FD3DF1211985@.microsoft.com...
> > Cool. You actually gave me another idea (BTW, right after I begged for
> > help
> > on ucase, I did in search for uppercase and discover ucase
> > (field!myfld.Value).toupper and your method - well, at least I got
> > charactercasing covered :).
> >
> > Anyway, my idea for the title page was to place the rectangle/control
> > (actually a textbox which will take a param because the title will change
> > based on selections) on top of the other textboxes. On the first page,
> > the
> > data textboxes are not visible, then after the first page, the title
> > textbox
> > is not visible but the data textboxes are visible. Does this sound
> > possible?
> >
> > I am thinking I could set the pagebreak on the rectangle and have an
> > expression to control the visibility of the controls. Any comments
> > appreciated if this is doable or not.
> >
> > Thanks again,
> > Rich
> >
> > "Steve MunLeeuw" wrote:
> >
> >> I think you would want the report header rectangle at the begining of the
> >> report body. I see your point on the layout, nothing comes to mind...you
> >> can have a zero height, but then it would be pretty darn hard to add your
> >> textbox layout.
> >>
> >> Here's what I'd try:
> >> Make a backup copy, or use source control
> >> Note the top property of the topmost item in the body
> >> Cut everything in the body
> >> Add two rectangles
> >> Paste everything into the second rectangle
> >> Use the up/down arrows with everything selected in the rectangle so you
> >> get
> >> the same top spot noted before
> >> set the page break property on the first rectangle
> >>
> >> For the uppercase you have access to quite a bit.
> >> try:
> >> System.String.ToUpper(field!myField.Value)
> >>
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:9B516761-4FB4-4ADF-9417-12904AB2889B@.microsoft.com...
> >> > Forgot to ask -- so I add a rectangle - I am guessing to the detail
> >> > section, then I place a page break there. I have placed 5 textboxes
> >> > in a
> >> > list control. This report prints labels (real pain to set the column
> >> > widths
> >> > a row spacings just right). I don't want to upset the spacings of the
> >> > textboxes because it took me a long time (and a lot of tree killing and
> >> > ink
> >> > usage) to get the spacings just right in the detail section.
> >> >
> >> > May I ask where I place the rectangle?
> >> >
> >> > And to stretch this inch to a mile, I was just told that the labels
> >> > have
> >> > to
> >> > be all in uppercase. In my winform app there is a textbox property for
> >> > charactercasing. I did not see that in the RDL. I looked at the
> >> > Globals
> >> > collection but nothing caught my attention for forcing upper case. I
> >> > could
> >> > force upper case at the datasource level, but is there a way to
> >> > specify/force
> >> > character casing at the Report Textbox level? How to do that?
> >> >
> >> > Thank you for your help and patience. I really appreciate it.
> >> >
> >> > Thanks,
> >> > Rich
> >> >
> >> > "Steve MunLeeuw" wrote:
> >> >
> >> >> We use a rectangle on the first page and put our intro page there,
> >> >> then
> >> >> check the page break after property.
> >> >>
> >> >> Steve MunLeeuw
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> >> >> > Hello,
> >> >> >
> >> >> > I can see how to add a page header to a report in Reporting
> >> >> > Services.
> >> >> > But
> >> >> > is there a Report Header section? What I need to do is to display a
> >> >> > reoprt
> >> >> > title on the Report Header, then force a new page after the Report
> >> >> > Header
> >> >> > section and list my data. How can something like this be
> >> >> > accomplished
> >> >> > using
> >> >> > Reporting Services?
> >> >> >
> >> >> > Thanks,
> >> >> > Rich
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||>Here's what I'd try:
>Make a backup copy, or use source control
>Note the top property of the topmost item in the body
>Cut everything in the body
>Add two rectangles
>Paste everything into the second rectangle
>Use the up/down arrows with everything selected in the rectangle so you get
>the same top spot noted before
>set the page break property on the first rectangle
I am not quite sure what you mean about using the up/down arrows. Do you
mean the up/down arrow keys? The textboxes are located inside a list
control. The list control and the topmost textbox are at the very top of the
body position 0,0.
So I added the 2 rectangles and have copied the contents of the list control
(and all the textboxes inside the list control) to the 2nd rectangle.
Anyway, as I expected, I am having some issues with the layout. The
pagebreak for the first rectangle/title page didn't really break the page.
The data page is starting on the same page as the title page. I guess I will
have to experiment a little bit. Suggestions appreciated.
Thanks,
Rich
"Steve MunLeeuw" wrote:
> I think you would want the report header rectangle at the begining of the
> report body. I see your point on the layout, nothing comes to mind...you
> can have a zero height, but then it would be pretty darn hard to add your
> textbox layout.
> Here's what I'd try:
> Make a backup copy, or use source control
> Note the top property of the topmost item in the body
> Cut everything in the body
> Add two rectangles
> Paste everything into the second rectangle
> Use the up/down arrows with everything selected in the rectangle so you get
> the same top spot noted before
> set the page break property on the first rectangle
> For the uppercase you have access to quite a bit.
> try:
> System.String.ToUpper(field!myField.Value)
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9B516761-4FB4-4ADF-9417-12904AB2889B@.microsoft.com...
> > Forgot to ask -- so I add a rectangle - I am guessing to the detail
> > section, then I place a page break there. I have placed 5 textboxes in a
> > list control. This report prints labels (real pain to set the column
> > widths
> > a row spacings just right). I don't want to upset the spacings of the
> > textboxes because it took me a long time (and a lot of tree killing and
> > ink
> > usage) to get the spacings just right in the detail section.
> >
> > May I ask where I place the rectangle?
> >
> > And to stretch this inch to a mile, I was just told that the labels have
> > to
> > be all in uppercase. In my winform app there is a textbox property for
> > charactercasing. I did not see that in the RDL. I looked at the Globals
> > collection but nothing caught my attention for forcing upper case. I
> > could
> > force upper case at the datasource level, but is there a way to
> > specify/force
> > character casing at the Report Textbox level? How to do that?
> >
> > Thank you for your help and patience. I really appreciate it.
> >
> > Thanks,
> > Rich
> >
> > "Steve MunLeeuw" wrote:
> >
> >> We use a rectangle on the first page and put our intro page there, then
> >> check the page break after property.
> >>
> >> Steve MunLeeuw
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:4C2F1C6A-60FF-4C27-B742-240B14387F52@.microsoft.com...
> >> > Hello,
> >> >
> >> > I can see how to add a page header to a report in Reporting Services.
> >> > But
> >> > is there a Report Header section? What I need to do is to display a
> >> > reoprt
> >> > title on the Report Header, then force a new page after the Report
> >> > Header
> >> > section and list my data. How can something like this be accomplished
> >> > using
> >> > Reporting Services?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>

Does all of WHERE clause get executed?

Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar bu
t
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first an
d
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the convert
!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!IsNumeric will return 1 for some strings that can't be converted to an
integer, but can be converted to other numeric datatypes, like '1E10', which
can be converted to a float, or '200,000.00', which can be converted to
money.
http://www.aspfaq.com/show.asp?id=2390 gives you a workaround.
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
> Hi there.
> I have an update statement that wishes to update certain fields depending
> on
> a column that may or may not contain numeric values (the field is varchar
> but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update
> statement
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
> and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen
> cases
> on another server where the update statement still fails due to
> non-numeric
> values.
> Q: Is there some server setting that would dictate whether all criteria in
> a
> WHERE clause will get executed? Or in which order they get executed? I.e:
> Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the
> convert!
> I just want to know whether this is down to my code or is I should look
> for
> some other issue.
> Any help would be appreciated!|||Hi
The query processor can choose to execute a query in any way it pleases.
Depending on statistics, indexes, processors and RAM, it might decide to
execute a different query plan.
Have you tried:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
FROM
MyTable
INNER JOIN
OtherTable
ON
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
WHERE
IsNumeric(MyTable.MaybeNumeric) = 1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"len" wrote:

> Hi there.
> I have an update statement that wishes to update certain fields depending
on
> a column that may or may not contain numeric values (the field is varchar
but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update statemen
t
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen cas
es
> on another server where the update statement still fails due to non-numeri
c
> values.
> Q: Is there some server setting that would dictate whether all criteria in
a
> WHERE clause will get executed? Or in which order they get executed? I.e:
Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the conve
rt!
> I just want to know whether this is down to my code or is I should look fo
r
> some other issue.
> Any help would be appreciated!|||Optimiser can choose whatever evaluation path it sees fit. Look for "short
circuit" in the page below and see if it helps:
http://msdn.microsoft.com/library/d...
heckitout.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar
but
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first
and
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the
convert!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!|||A more detailed analysis of the "short circuit" feature in SQL Server
is written in the following article (also by Itzik Ben-Gan):
http://www.windowsitpro.com/Article...?ArticleID=9148
Razvan|||>> I have an update statement that wishes to update certain fields [sic] depending on
a column that may or may not contain numeric values (the field [sic] is varchar b
ut may contain '001', '002' etc. <<
Exactly how did you get this kind of problem in the first place?
You have a huge design problem and need to change your schema, not go
hunting for kludges. One of the reasons that I beat on people about
not calling a "column" a "field" is that a field (a file processing
concept) can hold anything; it gets its meaning from being read by a
host program. A relational column has ONE AND ONLY ONE domain which
has ONE AND ONLY ONE data type. It has meaning in and of itself, it
enforces its own integrity, it does not depend on a host program.
If this data element is used for computations, then it needs to be a
numeric. If it is a tag number, then you can use character types. You
do not mix things like this in an RDBMS.
Also, you might want to stop using the unpredictable proprietary UPDATE
syntax.|||Len's requirement is common enough in an ELT data staging scenario. For
conforming external data I would typically load to an "untyped" table
(NVARCHAR throughout) first, perform the necessary validation, then
load the valid data to another staging table with the correct datatyes.
Any UPDATE against the actual data in the target database utilizes the
correctly typed table. This avoids type conversions and errors and
ensures you maximize the benefit of indexing on the two tables.
David Portas
SQL Server MVP
--|||On Wed, 25 May 2005 04:44:03 -0700, len wrote:
(snip)
>The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
>criteria in the WHERE clause - thinking that this will get executed first a
nd
>will exclude non-numeric rows.
Hi len,
As others have said: there's no guarantee.
You might wish to try this one:
UPDATE MyTable
SET MyTable.SomeColumn = OtherTable.SomeColumn
FROM OtherTable
WHERE OtherTable.DefinitelyNumeric =
CASE WHEN IsNumeric(MyTable.MaybeNumeric) = 1
THEN CONVERT(integer, MyTable.MaybeNumeric)
ELSE OtherTable.DefinitelyNumeric + 1
END
But do beware the gotchas with IsNumeric (see Jacco's post).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)