configure Defaultmaxbuffersize and DefaultmaxbufferRows

i want to improve the performance of my ssis...im left with last 3 bottleneks...3 huge tables ...here are the details :

table 1 :rows : 40 million +

size of each row..1 -2 KB

i had set the default max buffer to the max..100 MB and default max buffer rows to 100,000 ... considering that average row size is 1 kb.

table 2:

rows :17 million

row size : 280 bytes

again i have set the max buffer to 100 MB and default max buffer row to 300,000 .applyin the same logic..

table 3

rows: 59 million

size per row : 85 bytes..

as the row size was small...i made default max buffer row to 1000000 that'll still be less than 100 mb..which i had set as default max buffer size....

the first 2 have given a considerable speedup..but the 3rd one (as i feared) has gone dead slow...

ne ideas..and is one huge buffer better..or many small buffers...

Can you describe what transformations you're applying to the data from table 3? And what source and destination adapters you're using?|||its a oledb source and destination....the data flow tasks i mentioned (and few other similar) r called from a sequence container in control flow... the transformation is minimal...thouh there r 2 columns compared in the where clause(just 1 in others)..and i'm not sure of indexes on that as im not in control of that DB ... ya that may be a reason...but can u suggest anything apart from that..|||

I'd suggest that you try to determine the location of the bottleneck:

To check the source, try writing out the data from the source into a Raw File, and replacing the source adapter in your existing package with a Raw File Source adapter that's pointed at this file.

To check the destination, replace the existing destination adapter with an unconfigured Export Column transform.

To check the transforms, do both of the previous things.

Conditionaly hiding rows in a table in a single datagroup

Hello,
I have a report which takes data from a stored procedure and then reveals
some text on a row depending on whether or not a bit is true. The data coming
from teh stored procedure is a single row for a contact, and it displays a
number of rows in the report.
When we run the report into a PDF the data is all tidily on one page,
however in the web browser it is only showng 4 database rows per page (even
if there is only one additional line being displayed), this is because there
are so many rows (10), though the data isnt spaced out it is all at the top
of the report. Is there a way of getting more data to display in the report ?
Many Thanks
ChrisHi Chris,
I understood you would like to hide rows in a table, however I am not sure
in what condition you would like to hide the row? Would you please provide
us some detailed scenario examples?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
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.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Conditionally hide 2 out of 6 rows in a matrix?

I just spent about 30mins searching through the forums for this and saw several posts, but I didn't find a straight answer that seems like it would work for my report. How can I add only 2 data rows to a group (to hide them via the group's visible properties) and keep the rest of the rows outside of the group, but still in the same column (vertical area), as shown:

This is the view of all rows:

Cat1 Cat2 Product A Data 1 # # Data 2 # # Data 3 # # Data 4 # # Data 5 # # Data 6 # # Product B Data 1 # # Data 2 # # Data 3 # # Data 4 # # Data 5 # # Data 6 # #

And this is the view I'm seeking in some cases:

Cat1 Cat2 Product A Data 1 # # Data 4 # # Data 5 # # Data 6 # # Product B Data 1 # # Data 4 # # Data 5 # # Data 6 # #

This question is not clear to me. So, do you want to hide two rows from each group? If so does it have to be any specific rows. If this is what you want then I think you can do this with ROWCOUNT

|||

Each Data line that I have added is for a separate field from the dataset window and I need specific fields hidden, but if it makes things more workable for changing visibility, I could go by the row number on which that they are located (although I doubt [data] row number is an accessible value).

|||

Sorry, I meant to say RowNumber in my last post and instead I said RowCount. You might be able to do this with RowNumber(). This will give you the rownumber of the dataset. CountRows() will give you the total rows within the group.

See if you can use CountRows() and RowNumber() and develop a logic based on what you need.

|||

It's not as simple as that because setting a row visibility hidden to true would just blank out the row and leave white space, and the only way I see to group the data to toggle group visibility would be to stuff all data pieces into the same group. Suggestions have been given to put the data you want to hide into a group, but that may only work to hide data pieces to the right of some other data (which would be in a separate group). As far as I can see, that won't allow me to get the result I've pictured above in my first post.

Conditionally Expand a Table

I have a form that has a table in the center with text boxes above and below
where the table may contain 1 to n number of rows. When the table has 4 or
more rows the page looks well balanced but anything less makes it look too
compressed.
I would like to conditionally add some blank table rows and have been
attempting this by adding rows and setting the visibility property using
RowCount(). Problem is I have 2 groups in this table so have separate row
counts. I did a test and added RowCount() to a text box in the table header
and it shows the total for the entire table. Is there a syntax for RowCount
that will allow me to reference the header RowCount from each of the group
row visibility properties?
Or is there a better way to set a minimum table size?
Thankstry putting your items inside of a rectangle as a group - I think your table
will expand and contract within the bounds of the rectangle and keep your
text boxed on either side too.
"Mike Harbinger" wrote:
> I have a form that has a table in the center with text boxes above and below
> where the table may contain 1 to n number of rows. When the table has 4 or
> more rows the page looks well balanced but anything less makes it look too
> compressed.
> I would like to conditionally add some blank table rows and have been
> attempting this by adding rows and setting the visibility property using
> RowCount(). Problem is I have 2 groups in this table so have separate row
> counts. I did a test and added RowCount() to a text box in the table header
> and it shows the total for the entire table. Is there a syntax for RowCount
> that will allow me to reference the header RowCount from each of the group
> row visibility properties?
> Or is there a better way to set a minimum table size?
> Thanks
>
>

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditionally count rows in a table

I am building a Table Report where I need to "count" the number of cells in a column conditionally.

I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:

iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer

IF Cell.Value = "Orange" THEN

iCountOfOranges = iCountOfOranges + 1

ELSE IF Cell.Value = "Apple" THEN

iCountOfApples = iCountOfApples + 1

ELSE IF Cell.Value = "Banana" THEN

iCountOfBananas = iCountOfBananas + 1

The 3 count values would then be displayed in 3 footer rows at the bottom of the table.

Thanks

There are a couple of ways you could go about this. I generally prefer doing things such as this in SQL. Simply add another data set that returns the counts and display them.

If you can't change your query or just feel compelled to do it all within SSRS, I believe the easiest way to do it involves the RunningValue function. The following statment will accuratly count the number of times "apple" is returned:

Code Snippet

=RunningValue(

IIF(

IIF(

Fields!fruit.Value IS NOTHING,

"",

LCase(Fields!fruit.Value)

).Equals("apple"),

1,

0),

SUM,

"DataSet1"

)

Note that it will not even miss "Apple" because of the LCase function call.

Let me know how this works for you. You could also use custom code, but I will leave that as an exercise for a later date.

Good luck!

Larry Smithmier

|||Thanks Larry - that worked like a charm!

I have isolated the data from the end user (report building) by only exposing Stored Procedures. I'm trying to limit the number of Stored Procs that our data access interface will have.

I do have an additional question for you. Within a Report, Is there a way to run SQL statements against the dataset returned by a stored procedure. I had zero database experience when I started on this project (and hey - out of our entire team, I have the most DB experience so go figure) so I am learning as I go. I'm building a data repository that we will then have a reporting front end to do all kinds of statistical reports for those guys that sit in the Ivory Towers.

Thanks again!

Marty
|||

Hello Marty,

You can't use a stored procedure in a SQL statement, but you can use a Function. Functions look and feel like stored procedures, but can replace tables in SQL statements. For example, the following defines a function:

Code Snippet

CREATE FUNCTION TestFunction
(
)
RETURNS TABLE
AS
RETURN
(
SELECT * from Complex
)
GO

and here is the function used in a select statement:

Code Snippet

SELECT

*

FROM

dbo.TestFunction() AS TestFunction

Good luck!

Larry Smithmier

Conditional Split Transformation

Hi all,

I have set up a conditional split task which i want to use with a flat file data source. The flat file consists of multiple rows of data where the first column is an ID. The conditional split is based on the first column value.

What i'd like to know is if in the conditional split once it splits the data can the output be transformed. e.g. If one of the values coming from the flat file requires to be either split up into two values or requires to be passed into a stored procedure to manipulate it, can this be done?

Hope that makes sense.

All help is greatly appreciated, TIA.

Cheers,

Grant

Well in least words, YES!

Output of conditional split can be simply passed to any other control to manipulate in whatever way u like

|||Hi,

thanks for the reply. I have just realised what a stupid question it was. I have just dragged a constraint from the conditional loop and see that a dialog box allows you to select the output. My apologies, and thanks for the help.

Cheers,

Grant|||Hi Again,

Out of interest once i have the row of data i want to process, how would i go about doing the actually processing.

The first this i need to do is to pass once of the row values into a stored procedure and return a variable. Whta would be the best command for this. In the control flow i would have used an Execute SQL task, but this doesn't appear to be available.
Do i have to script anything like this once i have the row?

Thanks again,

Grant

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Conditional Rows (New to reporting services)

I appologize in advance if this is a question that has already been asked. My
problem is this, I am creating a report that will run on a daily basis, and I
need to add weekly total lines. Something along the lines of, if the day is
friday then add the row, which would calculate the totals for the week.
The other option, would be to populate all of the days of the month,
including a week total and month total line every time I run it. Help! I am
stuck here...
Thank you!
lhagertyUse a different dataset with a table or list which fetches the week row? If
it's not friday no row will be returned. Test the number of rows fetched to
determine if you display the table or list
Erik
"lhagerty" wrote:
> I appologize in advance if this is a question that has already been asked. My
> problem is this, I am creating a report that will run on a daily basis, and I
> need to add weekly total lines. Something along the lines of, if the day is
> friday then add the row, which would calculate the totals for the week.
> The other option, would be to populate all of the days of the month,
> including a week total and month total line every time I run it. Help! I am
> stuck here...
> Thank you!
> lhagerty|||Thank you I will try that!
"Erik van der Aar" wrote:
> Use a different dataset with a table or list which fetches the week row? If
> it's not friday no row will be returned. Test the number of rows fetched to
> determine if you display the table or list
> Erik
> "lhagerty" wrote:
> > I appologize in advance if this is a question that has already been asked. My
> > problem is this, I am creating a report that will run on a daily basis, and I
> > need to add weekly total lines. Something along the lines of, if the day is
> > friday then add the row, which would calculate the totals for the week.
> > The other option, would be to populate all of the days of the month,
> > including a week total and month total line every time I run it. Help! I am
> > stuck here...
> > Thank you!
> > lhagerty

Conditional row formatting in matrix

I've got a report that uses a matrix (pivot table). I want to format the rows using alternating colors (green-bar style). However, my matrix is not fully populated (some cells are null). Therefore, my formatting does not look correct. I'm using a expression like...
=iif(RowNumber("matrix1_columngroupscope") Mod 2,"Blue","White")
to format the rows. But the problem occurs in that the color does not change if the cell is null (e.g. not entry in the data set). For columns that are fully populated, I get the correct result. If columns that are not, I don't.virtualfergy wrote:
> Sorry if I'm being daft, but you said "attached report". How do I
> view the attachment?
You need to have OE for this or a news reader able to deal with attachment.
If you don't have this pls. find the GreenBar sample code below. Copy and
paste it into your *.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Qty">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Qty</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Qty.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.25in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="Category">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.125in</Top>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="Country">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Country">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Right>None</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
g) Mod 2, "AliceBlue", "White")</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value & " " &
RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="Count">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Color">
<Style>
<BorderStyle>
<Default>Solid</Default>
<Left>None</Left>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=Value</BackgroundColor>
<FontSize>1pt</FontSize>
<Color>=Value</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2,
"AliceBlue", "White")</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>3.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code />
<Width>6.875in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.Country, SUM([Order
Details].Quantity) AS Qty, Categories.CategoryName
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID
GROUP BY Customers.Country, Categories.CategoryName</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Whoa... that worked but what a convoluted way to make it happen. I hope you folks at MS make this simpler in the future. Thanks for the help.
"Roland" wrote:
> virtualfergy wrote:
> > Sorry if I'm being daft, but you said "attached report". How do I
> > view the attachment?
> You need to have OE for this or a news reader able to deal with attachment.
> If you don't have this pls. find the GreenBar sample code below. Copy and
> paste it into your *.rdl
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="Qty">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=ReportItems!Color.Value</BackgroundColor>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>Qty</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Qty.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.25in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>0.875in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>DataSet1</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="Category">
> <GroupExpressions>
> <GroupExpression>=Fields!CategoryName.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="CategoryName">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>CategoryName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CategoryName.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Width>2in</Width>
> <Top>0.125in</Top>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="Country">
> <GroupExpressions>
> <GroupExpression>=Fields!Country.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="Country">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> <Right>None</Right>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothin
> g) Mod 2, "AliceBlue", "White")</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>Country</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Country.Value & " " &
> RunningValue(Fields!Country.Value,CountDistinct,Nothing)</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1in</Width>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="Count">
> <GroupExpressions>
> <GroupExpression>=1</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <ReportItems>
> <Textbox Name="Color">
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> <Left>None</Left>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>=Value</BackgroundColor>
> <FontSize>1pt</FontSize>
> <Color>=Value</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2,
> "AliceBlue", "White")</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>0.125in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>3.25in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>26f1bf87-1fa6-4e77-8d1a-81b0cd940403</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=.;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Code />
> <Width>6.875in</Width>
> <DataSets>
> <DataSet Name="DataSet1">
> <Fields>
> <Field Name="Country">
> <DataField>Country</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Qty">
> <DataField>Qty</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="CategoryName">
> <DataField>CategoryName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Customers.Country, SUM([Order
> Details].Quantity) AS Qty, Categories.CategoryName
> FROM Customers INNER JOIN
> Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID INNER JOIN
> Products ON [Order Details].ProductID => Products.ProductID INNER JOIN
> Categories ON Products.CategoryID => Categories.CategoryID
> GROUP BY Customers.Country, Categories.CategoryName</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <Description />
> <rd:ReportID>ab2c120b-3169-427d-8ad6-b8716f8c5101</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>
>
>
>

Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

Conditional outer join help

I need to do an outer join in order to retrieve info in cases where some
table data does not exist, but doing this causes my query to return rows I
don't want for cases where data does exist in all tables. I have simplified
the problem as follows:
Say my data looks like this:
Table A Table B Table C
ID Code ID Code ID Code
1 11 11 A 11 X
1 12 12 A 12 Y
1 13 13 B 13 Z
2 21 21 C 21 X
My query needs to return data from other tables, which then join to Table A
on ID. I'm only interested in data from Table B and C where B.code = A and
C.code = Y. So effectively what I want returned from this part of the query
is
A.ID A.Code B.ID B.Code C.ID C.Code
1 12 12 A 12 Y
null null null null null null (where the null row is
from A.ID = 2)
The problem is, to get the null row returned from A.ID = 2, I have to do the
following:
select *
from A
left outer join B
on A.code = B.ID
and B.code = 'A'
left outer join C
on B.ID = C.ID
and C.code = 'Y'
But this means that I also get two extra rows returned for when A.ID = 1.
To get the correct rows returned for A.ID = 1, I need to do the following:
select *
from A
left outer join B
on A.code = B.ID
left outer join C
on B.ID = C.ID
where B.code = 'A'
and C.code = 'Y'
How can I do both?
Thanks in advance.Easy: replace the second outer join by an inner join between B and C. (I'm
not sure if you will have to put it into a subquery.)
Another possibility would be to put your first solution into a subquery
itself but this is a much less elegant solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>I need to do an outer join in order to retrieve info in cases where some
> table data does not exist, but doing this causes my query to return rows I
> don't want for cases where data does exist in all tables. I have
> simplified
> the problem as follows:
> Say my data looks like this:
> Table A Table B Table C
> ID Code ID Code ID Code
> 1 11 11 A 11 X
> 1 12 12 A 12 Y
> 1 13 13 B 13 Z
> 2 21 21 C 21 X
> My query needs to return data from other tables, which then join to Table
> A
> on ID. I'm only interested in data from Table B and C where B.code = A
> and
> C.code = Y. So effectively what I want returned from this part of the
> query
> is
> A.ID A.Code B.ID B.Code C.ID C.Code
> 1 12 12 A 12 Y
> null null null null null null (where the null row
> is
> from A.ID = 2)
> The problem is, to get the null row returned from A.ID = 2, I have to do
> the
> following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> and B.code = 'A'
> left outer join C
> on B.ID = C.ID
> and C.code = 'Y'
> But this means that I also get two extra rows returned for when A.ID = 1.
> To get the correct rows returned for A.ID = 1, I need to do the following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> left outer join C
> on B.ID = C.ID
> where B.code = 'A'
> and C.code = 'Y'
> How can I do both?
> Thanks in advance.|||Thanks Sylvain but inner joining between B and C eliminates the row of all
nulls that I need returned for A.ID = 2. I'm not sure how putting this in a
subquery would help? Can you be more specific?
Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
A.ID A.Code B.ID B.Code
1 11 11 A
1 12 12 A
null null null null (A.ID was 1)
null null null null (A.ID was 2)
So you can see that inner joining this to C on B.ID = C.ID is not going to
return the null rows. What am I missing in your explanation?
"Sylvain Lafontaine" wrote:

> Easy: replace the second outer join by an inner join between B and C. (I'
m
> not sure if you will have to put it into a subquery.)
> Another possibility would be to put your first solution into a subquery
> itself but this is a much less elegant solution.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>
>|||Sorry, but your repetition of the same names (ID and CODE) for differents
values may have mixed my little head. I've just took a little time to write
a test database with your data and here a query that I have made by
transforming your second query into a subquery and use it with an outer join
to the distinct values from A:
Select R.*, S.* from
(Select Distinct Id from A) as R Left outer join
(select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id as
CId, C.Code as CCode
from A left outer join B on A.code = B.ID
left outer join C on B.ID = C.ID
where (B.Code is Null and C.Code is Null)
or (B.code = 'A' and C.code = 'Y')
) as S
On R.Id = S.AId
and here are the result:
1 1 12 12 A 12 Y
2 NULL NULL NULL NULL NULL NULL
The first column is a new column that I have added and it simply gives the
list of distinct values for the ID of A. With the exception of this column,
this is exactly the result that you have asked for in your first post. I
have also added alias because of the multiple repetition of ID and CODE with
different meanings in the three tables.
Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
useless and can be probably replaced with INNER JOIN to give the same
results but I'm not sure if this is the case for you because I don't know
enough about your real data for the rest of the tables.
There are probably other possibilities, too but now, it's getting to late
for me.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
> Thanks Sylvain but inner joining between B and C eliminates the row of all
> nulls that I need returned for A.ID = 2. I'm not sure how putting this in
> a
> subquery would help? Can you be more specific?
> Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
> A.ID A.Code B.ID B.Code
> 1 11 11 A
> 1 12 12 A
> null null null null (A.ID was 1)
> null null null null (A.ID was 2)
> So you can see that inner joining this to C on B.ID = C.ID is not going to
> return the null rows. What am I missing in your explanation?
> "Sylvain Lafontaine" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Right now you have a magical "id" that does not tell us what it
identifies and a magical "code' that modeled as both strings and
numerics in the same schema. Then you have code and id are equi-joined
together. Here is my guess at what you might have meant to say:
CREATE TABLE Foobar
(foo_grp INTEGER NOT NULL,
member_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE Foo
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
foo_score CHAR(1) NOT NULL);
CREATE TABLE Bar
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
bar_score CHAR(1) NOT NULL);
Table A on ID. I'm only interested in data from Table B and C where
B.code = A and C.code = Y. <<
SELECT foo_grp, member_id, 'A' AS bar_score, 'Y' AS foo_score
FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foo AS F1, Bar AS B1
WHERE F1.member_id = Foobar.member_id
AND B1.member_id = Foobar.member_id
AND bar_score = 'A'
AND foo_score = 'Y');|||Hi Sylvain,
I really appreciate your help on this. Sorry that my attempt at
simplification has made things confusing. The real tables have hideous long
names and the referential integrity between them is full of holes.
I have tried to apply your solution but I don't understand how your inner
query generates the null column values. As you say, the outer joins in the
subqueries are effectively the same as inner joins. Therefore, there will b
e
no null column values in the S table. I tried adding the restrictions to th
e
inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code = 'A
'
left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this mean
s
I will always get an extra row of nulls for the A.ID = 1 row. i.e. my resul
t
set would be (using your leading row of distinct values from A):
1 1 12 12 A 12 Y
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
Jane
"Sylvain Lafontaine" wrote:

> Sorry, but your repetition of the same names (ID and CODE) for differents
> values may have mixed my little head. I've just took a little time to wri
te
> a test database with your data and here a query that I have made by
> transforming your second query into a subquery and use it with an outer jo
in
> to the distinct values from A:
> Select R.*, S.* from
> (Select Distinct Id from A) as R Left outer join
> (select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id a
s
> CId, C.Code as CCode
> from A left outer join B on A.code = B.ID
> left outer join C on B.ID = C.ID
> where (B.Code is Null and C.Code is Null)
> or (B.code = 'A' and C.code = 'Y')
> ) as S
> On R.Id = S.AId
> and here are the result:
> 1 1 12 12 A 12 Y
> 2 NULL NULL NULL NULL NULL NULL
> The first column is a new column that I have added and it simply gives the
> list of distinct values for the ID of A. With the exception of this colum
n,
> this is exactly the result that you have asked for in your first post. I
> have also added alias because of the multiple repetition of ID and CODE wi
th
> different meanings in the three tables.
> Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
> useless and can be probably replaced with INNER JOIN to give the same
> results but I'm not sure if this is the case for you because I don't know
> enough about your real data for the rest of the tables.
> There are probably other possibilities, too but now, it's getting to late
> for me.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
>
>|||Hi Jane,
The null column values are not generated by the inner query but by the
Left Outer Join of the outer query.
Excerpt for the aliases, the big inner query is the same as your second
query in your first post and this query generate only one line. The other
inner query (the small one: (Select Distinct Id from A) ) generate only
two lines with two values: 1 and 2 and combined as a Left Outer Join to the
other inner query can give only two big lines: the first one with values
other than null and the other one with all null values.
Maybe you could post here the query that you have tried and that gives
three lines instead of two.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
> Hi Sylvain,
> I really appreciate your help on this. Sorry that my attempt at
> simplification has made things confusing. The real tables have hideous
> long
> names and the referential integrity between them is full of holes.
> I have tried to apply your solution but I don't understand how your inner
> query generates the null column values. As you say, the outer joins in
> the
> subqueries are effectively the same as inner joins. Therefore, there will
> be
> no null column values in the S table. I tried adding the restrictions to
> the
> inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code =
> 'A'
> left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this
> means
> I will always get an extra row of nulls for the A.ID = 1 row. i.e. my
> result
> set would be (using your leading row of distinct values from A):
> 1 1 12 12 A 12 Y
> 1 NULL NULL NULL NULL NULL NULL
> 2 NULL NULL NULL NULL NULL NULL
> Jane
> "Sylvain Lafontaine" wrote:
>|||Hi,
Okay I see what you mean. I have used a version of your solution and it
works although I have to repeat a large query. I feel like there should be
a
better way but I don't want to post the actual query. It's too large and th
e
relationships between the tables are too hard to see.
Thanks for your help.
jane
"Sylvain Lafontaine" wrote:

> Hi Jane,
> The null column values are not generated by the inner query but by the
> Left Outer Join of the outer query.
> Excerpt for the aliases, the big inner query is the same as your secon
d
> query in your first post and this query generate only one line. The other
> inner query (the small one: ? (Select Distinct Id from A) ? ) generate o
nly
> two lines with two values: 1 and 2 and combined as a Left Outer Join to th
e
> other inner query can give only two big lines: the first one with values
> other than null and the other one with all null values.
> Maybe you could post here the query that you have tried and that gives
> three lines instead of two.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
>
>|||You can use a temporary table or a table variable to store the result of
this large query and avoid repeating it.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
> Hi,
> Okay I see what you mean. I have used a version of your solution and it
> works although I have to repeat a large query. I feel like there should
> be a
> better way but I don't want to post the actual query. It's too large and
> the
> relationships between the tables are too hard to see.
> Thanks for your help.
> jane
>
> "Sylvain Lafontaine" wrote:
>|||And finally, if you query is really complex, then the use of other options
like Exists() could be your best solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23UcmOnIYFHA.796@.TK2MSFTNGP09.phx.gbl...
> You can use a temporary table or a table variable to store the result of
> this large query and avoid repeating it.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
>

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.
Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pro...05/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.
|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>
|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:
>> Creating an index over a table causes its schema to change, and in turn
>> this causes queries that reference the table to be recompiled. So, the
>> short answer to "will the conditional index creation mess up the SQL
>> engine" is no.
>> SQL Server will first compile the procedure, and then start executing it.
>> If the schema of a table changes between the compilation and execution of
>> a statement referencing it, the statement will be recompiled.
>> Actually the behavior changed significantly between SQL 2000 and 2005. In
>> 2000, the recompilations would affect the entire batch or procedure. A
>> significant improvement has been made in SQL 2005 with statement-level
>> recompiles. As the name suggests, in SQL 2005 only the affected
>> statements are recompiled, rather than the entire batch or procedure.
>> For more information on the subject, we have a very good whitepaper here:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> The consequence of what you are doing is that if you interleave
>> executions of the procedure that do not cause the index creation with
>> others where the index is created, you will incur in a significant number
>> of recompiles, because the schema of the temp table won't match the
>> previous compiled plan. In SQL 2000, this will be exacerbated with the
>> lack of statement level recompiles. This might easily negate the benefits
>> of saving the overhead of creating an index when the table is small.
>> Also, creating an index on a small table is a low overhead operation
>> anyway. I'd consider always creating the index, and seeing if you can
>> make it part of the table definition altogether if applicable.

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pr...005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn th
is
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it.
If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where th
e
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled pla
n.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead o
f
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creati
ng
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote: