Execution Plan: February 2012

Sunday, February 26, 2012

Expand page header as matrix expands

Is there a way to tie the page header to the matrix so it expands to the width of the matrix as the matrix expands?

Thanks.

Vicki Ecker

Hello,

For physical paginated renderes, page header&footer width = page.Width – margin.Left – margin.Right

If your matrix requires multiple horizontal pages, the header&footer will be repeated for every horizontal page.

HTML renderer & Preview – are not bound horizontally by a page width.

The page header&footer will grow based on the runtime body width.

Thank you,

Nico

Expand disk for cluster

Good morning all...

We currently have a active/passive 2K cluster -- 100GB partition for data and another 100GB for backups. We just acquired another mass amount of databases from another division that we need to get online and running very soon, however we can't have a extensive outage of our cluster. PROBLEM: we need to expand the LUN on our SAN by another 150GB to accomodate for the new databases on the data partition, along with adding another 75GB on our backup partition.

Has anyone run into this situation where they are expanding their SAN to accomodate for growth? What did you do to minimize the downtime? Is it even possible? Or do I have to break the cluster, add the disk, rebuild the cluster and restore? GEESH, I hope not.

Thanks in advance!Refer to this link (http://www.sql-server-performance.com/clustering_intro2.asp) for more information.

Expand Database -- How long ??

I am running SQL 6.5 SP5a and am trying to expand my SMS
database from 195mb to 11GB.
When I run it the disks seem to be doing the work but
Enterprise manager looks like it has stopped responding.
Does this process take very long ' It is running now for
over 1hr.
ericWith 6.5, even on fairly modern disk systems, I'm thinking at least a couple
hours for that size expansion. You can get a rough estimate by looking at
perfmon disk bytes/sec and dividing ~11 Billion by that number to determine
the approximate elapsed time +/- 1000% :-)
And yes, EM will appear unresponsive.
----
The views expressed here are my own
and not of my employer.
----
"Dirc" <dirc.khan-evans@.eqos.com> wrote in message
news:OqEMt2SUDHA.1712@.tk2msftngp13.phx.gbl...
> Yes, usually takes a lot of time... esp with 6.5
>
> "Eric" <derick@.bge.ie> wrote in message
> news:0d9c01c35119$45664b70$a101280a@.phx.gbl...
> > I am running SQL 6.5 SP5a and am trying to expand my SMS
> > database from 195mb to 11GB.
> >
> > When I run it the disks seem to be doing the work but
> > Enterprise manager looks like it has stopped responding.
> > Does this process take very long ' It is running now for
> > over 1hr.
> >
> > eric
>

Expand Collapse All Detail Groupings

Hello there,
I saw another post similar to this one on 15 June, but the lucky
recipient received a RDL file with the answer and I'm hoping someone
can help me with basically the same problem.
The problem is that I would like to add a 'Toggle All' text box to a
report which toggles the state of all the detail groupings on the
report while the individual detail groupings can still be expanded and
collapsed using their '+' markers.
What I have found so far is that I can only use one report item in the
toggle property for the detail grouping, but would like to use 2.
Passing a parameter or having the user set one is not an option in
this case :-(
Any help would be very much appreciated.
Thanks in advance
MichaelWith SP1, you could create a hidden "ToggleAll" parameter that the user
doesn't get to see. You could then add a link in the report to call back to
the same report with the ToggleAll value set or unset.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Brandt" <mandl@.bigpond.com> wrote in message
news:300b952a.0407082123.61f04004@.posting.google.com...
> Hello there,
> I saw another post similar to this one on 15 June, but the lucky
> recipient received a RDL file with the answer and I'm hoping someone
> can help me with basically the same problem.
> The problem is that I would like to add a 'Toggle All' text box to a
> report which toggles the state of all the detail groupings on the
> report while the individual detail groupings can still be expanded and
> collapsed using their '+' markers.
> What I have found so far is that I can only use one report item in the
> toggle property for the detail grouping, but would like to use 2.
> Passing a parameter or having the user set one is not an option in
> this case :-(
> Any help would be very much appreciated.
> Thanks in advance
> Michael

Expand and Contract Groups

Hi,
Does anyone know how to give a group expand/contract ability? I have a ton
of data and dont want to display all the goupings detail unless the user
expands the group. Drills down.
It must be possible?
Kind Regards
WarrenSolved myself :-)
1) Right click row within group (The row/s you want to be toggled)
2) Select Edit Group
3) Select Visibility Tab
4) Check "Visibility can be toggled by another report item"
5) Select Item that toggles view
6) Hit OK
7) Voila!
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23CGGoe3sFHA.3404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Does anyone know how to give a group expand/contract ability? I have a
ton
> of data and dont want to display all the goupings detail unless the user
> expands the group. Drills down.
> It must be possible?
>
> Kind Regards
> Warren
>

Expand and collapse

My users want to be able to view a report in html and have the rows
expand and collapse on certain information. However they want to be
able to view the same report in PDF mode and be able to view all of the
drill down rows.
So my question is this is there a way to have a master expand and
collapse button so that the users would click it to make all of the
rows visible before exporting to PDF of do I need to create a second
report that does not collapse the rows and just have it linked to that
in PDF format?There is a way to have a "master expand all" button:
Assuming a table control is used:
1) Add a top group to your table and set expression to "=Nothing" and
check the include group header checkbox
2) Type in the top left textbox, we'll call it TxtAll, in the group
header for the new group "Expand All" (You could also leave it blank so
it doesn't look awkward on exported report)
3) Insert a new row under your details row in the table and duplicate
all of the fields exactly
4) For second detail tablerow, set Visibility-->Hidden to True and set
ToggleItem to txtAll
5) For all the textboxes in second detail row, set Visibility-->Hidden
to True and set ToggleItem to txtAll
6) For first detail tablerow, set Visibility-->Hidden to True and set
ToggleItem to txtAll
7) For all of the textboxes in the first detail row, set
Visibility-->Hidden to True and set ToggleItem to whatever textbox your
current report is using to toggle
Now the user can drill-down to any detail row or get all rows expanded
by clicking on the Expand All

Expand All\Collapse All

I have a table where i used almost 7 groups with toggle on each level ...
so by default only one row shows up with a + - user clicks on it 2nd row
shows up like that 7 groups and then they can c the rows.
But is there anyway that i can use an option of Expand All in one click ?
like have a button next to the table and when the user clicks on it - all the
rows will be expanded without the user clicking so many times to c the final
result '
thanksRP,
Use a parameter.
In the Visibility tab of the groups properties, make the Visible
property and expression based around a parameter.
Search this newsgroup for more detail. It's a common request.
Chris
RP wrote:
> I have a table where i used almost 7 groups with toggle on each level
> ... so by default only one row shows up with a + - user clicks on
> it 2nd row shows up like that 7 groups and then they can c the rows.
> But is there anyway that i can use an option of Expand All in one
> click ? like have a button next to the table and when the user
> clicks on it - all the rows will be expanded without the user
> clicking so many times to c the final result '
> thanks|||Thanks for the reply chris, I searched the newsgroup - but the ?'s were
mostly on individaul levels.
My problem is once, the user click the Expand all button, i want all the
levels in all the groups to be expanded.
ANyways i will try the parameter.
Thanks
"Chris McGuigan" wrote:
> RP,
> Use a parameter.
> In the Visibility tab of the groups properties, make the Visible
> property and expression based around a parameter.
> Search this newsgroup for more detail. It's a common request.
> Chris
>
> RP wrote:
> > I have a table where i used almost 7 groups with toggle on each level
> > ... so by default only one row shows up with a + - user clicks on
> > it 2nd row shows up like that 7 groups and then they can c the rows.
> > But is there anyway that i can use an option of Expand All in one
> > click ? like have a button next to the table and when the user
> > clicks on it - all the rows will be expanded without the user
> > clicking so many times to c the final result '
> >
> > thanks
>|||RP,
If you set the property mentioned in the same way for each level, you
should get what you've asked for.
You could set them to toggle from a heading cell also with the toggle
item property.
Chris
RP wrote:
> Thanks for the reply chris, I searched the newsgroup - but the ?'s
> were mostly on individaul levels.
> My problem is once, the user click the Expand all button, i want all
> the levels in all the groups to be expanded.
> ANyways i will try the parameter.
> Thanks
>
> "Chris McGuigan" wrote:
> > RP,
> > Use a parameter.
> > In the Visibility tab of the groups properties, make the Visible
> > property and expression based around a parameter.
> >
> > Search this newsgroup for more detail. It's a common request.
> >
> > Chris
> >
> >
> > RP wrote:
> >
> > > I have a table where i used almost 7 groups with toggle on each
> > > level ... so by default only one row shows up with a + - user
> > > clicks on it 2nd row shows up like that 7 groups and then they
> > > can c the rows. But is there anyway that i can use an option of
> > > Expand All in one click ? like have a button next to the table
> > > and when the user clicks on it - all the rows will be expanded
> > > without the user clicking so many times to c the final result '
> > >
> > > thanks
> >
> >

Expand All/Collapse All Toggle

I have implemented all of the suggestions to create an Expand All/Collapse All toggle on a couple of my reports that have Drilldown using a parameter to control it. But, I have an issue with figuring out how to create a link or button on the reports that can be clicked to set the parameter value appropriately? When I render reports in my application I use the RS2005 ReportViewer control in VS2005, but I do not show the parameter bar in the viewer at all and don't want to.

Is there a way to set the value of the parameter within the Body of the report and then cause the report to render again with that new value?

Thanks,

Steve

I figured it out on my own. I just added the textboxes to the report and set the Action on the textbox to link back to itself and change the ExpandAllToggle parameter to the other value.

Expand All/Collapse All feature

1. Is there a way to Expand All nodes or Collapse All Nodes in a drill-down
report designed with RS ? I find it annoying to click every + sign to open
all nodes. Maybe, there is an easier way that I do not know.
2. When the same drill-down report is partially expanded and I export the
report as-it-is to Excel, the exported file has only the partial
data.....only the data that was expanded. The other data is never exported.
What can I do to resolve this ?
Please respond.You can add logic to the Visibility property of each collapable item on the
report and link it to a report parameter. This solution would require a
round trip to the server each time. Others might have additional
suggestions...
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DC" <dc@.discussions.microsoft.com> wrote in message
news:eAo4xY91EHA.1192@.tk2msftngp13.phx.gbl...
> 1. Is there a way to Expand All nodes or Collapse All Nodes in a
> drill-down
> report designed with RS ? I find it annoying to click every + sign to open
> all nodes. Maybe, there is an easier way that I do not know.
> 2. When the same drill-down report is partially expanded and I export the
> report as-it-is to Excel, the exported file has only the partial
> data.....only the data that was expanded. The other data is never
> exported.
> What can I do to resolve this ?
> Please respond.
>|||I have not received any response on this yet. Can somebody help ?
"DC" <dc@.discussions.microsoft.com> wrote in message
news:eAo4xY91EHA.1192@.tk2msftngp13.phx.gbl...
> 1. Is there a way to Expand All nodes or Collapse All Nodes in a
drill-down
> report designed with RS ? I find it annoying to click every + sign to open
> all nodes. Maybe, there is an easier way that I do not know.
> 2. When the same drill-down report is partially expanded and I export the
> report as-it-is to Excel, the exported file has only the partial
> data.....only the data that was expanded. The other data is never
exported.
> What can I do to resolve this ?
> Please respond.
>|||Hmmmm... did you miss my post? There is also an answer to your repost.
Here was my suggestion:
You can add logic to the Visibility property of each collapable item on the
report and link it to a report parameter. This solution would require a
round trip to the server each time. Others might have additional
suggestions...
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DC" <dc@.discussions.microsoft.com> wrote in message
news:O6td5kI2EHA.2112@.TK2MSFTNGP15.phx.gbl...
>I have not received any response on this yet. Can somebody help ?
> "DC" <dc@.discussions.microsoft.com> wrote in message
> news:eAo4xY91EHA.1192@.tk2msftngp13.phx.gbl...
>> 1. Is there a way to Expand All nodes or Collapse All Nodes in a
> drill-down
>> report designed with RS ? I find it annoying to click every + sign to
>> open
>> all nodes. Maybe, there is an easier way that I do not know.
>> 2. When the same drill-down report is partially expanded and I export
>> the
>> report as-it-is to Excel, the exported file has only the partial
>> data.....only the data that was expanded. The other data is never
> exported.
>> What can I do to resolve this ?
>> Please respond.
>>
>|||Jeff, I think your solution is a good one but I have a question for you.
What do you mean that a round trip would be required each time?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:edWDORJ2EHA.1124@.tk2msftngp13.phx.gbl...
> Hmmmm... did you miss my post? There is also an answer to your repost.
> Here was my suggestion:
> You can add logic to the Visibility property of each collapable item on
the
> report and link it to a report parameter. This solution would require a
> round trip to the server each time. Others might have additional
> suggestions...
> Cheers,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "DC" <dc@.discussions.microsoft.com> wrote in message
> news:O6td5kI2EHA.2112@.TK2MSFTNGP15.phx.gbl...
> >I have not received any response on this yet. Can somebody help ?
> >
> > "DC" <dc@.discussions.microsoft.com> wrote in message
> > news:eAo4xY91EHA.1192@.tk2msftngp13.phx.gbl...
> >> 1. Is there a way to Expand All nodes or Collapse All Nodes in a
> > drill-down
> >> report designed with RS ? I find it annoying to click every + sign to
> >> open
> >> all nodes. Maybe, there is an easier way that I do not know.
> >>
> >> 2. When the same drill-down report is partially expanded and I export
> >> the
> >> report as-it-is to Excel, the exported file has only the partial
> >> data.....only the data that was expanded. The other data is never
> > exported.
> >> What can I do to resolve this ?
> >>
> >> Please respond.
> >>
> >>
> >
> >
>|||If visibility was tied to a report parameter, it would require a postback or
click on a link to change the parameter value.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uezBCzJ2EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Jeff, I think your solution is a good one but I have a question for you.
> What do you mean that a round trip would be required each time?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:edWDORJ2EHA.1124@.tk2msftngp13.phx.gbl...
>> Hmmmm... did you miss my post? There is also an answer to your repost.
>> Here was my suggestion:
>> You can add logic to the Visibility property of each collapable item on
> the
>> report and link it to a report parameter. This solution would require a
>> round trip to the server each time. Others might have additional
>> suggestions...
>> Cheers,
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "DC" <dc@.discussions.microsoft.com> wrote in message
>> news:O6td5kI2EHA.2112@.TK2MSFTNGP15.phx.gbl...
>> >I have not received any response on this yet. Can somebody help ?
>> >
>> > "DC" <dc@.discussions.microsoft.com> wrote in message
>> > news:eAo4xY91EHA.1192@.tk2msftngp13.phx.gbl...
>> >> 1. Is there a way to Expand All nodes or Collapse All Nodes in a
>> > drill-down
>> >> report designed with RS ? I find it annoying to click every + sign to
>> >> open
>> >> all nodes. Maybe, there is an easier way that I do not know.
>> >>
>> >> 2. When the same drill-down report is partially expanded and I export
>> >> the
>> >> report as-it-is to Excel, the exported file has only the partial
>> >> data.....only the data that was expanded. The other data is never
>> > exported.
>> >> What can I do to resolve this ?
>> >>
>> >> Please respond.
>> >>
>> >>
>> >
>> >
>>
>

Expand All/Collapse All

Is there a way to add an interactive button to a rendered report that would
expand all of the detail and clicking it again would collapse all of the
detail. I'm a newbie at Reporting Services. I thought this would be nice to
have since i could essentially have detail and summary reports in one this
way. Any help would be appreciated.
Thanks,
JamesInsert a new row in your table and add a group expression. That will give
you the subtotalling/totalling appearance you want.
"JamesB" wrote:
> Is there a way to add an interactive button to a rendered report that would
> expand all of the detail and clicking it again would collapse all of the
> detail. I'm a newbie at Reporting Services. I thought this would be nice to
> have since i could essentially have detail and summary reports in one this
> way. Any help would be appreciated.
> Thanks,
> James

Expand all groupings at once

I have a grouped report and I want to be able to expand all of the groups at
once instead of individually. Is this possible?Hi Willy,
How about setting all everything to toggle on the first group? When
that group is expanded everything will show.
+ GROUP 1
GROUP 2 - hidden = true, toggle=group1
GROUP 3 - hidden=true, toggle=group 1
DETAILS - hidden=true, toggle=group 1
Michelle

Expand All Collapse All

I'm using SSRS 2005 and I can create report that use the enable drill down
function fine, and when the report is displayed the user can click on the +
signs to expand a group. I would like to add the option for the user to be
able to Expand All or Collapse all. Currently they have to click on every +
sign to Expand All. I have not found out how to do this in SSRS. Any help
with this would be greatly appreciated.
Thanks.
--
ChrisYou can have expand all by setting the toggle item to a level above the
parent group, or perhaps to the lable at the top of the tabel.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Chris" wrote:
> I'm using SSRS 2005 and I can create report that use the enable drill down
> function fine, and when the report is displayed the user can click on the +
> signs to expand a group. I would like to add the option for the user to be
> able to Expand All or Collapse all. Currently they have to click on every +
> sign to Expand All. I have not found out how to do this in SSRS. Any help
> with this would be greatly appreciated.
> Thanks.
> --
> Chris|||Thank you Wayne I will try that.
--
Chris
"Wayne Snyder" wrote:
> You can have expand all by setting the toggle item to a level above the
> parent group, or perhaps to the lable at the top of the tabel.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Chris" wrote:
> > I'm using SSRS 2005 and I can create report that use the enable drill down
> > function fine, and when the report is displayed the user can click on the +
> > signs to expand a group. I would like to add the option for the user to be
> > able to Expand All or Collapse all. Currently they have to click on every +
> > sign to Expand All. I have not found out how to do this in SSRS. Any help
> > with this would be greatly appreciated.
> >
> > Thanks.
> >
> > --
> > Chris|||Thank you very much! It worked just fine.
--
Chris
"Chris" wrote:
> Thank you Wayne I will try that.
> --
> Chris
>
> "Wayne Snyder" wrote:
> > You can have expand all by setting the toggle item to a level above the
> > parent group, or perhaps to the lable at the top of the tabel.
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "Chris" wrote:
> >
> > > I'm using SSRS 2005 and I can create report that use the enable drill down
> > > function fine, and when the report is displayed the user can click on the +
> > > signs to expand a group. I would like to add the option for the user to be
> > > able to Expand All or Collapse all. Currently they have to click on every +
> > > sign to Expand All. I have not found out how to do this in SSRS. Any help
> > > with this would be greatly appreciated.
> > >
> > > Thanks.
> > >
> > > --
> > > Chris

Expand ALL

Is it possible to "Expand ALL" levels of a summarized report? I have a
report that has 3 levels of detail, with about 10 main items, a couple items
under that and 4 or so items each under there, it gets tiresome real quick
to click all the little pluses...
When I export it to Excel 2000 there are three little boxes at the top of
the expand columns (1,2,3) and I can click the level of detail I want to show
by using these buttons... Really cool.
Can that work in Report Services directly?
--
Thanks,
Bob MylesYou could have a boolean report parameter called "Expand All". Then on your
report rows' Visibility/Hidden attribute use the expression:
=Not(Parameters!ExpandAll.Value)
HTH,
Magendo_man
"Bob Myles" wrote:
> Is it possible to "Expand ALL" levels of a summarized report? I have a
> report that has 3 levels of detail, with about 10 main items, a couple items
> under that and 4 or so items each under there, it gets tiresome real quick
> to click all the little pluses...
> When I export it to Excel 2000 there are three little boxes at the top of
> the expand columns (1,2,3) and I can click the level of detail I want to show
> by using these buttons... Really cool.
> Can that work in Report Services directly?
> --
> Thanks,
> Bob Myles|||I can get this working for one level of grouping, but for two it does not
seem to. I can get level one expanded, but then level two refuses to show
any of its rows.
Is it possible to elaborate on this solution?
Many thanks for your help.
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:4A2D232D-C462-4576-B59A-4DA8A179D6E8@.microsoft.com...
> You could have a boolean report parameter called "Expand All". Then on
> your
> report rows' Visibility/Hidden attribute use the expression:
> =Not(Parameters!ExpandAll.Value)
> HTH,
> Magendo_man
> "Bob Myles" wrote:
>> Is it possible to "Expand ALL" levels of a summarized report? I have a
>> report that has 3 levels of detail, with about 10 main items, a couple
>> items
>> under that and 4 or so items each under there, it gets tiresome real
>> quick
>> to click all the little pluses...
>> When I export it to Excel 2000 there are three little boxes at the top of
>> the expand columns (1,2,3) and I can click the level of detail I want to
>> show
>> by using these buttons... Really cool.
>> Can that work in Report Services directly?
>> --
>> Thanks,
>> Bob Myles|||I tried it on a report I did last week with two levels of grouping and a
detail line. By default the report shows only the top level grouping.
However, with the solution suggested below I was able to get the second level
grouping and the detail to be visible (or not visible).
Are you changing the Visibility/Hidden attribute for two different rows, or
is one a row and another a textbox or a sub-report?
~ Magendo_man
"Chris Pratt" wrote:
> I can get this working for one level of grouping, but for two it does not
> seem to. I can get level one expanded, but then level two refuses to show
> any of its rows.
> Is it possible to elaborate on this solution?
> Many thanks for your help.
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:4A2D232D-C462-4576-B59A-4DA8A179D6E8@.microsoft.com...
> > You could have a boolean report parameter called "Expand All". Then on
> > your
> > report rows' Visibility/Hidden attribute use the expression:
> >
> > =Not(Parameters!ExpandAll.Value)
> >
> > HTH,
> > Magendo_man
> >
> > "Bob Myles" wrote:
> >
> >> Is it possible to "Expand ALL" levels of a summarized report? I have a
> >> report that has 3 levels of detail, with about 10 main items, a couple
> >> items
> >> under that and 4 or so items each under there, it gets tiresome real
> >> quick
> >> to click all the little pluses...
> >>
> >> When I export it to Excel 2000 there are three little boxes at the top of
> >> the expand columns (1,2,3) and I can click the level of detail I want to
> >> show
> >> by using these buttons... Really cool.
> >>
> >> Can that work in Report Services directly?
> >>
> >> --
> >> Thanks,
> >> Bob Myles
>
>

Expand a querry for more function

I have an automated shipping software that reaches into our database and gets customer orders and processes them for UPS tags. The problem I am having is we have to manually break out line items. What I mean iss this, if there is multiples of the same thing on the order the UPS software shows a total wieght for all simular items as if it was one item.

What I am trying to do is to pre process the Order table into individuale lines so the automated feature of my UPS shipping software works.

The table looks something like this,

Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 3, 12, 36
1300, XY32, Junk, 2, 40, 80

What I need to turn it into is"

Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1300, XY32, Junk, 1, 40, 80
1300, XY32, Junk, 1, 40, 80

it would also be nice to add a column to the table to track the total number of packages for each order number, kind of a 1 of 3, 2 of 3 type thing, but that is much easyier than the breack out

any help woulf be greatly apreciated

KellyCan you post the DDL of the table? There's got to me more to it...

Because you do you get..

1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36

From this?

1200, AB324, More Stuff, 1, 12, 36

Doesn't make sense, unless there are other things on the row...

Script the table and post the DDL like..

CREATE TABLE myTable99 (Col1 int, ect...
GO

And sample Data like..

INSERT INTO myTable99 (Col1, col2, ect)
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data

and it'll be easier for us to help..|||There is a lot of extra columns we do not even use as this is a canned ERP package this is from , we are a simple manufacturing company and do not require many of the features that this package provides, I have posted two create statements, first with only the columns that we use, and second with every thing.

the columns we are interested in are,

CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

The entire table is below,

CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pick_seq] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cus_item_no] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[discount_pct] [decimal](5, 2) NULL ,
[request_dt] [int] NOT NULL ,
[qty_bkord] [decimal](13, 4) NULL ,
[qty_return_to_stk] [decimal](13, 4) NULL ,
[bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom_ratio] [decimal](9, 5) NULL ,
[unit_cost] [decimal](13, 6) NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[comm_calc_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comm_pct_or_amt] [decimal](7, 2) NULL ,
[promise_dt] [int] NOT NULL ,
[tax_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stocked_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[controlled_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[select_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[tot_qty_shipped] [decimal](13, 4) NULL ,
[tax_fg_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[orig_price] [decimal](13, 6) NULL ,
[copy_to_bm_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[explode_kit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allocate_dt] [int] NULL ,
[last_post_dt] [int] NULL ,
[post_to_inv_qty] [decimal](13, 4) NULL ,
[posted_to_inv] [decimal](13, 4) NULL ,
[tot_qty_posted] [decimal](13, 4) NULL ,
[qty_allocated] [decimal](13, 4) NULL ,
[components_alloc] [decimal](13, 4) NULL ,
[bin_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost_meth] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ser_lot_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_ftr_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[line_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prod_cat] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_item_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reason_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feature_return] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_inspection] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_from_stk] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_release] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[req_ship_dt] [int] NULL ,
[qty_from_stk] [decimal](13, 4) NULL ,
[user_def_fld_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[picked_dt] [int] NULL ,
[shipped_dt] [int] NULL ,
[billed_dt] [int] NULL ,
[update_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prc_cd_orig_price] [decimal](13, 6) NULL ,
[tax_sched] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_amt] [decimal](14, 2) NULL ,
[qty_bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_no] [smallint] NOT NULL ,
[mfg_method] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[forced_demand] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[conf_pick_dt] [int] NULL ,
[item_release_no] [int] NULL ,
[bin_ser_lot_comp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offset_used_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ecs_space] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sfc_order_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_cost] [decimal](14, 2) NULL ,
[po_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rma_line_seq_no] [smallint] NULL ,
[vend_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler_0004] [char] (71) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO|||Here is an insert script with some data from our test database, I have only included the data we use, most of the rest is zeros or nulls,

Insert Into [OEORDLIN_SQL]
(ord_type,ord_no,line_seq_no,item_no,loc,item_desc _1,item_desc_2,qty_ordered,
qty_to_ship,unit_price,uom,unit_weight,tot_qty_ord ered,cus_no,A4GLIdentity)

Values (B,00000002,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,.0000,64.420000,EA,26.000000,3.0000, SCHFNE,18301)
(B,00000003,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,5.0000,.0000,128.840000, EA,46.000000,6.0000,PYEFAR,20247)
(O,00056941,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,1.0000,1.0000,46.160000,EA,26.000000,1.0000,M ASCAL,34818)
(O,00056941,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,1.0000,1.0000,92.320000,EA,49.000000,1.0000,M ASCAL,34819)
(O,00056941,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,5.0000,5.0000,88.220000,EA,49.000000,5.000 0,MASCAL,34820)
(O,00056942,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,RAIEGA,34821)
(O,00056943,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,87.720000,EA,46.000000,1.0000 ,CORVGA,34823)
(O,00056944,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,MAR6TX,34824)
(O,00056945,1,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,3.0000,3.0000,92.320000,EA,49.000000,3.0000,C TGFAL,34825)
(O,00056945,2,FPFF44,WH,Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CTGFAL,34826)
(O,00056946,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,WYLKAL,34827)
(O,00056946,2,FPHD8TT,WH,Disenfectant 8, Tea Tree Oil,1.0000,1.0000,12.000000,EA,.000000,1.0000,WYLK AL,34828)
(O,00056947,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,ZICBFL,34829)
(O,00056948,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,43.860000, EA,23.000000,1.0000,YATRAL,348300
(O,00056949,1,FPFF44,WH,Farrier's Formula 44 lb,NULL, 1.0000,1.0000,132.750000, EA,49.000000,1.0000,BERAIL,348310
(O,00056950,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,10.0000,10.0000,87.720000,EA,46.000000,10.0 000,MODFCA,348320
(O,00056951,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,2.0000,2.0000,46.160000,EA,26.000000,2.0000,S TAAOR,348330
(O,00056952,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,3.0000,87.720000,EA,46.000000,3.0000 ,EMCPNY,348340
(O,00056952,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,2.0000,2.0000,92.320000,EA,49.000000,2.0000,E MCPNY,348350
(O,00056952,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,4.0000,4.0000,88.220000,EA,49.000000,4.000 0,EMCPNY,34836)
(O,00056953,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,BARBNY,34837)
(O,00056955,1,FPFF44,HSP, Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CASAVT,34839)
(O,00056956,1,FPFF11CS,HSP, 2 PK 11 LB FARRIERS FORMULA, NULL, 2.0000,2.0000,46.160000,EA,26.000000,2.0000,HORNMA , 34840)
(O,00056956,2,FPFF22,HSP, Farrier's Formula 22 lb,NULL,5.0000,5.0000,46.160000,EA,25.000000,5.000 0,HORNMA,34841)
(O,00056956,3,FPFF44,HSP, Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,HORNMA,34842)
Go

Expand

Hi,
I have a report with a matrix. The width of the body is 10in and the reportmargings are .5in. Above the matrix is a textbox with some information regarding the report. It as a width of 10in. The matrix has three groups above the columns where I put some daterelated fields in so I can simulate a date hierarchy. The hierarchy consists of the fields Year, Quarter and Month.
When I preview the report and set the Date level to Month the width of the matrix becomes greater than the width of the page. But the width of the textbox stays on 10in. Can this be dynamically adjusted to the width of the report?
QThere's no general "Grow to fit container" behavior in the current version.
But you can take advantage of the fact that table cells do have this feature
via the following sleazy hack:
1. Add a table to your report with one column, two header rows, no
groupings and no detail rows.
2. Put the textbox in the first cell
3. Put the matrix in the second cell
When the matrix expands, it will force the column to expand.
And the contents of the cells (including the textbox) must expand with it.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:3B04CD11-5C12-4E71-BA95-97E185784A31@.microsoft.com...
> Hi,
> I have a report with a matrix. The width of the body is 10in and the
reportmargings are .5in. Above the matrix is a textbox with some information
regarding the report. It as a width of 10in. The matrix has three groups
above the columns where I put some daterelated fields in so I can simulate a
date hierarchy. The hierarchy consists of the fields Year, Quarter and
Month.
> When I preview the report and set the Date level to Month the width of the
matrix becomes greater than the width of the page. But the width of the
textbox stays on 10in. Can this be dynamically adjusted to the width of the
report?
> Q
>|||Brilliant!...;-)
Q
"Chris Hays [MSFT]" wrote:
> There's no general "Grow to fit container" behavior in the current version.
> But you can take advantage of the fact that table cells do have this feature
> via the following sleazy hack:
> 1. Add a table to your report with one column, two header rows, no
> groupings and no detail rows.
> 2. Put the textbox in the first cell
> 3. Put the matrix in the second cell
> When the matrix expands, it will force the column to expand.
> And the contents of the cells (including the textbox) must expand with it.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:3B04CD11-5C12-4E71-BA95-97E185784A31@.microsoft.com...
> > Hi,
> >
> > I have a report with a matrix. The width of the body is 10in and the
> reportmargings are .5in. Above the matrix is a textbox with some information
> regarding the report. It as a width of 10in. The matrix has three groups
> above the columns where I put some daterelated fields in so I can simulate a
> date hierarchy. The hierarchy consists of the fields Year, Quarter and
> Month.
> >
> > When I preview the report and set the Date level to Month the width of the
> matrix becomes greater than the width of the page. But the width of the
> textbox stays on 10in. Can this be dynamically adjusted to the width of the
> report?
> >
> > Q
> >
> >
>
>

Exp0001 in subreports stored procedure

Hi,

I have a report file that was made with Crystal 8.5.
The report has been working for years.
I open VisualStudio .NET 2005 and open the report file.
Without changing anything, I save the report.
Visual Studio asks if I want to convert to the version of Crystal that comes with VS .NET 2005.
I accept and the report is converted.
Then I chose Verify Database and it connects to the database, selects the Microsoft SQL Server stored procedure that provides the data for the report, etc.
This stored procedure has a simple select statement, and the first column it returns is "Code".
But the Verify Database ends up showing a field named "Exp0001" that it cannot map to any of the report's fields.
This has happened to me before with another report converted from Crystal 8.5

Any hints or help on why is this happening or how to correct it will be greatly appreciated.

Thanks in advance,

RichardIn your query if you used sum or any other aggretate functions, you need to specify the alias name

Select max(code) as code from table|||Thanks for the hint. In this case, the query does not use aggregates.
The query is something like:

SELECT Product_Code AS 'Code', Product_Description AS 'Description' FROM ORDER_LINE WHERE Order_ID = @.Order_ID

The column named "Code" appears as "Exp0001" even though this select statement is simple enough... Any help will be appreciated.

Thanks in advance,

Richard|||Remove single quotes in alias name and try

SELECT Product_Code AS Code, Product_Description AS Description FROM ORDER_LINE WHERE Order_ID = @.Order_ID

Exotix Unpivot Transformation

I totally give up.

I need to import an csv file with system monitor performance counters into a sql server 2005 database

example csv format:

"(PDH-CSV 4.0) (W. Europe Daylight Time)(-120)","Server1\Memory\% Commited Bytes In Use", )","Server2\Memory\%Commited Bytes In Use"
"07/18/2006 10:08:57.295","24.701095057336488","30.701095077776488"

and I want it to transform it into:

Time ServerName Memory\% Commited Bytes In Use
07/18/2006 10:08:57.295 Server1 24.701095057336488
07/18/2006 10:08:57.295 Server2 30.701095077776488

I have two problems:

I need to trim the servername from the column values

I need to insert an extra column ServerName and put those server names in it

I tried Unpivot and Fuzzy grouping in SSIS but it's al too fuzzy for me...

Any pointers someone?
Many thanks!

Regards,

Mop

I presume that the number of servers that can be listed is unknown at design-time, is that correct?

If so then you will need to do this with an asynchronous script component.For each incoming row create a row in the output for each server in that row. You should be able to parse out the server names fairly easily using .Net code.

-Jamie

|||

Hello Jamie

You're right, I don't know the number of servers at design-time. I will look what's possible with the asyncronous script component. In the meantime someone also told me it could be accouplished using XML.

Thank you!

Regards,

Mop

|||

mop wrote:

Hello Jamie
You're right, I don't know the number of servers at design-time. I will look what's possible with the asyncronous script component. In the meantime someone also told me it could be accouplished using XML.

does this other person mean to store the source differently?

Well yes, of course if you restructure the source file then you'll be able to do it without your "exotic unpivot" within script. The question of whether you store that restructured source file as XML, CSV, fixed-length or something else entirely is completely and utterly irrelevant.

-Jamie

Exotic Unpivot Transformation

I totally give up.

I need to import an csv file with system monitor performance counters into a sql server 2005 database

example csv format:

and I want it to transform it into:

Time ServerName Memory\% Commited Bytes In Use
07/18/2006 10:08:57.295 Server1 24.701095057336488
07/18/2006 10:08:57.295 Server2 30.701095077776488

I have two problems:

I need to trim the servername from the column values

I need to insert an extra column ServerName and put those server names in it

I tried Unpivot and Fuzzy grouping in SSIS but it's al too fuzzy for me...

Any pointers someone?
Many thanks!

Regards,

Mop

I presume that the number of servers that can be listed is unknown at design-time, is that correct?

-Jamie

|||

Hello Jamie

Thank you!

Regards,

Mop

|||

mop wrote:

Hello Jamie
You're right, I don't know the number of servers at design-time. I will look what's possible with the asyncronous script component. In the meantime someone also told me it could be accouplished using XML.

does this other person mean to store the source differently?

-Jamie

Exotic SQL Question

Table 1:
id1 name
10 - Monday
20 - Tuesday
30 - Wed..
40 - etc

Table2:
id2 - name
5 - blue
25 - red
33 - yellow
77- gree

How do I use SQL to join the tables so that the "id1" columns join on
the largest "id2" value smaller than "id1"

Result:
Monday - blue
Tuesday - blue
Wed - red
etc

Thanks
JFO*UNTESTED*

select ...
from table1 join table2 on table1.id1 > table2.id2
and not exists(select 1 from table2 t2 where table1.id1 > t2.id2 and
t2.id2>table2.id2)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.

CREATE TABLE Colors
(color_nbr INTEGER NOT NULL PRIMARY KEY,
color_name CHAR(10) NOT NULL);

CREATE TABLE Weekdays
(zeller_nbr INTEGER NOT NULL PRIMARY KEY,
weekday_name CHAR(10) NOT NULL);

>> How do I use SQL to join the tables so that the color_nbr columns join on the largest zeller_nbr value smaller than color_nbr <<

SELECT C1.color_nbr, W1.zeller_nbr, C1.color_name, W1.weekday_name
FROM Colors AS C1, Weekdays AS W1
WHERE zeller_number
= (SELECT MAX(zeller_nbr)
FROM Weekdays AS W2
WHERE W2.zeller_nbr < C1.color_nbr
AND W2.zeller_nbr = W1.zeller_nbr)

This sounds like the old joke about the teacher and the students:

teacher: "Mary, what is 6 times 7?"
student: "Red?"
teacher: "Billy, what is 6 times 7?"
student: "Thursday!"
teacher: "Johnny, what is 6 times 7?"
student: "42!"
teacher: "Johnny, tell the class how you go that answer."
student: "I divided Red by Thursday!"

untested.|||Thanks a lot, dude! will try that solution. The thing is, the two
tables that we want to join are quite big - one is 1.7 million entries,
another is 3.2 million. (working on a thesis in Finance)
I have been introduced to SQL less than a week ago, so can be quite a
lamer here. These are the DDL's for 2 tables
CREATE TABLE "public"."orders" (
"order_id" SERIAL,
"seq_id" INTEGER,
"datetime" TIMESTAMP(0) WITHOUT TIME ZONE,
"by_sell" INTEGER,
"price" DOUBLE PRECISION,
"volume" INTEGER,
CONSTRAINT "orders_pkey" PRIMARY KEY("order_id")
) WITHOUT OIDS;

Second:
CREATE TABLE "public"."transactions" (
"transaction_id" INTEGER NOT NULL,
"date" DATE,
"company_name" VARCHAR,
"trade_type" VARCHAR,
"volume" INTEGER,
"price" DOUBLE PRECISION,
"time" TIMESTAMP(0) WITHOUT TIME ZONE,
CONSTRAINT "transactions_pkey" PRIMARY KEY("transaction_id")
) WITHOUT OIDS;

Using posgres 8.1 Will wait for a friend of mine who is helping us
(the author of the question) to try the solution

But basically, we want to join orders with transactions, with field
price in transactions looking up PRECEEDING price in orders|||On 11 May 2006 10:20:06 -0700, --CELKO-- wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, data types, etc. in
>your schema are.
>CREATE TABLE Colors
>(color_nbr INTEGER NOT NULL PRIMARY KEY,
> color_name CHAR(10) NOT NULL);
>CREATE TABLE Weekdays
>(zeller_nbr INTEGER NOT NULL PRIMARY KEY,
> weekday_name CHAR(10) NOT NULL);

Hi Joe,

Are you seriously trying to teel me that after spending months
researching the data, this is your schema?

Both color names and weekday names can vary in length. Clearly, char is
a bad choice of datatype for those columns - use varchar (or character
varying, if you prefer ANSI-speak).

The longest English weekday name is wednesday, which has 9 characters.
Do you really want to end with a day like "wednessday" in your table?
Because if you allow 10 characters, you will!

Colornames can far exceed the lousy 10 characters you allow. Just take a
look on Wikipedia: http://en.wikipedia.org/wiki/List_of_colors. For
instance "International Klein Blue" has 24 characters. I didn't count
the length of all names on that page, nor will I assume that it's a
complete list. I'd allow at least 30 characters. Maybe even 50. (Oh
wait, I'll change it to 49 or 51 to prevent people thinking I'm using
the Access defaults...)

Oh, and please stop using silly column names such as zeller_nbr. We
don't design database schemas to show off how many algorithm names we
remember, we should strive for maintainability. Anything wrong with
weekday_nbr?

>teacher: "Mary, what is 6 times 7?"
>student: "Red?"
>teacher: "Billy, what is 6 times 7?"
>student: "Thursday!"
>teacher: "Johnny, what is 6 times 7?"
>student: "42!"
>teacher: "Johnny, tell the class how you go that answer."
>student: "I divided Red by Thursday!"

You're obviously wrong (again). Johnny built a supercomputer and let it
to calculate for a few billion years.

--
Hugo Kornelis, SQL Server MVP|||Supercomputer......that will do us nicely...Hugo, may I trouble you
for Johnny's e-mail?|||On 11 May 2006 14:24:18 -0700, alunev@.gmail.com wrote:

>Supercomputer......that will do us nicely...Hugo, may I trouble you
>for Johnny's e-mail?

Hi Alunev,

I don't know who "Johnny" is - but even if I did, I wouldn''t pass on
his e-mail to anybody without his own consent.

If you refer to Joe (not John!!) Celko, I believe that the email in his
Usenet headers is his real e-mail address. If you're refering to someone
else, I suggest that you ask him or her instead of me.

--
Hugo Kornelis, SQL Server MVP|||Johnny who has built a supercomputer...mighty serious, you dudes|||Hi There,

You can use somewhat Celko Suggested ,
SELECT C1.color_nbr, W1.zeller_nbr, C1.color_name, W1.weekday_name
FROM Colors AS C1, Weekdays AS W1
WHERE zeller_number
= (SELECT MAX(zeller_nbr)
FROM Weekdays AS W2
WHERE W2.zeller_nbr < C1.color_nbr
AND W2.zeller_nbr = W1.zeller_nbr)

You my try this

Select * From
DayTable AA , ColorTable BB
Where AA.id1>BB.id1
And BB.id1 = ( Select Max(id1) From ColorTable BB1 where BB1.id1 <
AA.id1)

With Warm regards
Jatinder Singh

ExOleDB Permissions problem as linked server

I have Exchange 2000 server and SQL Server 2000 installed on the same
box. I using the ExOLEDB data provider to set up Exchange as a linked
server in SQL Server. A public folder is queried in a stored procedure
using OpenQuery and a SQL Query against the Exchange folder.
This all works fine, as long as the user is an administrator. If a
non-admin attempts to use the stored procedure, the SQL Server returns
the error "Could not create an instance of OLE DB provider
'exoledb.DataSource.1'.". I know that it can (as I can do it as
admin), so it appears to be a permissions problem. What do I need to
set to give regular users access to this data? I've tried playing with
the permissions in ESM with no luck.
Thank you,
ErickIs the procedure using the sp_OACreate? If so non-administrators cannot use
these procedures. This is documented in Books on lIne.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||No, the procedure is using OpenQuery. It basically does the following
SELECT * FROM OpenQuery(Exchange, 'SELECT * FROM SCOPE(''SHALLOW
TRAVERSAL OF "http://nbr-sql.nbr.lan/public/Intranet%20Calendar/"'')
WHERE NOT "urn:schemas:calendar:instancetype" = 1
AND "DAV:contentclass" = ''urn:content-classes:appointment''
ORDER BY Start desc'
)
It appears if the logged in user is a member of the Enterprise Admin
group in active directory, then it will work. However, this is not a
solution that is workable...
An odd thing is that if I add a user to the Enterprise Admin group,
and then remove them, they appear to keep the ability to run the
query. It might simply be a delay until the removal takes effect, I'm
not sure yet. But I'd like to set the permission so it's not as issue.
Thanks,
Erick
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<6EUAwUB7DHA.3496@.cpmsftngx
a07.phx.gbl>...
> Is the procedure using the sp_OACreate? If so non-administrators cannot us
e
> these procedures. This is documented in Books on lIne.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||I'm having the exact same problem with Index Server as a linked server..
any ideas?
thx,
mo
"Erick T" <joikm@.hotmail.com> wrote in message
news:888ecafd.0402042350.13ed45dc@.posting.google.com...
> I have Exchange 2000 server and SQL Server 2000 installed on the same
> box. I using the ExOLEDB data provider to set up Exchange as a linked
> server in SQL Server. A public folder is queried in a stored procedure
> using OpenQuery and a SQL Query against the Exchange folder.
> This all works fine, as long as the user is an administrator. If a
> non-admin attempts to use the stored procedure, the SQL Server returns
> the error "Could not create an instance of OLE DB provider
> 'exoledb.DataSource.1'.". I know that it can (as I can do it as
> admin), so it appears to be a permissions problem. What do I need to
> set to give regular users access to this data? I've tried playing with
> the permissions in ESM with no luck.
> Thank you,
> Erick

exit the package conditionally

I'm inside a "dataflow" and

i have a conditional spliter

one of the condition say "ok"

and the the other says "not ok"

when my flow goes to the "not ok" condition

I want to Abort the entire package execution

I want to make it sure that the package is terminated

and could not triggers the next control in the control flow.

i want to kill/ abort the execution of all my SSIS processes

for the entire package within the current dataflow

The data flow is not the place to give the stop & go to the package execution flow. For that you should use the control flow and put some conditional logic in the precedence constraints.

If you want to abort the package execution because there is at least one row identified as 'not ok'; you could use a execute sql task to run a query (assuming your source is a table, but a simialr approach can be used for files) to check that condition and populated a variable with the result (0/1 r stop/go,etc) then use that variable in the precedence constraints to achieve the desire behavior

|||As Rafael Salas said you can use precedence constraint and there you can specify the

exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as follows:

Exec Sp1

Inserts, updates

Exec Sp2

Inserts, upadtes

Exec Sp3

If error Exit Exec Sp1

Can I end Sp1 if I catch an error in Sp3

No, all modification made by Sp1 and Sp2 won't be rolled back. Because SQL treat the 3 Sps a batch: if there is any exception in the batch during execution, the statements executed before the one that encountered the run-time error are not affected, unless statements are put in one transction. For more information, please take a look at this link:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_04_9i9f.asp?frame=true

exit job within a step

Hi,

I have a job with 6 steps. before I run steps 2 to 6 I check to see if there is data to process. I want to exit the job reporting success without running the other steps. How do I do this?

Thanks

I would add 2 additional steps to your job

Step1: Test if there is any data to load. If no - raise an error.

On Success - carry on

On Failure - go to step 8

Step 2- 7 your original steps, They'll never reach step 8.

Step 8: This will be your "dummy" step to make the load successfull if no data to load. It can be reached only from failure of Step 1.

Onother solution will be to use DTS package , where dataflow can be controlled much better and it can be schedules as well

NB.

|||

Thanks,

This what I have done before. I was looking for a difference way.

EXIT Installation Scripts that have multiple GO statements

Hi All

I'm Scripting various Upgrade scripts that set up roles / users / create databases etc and want to Exit the Script unconditionally if for example the user already exists etc.

I can't Use RETURN because it just jumps to the Code following the next GO statement

There is also some USE [Database] Statements

Is there a way to do this or am I missing something fundamental here ?

GWIs there a way to do this or am I missing something fundamental here ?No.

-PatP|||I think conditional statements are the way to go...

If the username exists, do nothing, else do something...
OR
If the username doesn't exist, do something.|||What if he dumped the monolithic type script and removed the batch separators instead calling a series of individual files in series? Sean posted a script to do this recently and I have a similar one but using sqlcmd.

George - I think the problem is that batch separators are required as otherwise the deferred name resolution causes the script to fail as dependant objects do not exist at compile time. Also, some objects (e.g. functions & procs) can be the only statements in a batch.|||this can be done BUT the script must always be run by either SQLCMD or maybe OSQL. Here is a sampling of the file that you would use;

select count(*)
from sys.databases
go

if exists (select * from sys.tables)
begin
exit
end

go

select count(*)
from sys.databases
go

This will always generate an error in Query Analyzer, but SQLCMD will exit as you desire.

EDIT: Note; when you test this script, you only get the results of the first query. The second query is ignored.|||Folks

Thanks for all your input & Apologies for not getting back to you sooner.

In this instance the new system is still in Dev & I'm releasing full versions into UAT / Testing environments (which comprise 2 new interface databases, Linked Servers & multiple security accounts setup) - hence the monolothic script.

I think I'll have to accept Pat's Answer.

Thanks again

GW

exit from script

I need to exit from a script after checking some conditions . for example I need to check if a store procedure does not exist make it other wise go exit . I try following code but became an error

if exists(select ..... )

create store procedure ...

<<error becomes>>

if exists(select .... ) return

create store procedure .....

<<both command runs>>

Moved to T-SQL forum.

|||

A good way to check before creation is something like this: Note: RETURN in not required.

The CREATE PROCEDURE code will execute after the PROCEDURE has been deleted.

Code Snippet

IF EXISTS
( SELECT name
FROM sys.Procedures
WHERE name = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
( @.Parameters etc )
AS
...

|||

Unfortunately you can't do it here.Create procedure script should not be batched on IF statement.

As per the specification Create Procedure/Function/Trigger/View should be the first statement of your query

The best approach is,

Code Snippet

IF EXISTS (Procedure)

Drop Procedure ...

Create Proc ....

|||

If you use GO on your batch you can't stop it. You can only return / stop the current query (which is enclosed by GO).. When you return or use goto, it will automatically fetch the next script..

So always check the object is available, if available drop it & recreate it.

Exit DTS Package

Is there a way to exit an executed DTS package using Microsoft TSQL without causing an error or does this need to be in VB?The TSQL executes within the package, and thus I don't think there is a way to directly stop the package using TSQL. I suppose you could call system stored procedures to command shell out and stop the service, or even identify the executing process and kill it, but I would certainly never recommend anything like this.

Your best be may be to have the TSQL return an error code or set an environment variable that your DTS package can respond to.|||Okay thanks. I did raiserror but that makes the package a failure which is what I don't want.|||the handling of return codes inside DTS is a different animal from RAISEERROR|||I ended up writing it in VB but thanks guys.

Exit codes for successful install?

Hello everyone,
I'm calling MSDE setup from my installation program with:
setup.exe SecurityMode=sql REBOOT=ReallySuppress instancename="QSA"
sapwd=********** /l*v "C:\Program Files\SGM\MSDE.LOG"
I have seen this call return value 3010 (GetExitCodeProcess) on a W98SE and
a W2000 machine.
1) What does this return value mean (anything specific other than
'success')?
2) Are there more return values that indicate success?
(so that I can distinguish them from failure)
Thanks in advance
Jan
Jan Doggen, QSA Landsmeer, The Netherlands
Please remove the spam blocker from my email address when replying directly
Found the answer myself a few days later
http://msdn.microsoft.com/archive/de...us/dnarsql7/ht
ml/deploybus_depdbsol.asp
Exit Code Description
0 Successful installation (no reboot required)
3010 Successful installation (reboot required)
-1 Failed installation (logic or configuration error)
Other Failed installation (Win32 error code-see Endnote 6)
Jan
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> schreef in bericht
news:OLNf#weMEHA.1348@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'm calling MSDE setup from my installation program with:
> setup.exe SecurityMode=sql REBOOT=ReallySuppress instancename="QSA"
> sapwd=********** /l*v "C:\Program Files\SGM\MSDE.LOG"
> I have seen this call return value 3010 (GetExitCodeProcess) on a W98SE
and
> a W2000 machine.
> 1) What does this return value mean (anything specific other than
> 'success')?
> 2) Are there more return values that indicate success?
> (so that I can distinguish them from failure)
> Thanks in advance
> Jan
>
> --
> ----
-
> Jan Doggen, QSA Landsmeer, The Netherlands
> Please remove the spam blocker from my email address when replying
directly
>

existsnode()

HI,
I have been using Oracle 10g release 2. but my current project uses SQL
SERVER 2000.
Is there any functions to tell if a node exists in the xml.In oracle we use
xmltype.existsnode(Xpathexpression).
It would be really helpful if you could reply... i am new to this...
ThanksYou can use the exist() method|||Hello markc600@.hotmail.com,

> You can use the exist() method
Umm, no, not in SQL 2000 -- not XML datatype there, need 2005 for that.
Toby, is SQL 2005 and option for you?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||...You can use exist() method on XML type only in SQL Server 2005.
For SQL Server 2000 look at OPENXML... or upgrading to SQL Server 2005.
Best regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<markc600@.hotmail.com> wrote in message
news:1145891388.305650.227350@.i39g2000cwa.googlegroups.com...
> You can use the exist() method
>|||
Apologies for that. Fortunately someones paying attention (not me!)
Regards Mark

exists: the absence of data

Can someone please tell me how to check if a record does not exist (as a secondary key) in one table when it exists in another as a primary key. The actual value of the record is not known ahead of time. I am dealing in sybase t-sql if that makes a difference. Also, can anyone recommend a good tutorial on t-sql.

ThanksYou can use the NOT EXISTS condition in your WHERE clause to find rows in the parent table that have no related rows in the child table.

There are lots of good books on Transact-SQL, as well as SQL in general. If all you need is a good introduction, the SQL for Dummies (http://www.dummies.com/WileyCDA/DummiesTitle/productCd-0764540750.html) will probably do the job.

-PatP|||Thanks Pat. Works exactly how I wanted it. Thanks for the book rec. too.

Adam

Originally posted by Pat Phelan
You can use the NOT EXISTS condition in your WHERE clause to find rows in the parent table that have no related rows in the child table.

There are lots of good books on Transact-SQL, as well as SQL in general. If all you need is a good introduction, the SQL for Dummies (http://www.dummies.com/WileyCDA/DummiesTitle/productCd-0764540750.html) will probably do the job.

-PatP

EXISTS with EXEC

This works:
IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk)
But since I may need to build the Sql statement I tried something like this,
which did NOT work,
IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk'))
Is there a way to go around this?
Evan>> Is there a way to go around this?
The EXISTS clause in SQL can have only a SELECT statement. Can you elaborate
on what your requirements are? Why do you want to do something like this?
Alternatively you can use the entire IF clause in your EXEC like:
EXEC ( 'IF NOT EXISTS ( SELECT ... ) ... ELSE .. ' )
Anith|||Evan Camilleri (e70mt@.yahoo.co.uk.nospam) writes:
> This works:
> IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @.qci_pk)
> But since I may need to build the Sql statement I tried something like
> this, which did NOT work,
> IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk =
> @.qci_pk'))
> Is there a way to go around this?
One way is to use sp_executesql:
SELECT @.sql = 'SELECT @.x = CASE WHEN EXISTS (SELECT ...) THEN 1 ELSE 0 END'
EXEC sp_executesql @.sql, N'@.x bit OUTPUT', @.exists OUTPUT
IF @.exists = 0
..
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

EXISTS Vs. NOT EXISTS

Which query is faster? assuming condition1 results in table scan (say 10000
records).
IF EXISTS(condition1)
BEGIN
operation1
END
ELSE
BEGIN
operation2
END
IF NOT EXISTS(condition1)
BEGIN
operation2
END
ELSE
BEGIN
operation1
ENDAm Mon, 24 Apr 2006 08:55:42 -0400 schrieb Justin:

> Which query is faster? assuming condition1 results in table scan (say 1000
0
> records).
> IF EXISTS(condition1)
> BEGIN
> operation1
> END
> ELSE
> BEGIN
> operation2
> END
>
> IF NOT EXISTS(condition1)
> BEGIN
> operation2
> END
> ELSE
> BEGIN
> operation1
> END
There will be no difference.
For example: your record has a field called number, this field is set
randomly. Now you are looking for a record with number = 1000.
Let's say, this is the fifth record from top.
if exists(select * from table where number = 1000) can nothing say on
record 1, 2, 3 or 4, but record 5 has this number, so you get TRUE as
answer.
if not exists(select * from table where number = 1000) can nothing say on
record 1, 2, 3 or 4, but record 5 has this number, so you get FALSE as
answer.
bye, Helmut|||If it can't use an index and does a full table scan, it is going to search
the table until it finds a record that meets the criteria of your exists.
Once it finds a record, it knows two things:
Exists = True
Not Exists = False
If no record exists, it will search every row trying to find one that meets
the criteria. When it has searched the entire table and found nothing, it
knows two things:
Exists = False
Not Exists = True
Either way it has an answer to your question with the same number of
lookups. At this point the choice is one of coding style and readability.
Personally I try to avoid negative criteria and go with the positive, simple
because negatives require more thought to understand whats happening. First
you ask a question, then you reverse the answer. Easy for a computer,
tougher for humans.
Now, it might be worth trying both ways and checking the execution plans,
just in case the optimizer handles them different. Since they are logically
identical as far as how much work is required to get an answer, the
optimizer should treat them as such, but the optimizer never ceases to amaze
me in some of the decisions that it makes.
"Justin" <jyang@.ioutsource.info> wrote in message
news:u52Dj55ZGHA.4564@.TK2MSFTNGP03.phx.gbl...
> Which query is faster? assuming condition1 results in table scan (say
10000
> records).
> IF EXISTS(condition1)
> BEGIN
> operation1
> END
> ELSE
> BEGIN
> operation2
> END
>
> IF NOT EXISTS(condition1)
> BEGIN
> operation2
> END
> ELSE
> BEGIN
> operation1
> END
>|||On Mon, 24 Apr 2006 08:55:42 -0400, Justin wrote:

>Which query is faster? assuming condition1 results in table scan (say 10000
>records).
>IF EXISTS(condition1)
>BEGIN
> operation1
>END
>ELSE
>BEGIN
> operation2
>END
>
>IF NOT EXISTS(condition1)
>BEGIN
> operation2
>END
>ELSE
>BEGIN
> operation1
>END
>
Hi Justin,
No difference, as already explained by Helmut and Jim.
But depending on what exactly the condition and the operations are, it
might be possible to get a faster version by using a different (yet
equivalent) logic.
As an example - the following common construct:
IF EXISTS (SELECT ...) -- Search specific row
BEGIN
UPDATE ... -- Update of row just found
END
ELSE
BEGIN
INSERT ... -- Insert of row that was searched for
END
Can be rewritten as
UPDATE ... -- Update of (maybe nonexistant) row
IF @.@.ROWCOUNT = 0
BEGIN
INSERT ... -- Didn't exist - insert it
END
Or as (same idea; logic reversed)
INSERT ...
WHERE NOT EXISTS (SELECT ...)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE ...
END
Even if these examples don't match your requirement, you might be able
to find a parallel. Or, if you post more details, we can help you find
more efficient ways to get the job done. See www.aspfaq.com/5006 for the
information to provide in order to get the best replies.
Hugo Kornelis, SQL Server MVP

EXISTS Vs. NOT EXISTS

Is there a performace difference in the following queries, assuming tblAgent
contains 100,000 records?
IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
BEGIN
...do operation A
END
ELSE
BEGIN
...do operation B
END
________________________________________
____________________________
IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
BEGIN
...do operation B
END
ELSE
BEGIN
...do operation A
ENDIt depends (among 400 other things) on how likely you are to find a row
where AgentID = @.SomeAgentID.
"Justin" <jyang@.ioutsource.info> wrote in message
news:OKCem3LZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Is there a performace difference in the following queries, assuming
> tblAgent contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation A
> END
> ELSE
> BEGIN
> ....do operation B
> END
> ________________________________________
____________________________
>
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation B
> END
> ELSE
> BEGIN
> ....do operation A
> END
>|||EXISTS() and NOT EXISTS() require exactly the same processing to come
up with an answer, assuming the same expression in the parentheses.
The answer to either gives the answer to both - they are the same.
This means that it makes no difference if you test for one or the
other, as long as the THEN/ELSE logic is also adjusted accordingly.
Which appears to be what your example does.
Roy Harvey
Beacon Falls, CT
On Thu, 20 Apr 2006 17:03:37 -0400, "Justin" <jyang@.ioutsource.info>
wrote:

>Is there a performace difference in the following queries, assuming tblAgen
t
>contains 100,000 records?
>IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>BEGIN
> ....do operation A
>END
>ELSE
>BEGIN
> ....do operation B
>END
> ________________________________________
____________________________
>
>IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>BEGIN
> ....do operation B
>END
>ELSE
>BEGIN
> ....do operation A
>END
>|||I think "exists" ususally is better, since it will stop searching once the
1st row is found, but the "not exists" perhaps (depending on the index) will
search all rows.
"Justin" wrote:

> Is there a performace difference in the following queries, assuming tblAge
nt
> contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation A
> END
> ELSE
> BEGIN
> ....do operation B
> END
> ________________________________________
____________________________
>
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
> BEGIN
> ....do operation B
> END
> ELSE
> BEGIN
> ....do operation A
> END
>
>|||You're right. I was a bit distracted to another topic just now.
"Roy Harvey" wrote:

> EXISTS() and NOT EXISTS() require exactly the same processing to come
> up with an answer, assuming the same expression in the parentheses.
> The answer to either gives the answer to both - they are the same.
> This means that it makes no difference if you test for one or the
> other, as long as the THEN/ELSE logic is also adjusted accordingly.
> Which appears to be what your example does.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 20 Apr 2006 17:03:37 -0400, "Justin" <jyang@.ioutsource.info>
> wrote:
>
>|||Justin (jyang@.ioutsource.info) writes:
> Is there a performace difference in the following queries, assuming
> tblAgent contains 100,000 records?
> IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
>...
> IF NOT EXISTS(SELECT * FROM tblAgent WHERE AgentID = @.SomeAgentID)
They should perform the same. At least that is my expectation.
What matters a lot more is whether AgentID is indexed or not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||James Ma wrote:
> I think "exists" ususally is better, since it will stop searching once the
> 1st row is found, but the "not exists" perhaps (depending on the index) wi
ll
> search all rows.
I would expect "not exists" stop once first row is found either.
What is the point to continue if the result is already known?|||I was wrong in this case.
I was thinking some real-life scenarios like: if you want to prove a cattle
flock has BSE, you can stop testing once you find 1st cattle with BSE; but i
f
you want to prove the flock doesn’t has BSE, you need to test each cattle
of
the flock. Recently I have been studying statistics and somehow was
distracted to there. Sorry for the misleading post.
Thanks,
James
"Sericinus hunter" wrote:

> James Ma wrote:
> I would expect "not exists" stop once first row is found either.
> What is the point to continue if the result is already known?
>

exists vs nonempty

the AS2005 documentation states that nonemptycrossjoin is deprecated and exists or nonempty should be used instead. i am a bit confused by the intended usage for these two functions. the documentation says that exits performs what autoexits does. however, autoexits only pertains to attribute from the same dimension. on the other hand, sets from different dimensions can be passed to the exists function. if no measure group is specified, exactly how does exists eliminate tuples? is exists(set_x, set_y) equivalent to nonempty(set_x, set_y)?

this might be a trivial question, but since i pretty new to MDX, i could really use some advice here.

Mosha sums things up pretty well in this posting on his blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

EXISTS with two parameters does whatever autoexists does. EXISTS with three parameters does something similar to NONEMPTY but with one difference (as far as I understand it): because you can now optionally have nulls from your fact table preserved in measure values you might want to find out which combinations of dimension members exist as rows in a fact table even when their associated measure values are null, and to do this you need to use this overload of EXISTS because NONEMPTY of course removes combinations where measures return null.

HTH,

Chris

|||

I tried to explain this in a little bit more detail here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

Hope this helps

Mosha

http://www.mosha.com/msolap

exists vs nonempty

this might be a trivial question, but since i pretty new to MDX, i could really use some advice here.

Mosha sums things up pretty well in this posting on his blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

HTH,

Chris

|||

I tried to explain this in a little bit more detail here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

Hope this helps

Mosha

http://www.mosha.com/msolap

EXISTS vs INNER JOIN

Hi All -

I know the data is unseen and my pose a problem visualizing whats going on here, and I will post sample data if needed, but Ill try first w/o data. I mainly need to know if/how I am incorrectly using EXISTS.

my key on vwAssays is LotID,AssayRank I am trying grab just the records out of vwAssays for each Lot with the greatest AssayRank

LotID AssayRank 1 0 1 1 1 2 2 0 2 1 2 2 3 0 3 1 3 2

SELECT *

FROM vwAssays a

WHERE exists (select lotid, max(assayrank) assayrank from vwAssays l where a.lotid = l.lotid and a.assayrank = l.assayrank group by lotid ) and runid = 122

order by lotid

SELECT *

FROM vwAssays a inner join (select lotid, max(assayrank) assayrank from vwAssays group by lotid) l on a.lotid = l.lotid and a.assayrank = l.assayrank

where runid = 122

order by a.lotid

select lotid, max(assayrank) assayrank from vwAssays l where runid = 122 group by lotid

#1 does not work, it is showing all assayranks for each lotid

#2 works ok but Id like to use Exists instead

my actuall question is what needs to happen to #1 so that it does what #2 does.

Hi there,

I would just use solution #2 and move on. The main thing you need to know about EXISTS is that it only returns either True or False, therefore the only way to get to True or False is to basically use the logic you have in #2.

|||

using CTE:

Code Snippet

WITH mycte AS (SELECT LotID, AssayRank, runid, Row_Number() OVER (PARTITION BY LotID ORDER BY AssayRank DESC) AS numRank
FROM vwAssays

WHERE runid = 122)
SELECT LotID, AssayRank FROM mycte WHERE numRank = 1

Exists T-SQL

I would like to change the t-sql statement listed below to execute quicker.
Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
If a Pum value of 806478 is found within the last 60 minutes. The output
COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
Please help me complete this task.
Thank You,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
(Select count(Pum)
from Call_Movements
where DATEDIFF(mi, Started_Time, GETDATE()) <=60
AND left(cast(Pum as varchar(20)),6) = ('806478'))Try,
DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
declare @.d datetime
set @.d = convert(varchar(16), getdate(), 126) + ':00'
SET @.COUNT_CALL_MOVEMENTS_REC_0 =
case when exists (
Select
*
from
dbo.Call_Movements
where
(Started_Time between dateadd(minutes, -60, @.d) and @.d)
AND cast(Pum as varchar(20)) like '806478%'
) 1 then 0 end
go
AMB
"Joe K." wrote:

> I would like to change the t-sql statement listed below to execute quicker
.
> Change the COUNT_CALL_MOVEMENTS_REC_0 data type from SMALLINT to BINARY.
> If a Pum value of 806478 is found within the last 60 minutes. The output
> COUNT_CALL_MOVEMENTS_REC_0 = 1, if not COUNT_CALL_MOVEMENTS_REC_0 = 0.
> Please help me complete this task.
> Thank You,
>
> DECLARE @.COUNT_CALL_MOVEMENTS_REC_0 SMALLINT
> SET @.COUNT_CALL_MOVEMENTS_REC_0 =
> (Select count(Pum)
> from Call_Movements
> where DATEDIFF(mi, Started_Time, GETDATE()) <=60
> AND left(cast(Pum as varchar(20)),6) = ('806478'))

exists rowguidcol on table?

Hi there
I have a table, which I want to alter and add a rowguid. Before I add this
rowguid to one of my column, then I want to be sure that the table does not
already have such a column. Therefore I was wondering how I can investigate
whether the table has a rowguid or not by using the composition below.
if exists (select 1 from ...)
begin
end
I know it is possible to clarify if an identity is configured to Not For
Replication or not by using the colstat and thought it might be possible to
investigate for the rowguidcol in same way, but I can't find any references
about how it is done neither in the official documentation nor at any news
groups and other unofficial references.
I hope for a soon reply on this.
Thanks in regards,
Jessuse: IF (OBJECTPROPERTY(OBJECT_ID(N'tablename'),
'TableHasRowGuidCol') = 1)
instead of: IF EXISTS (SELECT 1 FROM ...)
"languy" wrote:

> Hi there
> I have a table, which I want to alter and add a rowguid. Before I add this
> rowguid to one of my column, then I want to be sure that the table does no
t
> already have such a column. Therefore I was wondering how I can investigat
e
> whether the table has a rowguid or not by using the composition below.
> if exists (select 1 from ...)
> begin
> end
> I know it is possible to clarify if an identity is configured to Not For
> Replication or not by using the colstat and thought it might be possible t
o
> investigate for the rowguidcol in same way, but I can't find any reference
s
> about how it is done neither in the official documentation nor at any news
> groups and other unofficial references.
> I hope for a soon reply on this.
> Thanks in regards,
> Jess
>
>|||You can do this:
if exists(select 1 from syscolums where
columnproperty(id,name,'IsRowGuidCol')=1
and object_name(id)='yourtable')
print 'you betcha'
-oj
"languy" <jn@.online.stofanet.dk> wrote in message
news:epGr6$YFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi there
> I have a table, which I want to alter and add a rowguid. Before I add this
> rowguid to one of my column, then I want to be sure that the table does
> not already have such a column. Therefore I was wondering how I can
> investigate whether the table has a rowguid or not by using the
> composition below.
> if exists (select 1 from ...)
> begin
> end
> I know it is possible to clarify if an identity is configured to Not For
> Replication or not by using the colstat and thought it might be possible
> to investigate for the rowguidcol in same way, but I can't find any
> references about how it is done neither in the official documentation nor
> at any news groups and other unofficial references.
> I hope for a soon reply on this.
> Thanks in regards,
> Jess
>|||It works. :-)
Thanks a lot.
Regards,
Jess
"oj" <nospam_ojngo@.home.com> wrote in message
news:eu9Z4ZZFFHA.2572@.tk2msftngp13.phx.gbl...
> You can do this:
> if exists(select 1 from syscolums where
> columnproperty(id,name,'IsRowGuidCol')=1
and object_name(id)='yourtable')
> print 'you betcha'
>
> --
> -oj
>
> "languy" <jn@.online.stofanet.dk> wrote in message
> news:epGr6$YFFHA.2180@.TK2MSFTNGP10.phx.gbl...
>

Sunday, February 26, 2012

Blog Archive

About Me