Alternating Detail Sections

Is there a way to have two tables and have the detail section show
alternating records? (ie:51795 in one table and 51795P in the other)
Horizontallay
I need to compare records from two different datasets and have no idea how I
am going to do this?
--
Thank You, LeoHello Leo,
Insert field from different dataset is not supported in the table. However,
you may use function to transfer it. However, it still may not work as you
expect. The best option is using method to combine the data you want in one
dataset by using query or store prcedure.
Please feel free to respond back if you have further questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.
| Thread-Topic: Alternating Detail Sections
| thread-index: AcS8VnfT0lQlTSXmSBqXC/bhzmR85A==| X-WBNR-Posting-Host: 64.91.16.201
| From: "=?Utf-8?B?VHJ1c3N3b3Jrc0xlbw==?=" <Leo@.noemail.noemail>
| Subject: Alternating Detail Sections
| Date: Wed, 27 Oct 2004 11:55:03 -0700
| Lines: 10
| Message-ID: <63592D60-D9BA-47F3-8325-FE139745CF93@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:33335
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there a way to have two tables and have the detail section show
| alternating records? (ie:51795 in one table and 51795P in the other)
| Horizontallay
|
| I need to compare records from two different datasets and have no idea
how I
| am going to do this?
|
|
| --
| Thank You, Leo
|

Alternating BackgroundColor for rows in a table

I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.

Alternating background colors in table

Is there anyway to have alternating background colors in a table that is in a list? The existing methods I've seen work except that the tables don't always start with the same background color, as so:

BEGIN LIST
TABLE 1
(white background)
(gainsboro background)
(white background)

TABLE 2
(gainsboro background)
(white background)
(gainsboro background)
END LIST

Hopefully this gives you an idea of what I'm talking about. In table 2 above, the first row should have a white background, just like in table 1. It appears that SSRS treats all rows within a list as 1 table.

Is there any solution for this problem?

Hi,

try this:

=Iif(RowNumber(Nothing) Mod 2, "gainsboro ", "White")

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens, but that's what I'm already using. The color alternates, but it doesn't distinguish between occurrences of the table within a single list.|||

THen you have to provide the scope in the paranthetes. So replace the word Nothing with the name of the scope.

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

Alternating background color

Got a dataset that is used to populate a table. Want to alternate the
background color on every other row in the displayed detail group. Easy
enough right? Here's the catch: the output is grouped at display time. A
query output might be:
KEY Value1 Value2
A 0 1
A 1 0
B 5 0
C 3 0
C 0 7
etc...
The DISPLAY output is grouped on the KEY, and the two values are summed to
give me a display such as:
KEY Value1 Value2
A 1 1
B 5 0
C 3 7
Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
"Grey")", it counts EVERY row returned from the original query, not the
grouped output, so I don't get a uniform white-grey-white pattern. Anyone
know a workaround for this?
TIA,
BrianOk, I found my workaround. Someone is bound to have this issue sometime in
the future, so I'll put the workaround here.
I created a little routine in the custom Code area of the report that simply
toggles and returns an integer value:
Dim Public bgColor As Integer = 0
Public Function alternateColor As Integer
If bgColor = 0
bgColor = 1
return bgColor
else
bgColor = 0
return bgColor
end if
End Function
When i put my method call in the background color on the entire table ROW,
the result was alternating COLUMN colors. This is because the method was
called for every cell (column) in the row. In order to get alternating ROW
color, I only called the alternateColor routine in the FIRST column in the
table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
column in the row would simply check the "Code.bgColor" value for its
current value, and base its color on that (iif(Code.bgColor = 0, "white",
"grey")).
Maybe this will come in handy for someone else someday....
Brian
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> Got a dataset that is used to populate a table. Want to alternate the
> background color on every other row in the displayed detail group. Easy
> enough right? Here's the catch: the output is grouped at display time. A
> query output might be:
> KEY Value1 Value2
> A 0 1
> A 1 0
> B 5 0
> C 3 0
> C 0 7
> etc...
> The DISPLAY output is grouped on the KEY, and the two values are summed to
> give me a display such as:
> KEY Value1 Value2
> A 1 1
> B 5 0
> C 3 7
> Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> "Grey")", it counts EVERY row returned from the original query, not the
> grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> know a workaround for this?
> TIA,
> Brian
>|||Great solution, I've been playing around with RowNumber for ages - this is
much better!!!
Thanks Brian!!!
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This was just what i was looking for. My returned dataset sometimes Groups
so that rownumbers aren't in a consecutive order, giving some strange
alternate highlighting results using the conventional method. This should
work nicely, cheers
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>|||This didn't work for me since I am setting a row to hidden based on a value
in that row. SQL RS thinks that row is still there and displays two back to
back colors instead of alternating the colors.
Any ideas?
Thanks,
Don
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" <brian.grant@.si-intl-kc.com> wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>

Alternate ways of deleting records - without logging

Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:

> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>

alternate to formula

I have a table where one of the field is having formula, where UDF is used.
Is there any way by which formula can be removed and any other method is
used like trigger,
Because we can not have index on a formula based fieldCOMPUTED column? Can you show us the source?
"Vikram" <aa@.aa> wrote in message
news:eq0vE6aeGHA.3572@.TK2MSFTNGP03.phx.gbl...
>I have a table where one of the field is having formula, where UDF is used.
> Is there any way by which formula can be removed and any other method is
> used like trigger,
> Because we can not have index on a formula based field
>|||I think you cannot have an index on computed field either.
Though in SQL Server 2005 you can set the computed column as persisited and
create an index on it.|||Why not?
create table test (c1 int not null,c2 as c1*10)
create index ind_comp on test(c2)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:45CD1DD1-6AF4-4872-AD5D-28450AA30E23@.microsoft.com...
>I think you cannot have an index on computed field either.
> Though in SQL Server 2005 you can set the computed column as persisited
> and
> create an index on it.|||Oops.. sorry... you are right.. tea time for me :)
I it with foreign key constraints|||In fact, computed columns may be indexed if certain conditions are met:
http://msdn2.microsoft.com/en-us/library/ms189292.aspx
In SQL 2000 a computed column is not persisted until it's used in an index,
while SQL 2005 has this new option that you've mentioned.
ML
http://milambda.blogspot.com/|||> I it with foreign key constraints
You can create an index on the column that has FK
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BB66B25E-F4A8-4163-B5C5-8C20A8734C48@.microsoft.com...
> Oops.. sorry... you are right.. tea time for me :)
> I it with foreign key constraints|||No.. i meant foreign key on a computed column.|||Yes ,it is true
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0A422A1B-2100-4D50-A610-AAD305AFDD3B@.microsoft.com...
> No.. i meant foreign key on a computed column.
>sql

Alternate to a not in query

-- tested schema below --
-- create tables --
create table tbl_test
(serialnumber char(12))
go
create table tbl_test2
(serialnumber char(12),
exportedflag int)
go
--insert data --
insert into tbl_test2 values ('123456789010',0)
insert into tbl_test2 values ('123456789011',0)
insert into tbl_test2 values ('123456789012',0)
insert into tbl_test2 values ('123456789013',0)
insert into tbl_test2 values ('123456789014',0)
insert into tbl_test2 values ('123456789015',0)
insert into tbl_test2 values ('123456789016',0)
insert into tbl_test2 values ('123456789017',0)
insert into tbl_test2 values ('123456789018',0)
insert into tbl_test2 values ('123456789019',0)

insert into tbl_test values ('123456789011')
insert into tbl_test values ('123456789012')
insert into tbl_test values ('123456789013')
insert into tbl_test values ('123456789014')
insert into tbl_test values ('123456789015')

-- query --
Select serialnumber from tbl_test2
where serialnumber
not in (select serialnumber from tbl_test) and
exportedflag=0

This query runs quite fast with only the data above but when both
tables get million plus rows, the query simply bogs down. Is there a
better way to write this query?Select serialnumber
from tbl_test2 a
left joint tbl_test b on a. serialnumber = b.serialnumber
where (b.serialnumber IS NULL)
AND (a.exportedflag=0)|||There is another way to write the query, but it's not better (in fact,
I think it's worse):

Select tbl_test2.serialnumber from tbl_test2
left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
where exportedflag=0 and tbl_test.serialnumber is null

To improve the performance of this query, you should create primary
keys on the tables. Besides the conceptual benefits of a proper design,
this would accomplish (at least) the following things:
- create an index on the serialnumber column
- declare that the serialnumber column does not allow duplicates
- declare that the serialnumber column does not allow nulls
These things will help the Query Optimizer very much to create a better
execution plan.

Razvan|||
Razvan Socol wrote:
> There is another way to write the query, but it's not better (in fact,
> I think it's worse):
> Select tbl_test2.serialnumber from tbl_test2
> left join tbl_test on tbl_test2.serialnumber=tbl_test.serialnumber
> where exportedflag=0 and tbl_test.serialnumber is null

Razvan,

Why worse?

The common wisdom seems to be that it is always more efficient
eliminate nested subqueries, if possible.

My understanding is that the optimizer will internally eliminate the
subquery by doing a left join as above if it can.|||Ira Gladnick (IraGladnick@.yahoo.com) writes:
> Why worse?
> The common wisdom seems to be that it is always more efficient
> eliminate nested subqueries, if possible.

It's worse, becase it does not express the intent of the query equally
well, and therefore can contribute to higher maintenance costs.

> My understanding is that the optimizer will internally eliminate the
> subquery by doing a left join as above if it can.

I don't know if this is the case, but in such case there is even less
reason to rewrite the query in an obscure way.

I would write the query as:

Select serialnumber
from tbl_test2 t2
where not exists (select *
from tbl_test t
where t2.serialnuber = t.serialnumber)
and exportedflag=0

In SQL 6.5 this would typically perform better than NOT IN. But I believe
SQL 2000 will rewrite NOT IN to NOT EXISTS internally, so it is not that
much of an issue for performance. But NOT EXISTS is more general to use
than NOT IN, because you can handle multi-column conditions. Furthermore,
if there are NULL values involved, NOT IN can give you surpriese.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(kjaggi@.hotmail.com) writes:
> -- query --
> Select serialnumber from tbl_test2
> where serialnumber
> not in (select serialnumber from tbl_test) and
> exportedflag=0
> This query runs quite fast with only the data above but when both
> tables get million plus rows, the query simply bogs down. Is there a
> better way to write this query?

Beside the obvious point from Razvan about indexes, if you are on a multi-
CPU box, you can try this at the end of the query:

OPTION (MAXDOP 1)

this turns off parallelism. I've seen SQL Server use massive parallel
plans for this type of query, when a non-parallel plan have been much
faster.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1

column2

row1

data1,1

data1,2

row2

data2,1

data2,2

row3

data3,1

data3,2

Please, if somebody can help me

Thanks

hey there

there is something on this page that may help you

http://blogs.msdn.com/chrishays/

|||

Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||

Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.

I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1

column2

row1

data1,1

data1,2

row2

data2,1

data2,2

row3

data3,1

data3,2

Please, if somebody can help me

Thanks

Please read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1 column2 row1 data1,1 data1,2 row2 data2,1 data2,2 row3 data3,1 data3,2

Please, if somebody can help me

Thanks

hey there

there is something on this page that may help you

http://blogs.msdn.com/chrishays/

|||

Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||

Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.

I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.

alternate table value

I need to read through two tables, first stepping thru the first one,
finding a value to a particular field (model no) along with the value of
another (price), and then read thru the second table, looking for the same
field value (model no), and if found, use that (price) field vale verses the
original value for the (price) field.
TABLE 1
Model No. Price
X45 $30
X60 $50
TABLE 2
Model No. Price
X50 $35
X60 $48
RESULT SET
Model No. Price
X45 $30
X50 $35
X60 $48
Thanks,
DonOne method:
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
Hope this helps.
Dan Guzman
SQL Server MVP
<dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>I need to read through two tables, first stepping thru the first one,
>finding a value to a particular field (model no) along with the value of
>another (price), and then read thru the second table, looking for the same
>field value (model no), and if found, use that (price) field vale verses
>the original value for the (price) field.
> TABLE 1
> Model No. Price
> X45 $30
> X60 $50
> TABLE 2
> Model No. Price
> X50 $35
> X60 $48
> RESULT SET
> Model No. Price
> X45 $30
> X50 $35
> X60 $48
> Thanks,
> Don
>
>|||Hi Dan,
Thanks for the help. It appears as though this is very close. But, I am
still getting duplicate records for the same model number, when the desire
is to only use the record from table 2 when the same model no is found in
both.
Here is the result set I am getting:
Model No. Price
X45 $30
X50 $35
X60 $50
X60 $48
When I am looking for:
Model No. Price
X45 $30
X50 $35
X60 $48 <-- Table 2 is specifically used when same
model no is in both tables.
Below is the actual code being used:
SELECT TOP 100 PERCENT COALESCE (dbo.hb_view_organization_plan.plan_id,
dbo.hb_view_business_unit_plan.plan_id,
dbo.hb_view_subdivision_plan.plan_id,
dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
(dbo.hb_view_organization_plan.plan_no_name,
dbo.hb_view_business_unit_plan.plan_no_name,
dbo.hb_view_subdivision_plan.plan_no_name,
dbo.hb_view_lot_plan.plan_no_name)
AS plan_no_name, COALESCE
(dbo.hb_view_organization_plan.current_retail_price,
dbo.hb_view_business_unit_plan.current_retail_price,
dbo.hb_view_subdivision_plan.current_retail_price,
dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
COALESCE (dbo.hb_view_organization_plan.source,
dbo.hb_view_business_unit_plan.source, dbo.hb_view_subdivision_plan.source,
dbo.hb_view_lot_plan.source) AS source
FROM dbo.hb_view_organization_plan FULL OUTER JOIN
dbo.hb_view_business_unit_plan ON
dbo.hb_view_organization_plan.plan_id =
dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
dbo.hb_view_lot_plan FULL OUTER JOIN
dbo.hb_view_subdivision_plan ON
dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
dbo.hb_view_business_unit_plan.plan_id =
dbo.hb_view_subdivision_plan.plan_id
ORDER BY dbo.hb_view_organization_plan.plan_no_name
The logic here is that is to sum all the records and when a duplicate
plan_no_name is found, is to display only one record from the lowest in the
hierarchy, which organization, business_unit, subdivsion, and lot. Always
use the lot record first (lowest), subdivision second, business unit third,
and finally organization last (highest).
Again, thanks for your help.
Regards,
Don
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O1LO80ISFHA.2788@.TK2MSFTNGP09.phx.gbl...
> One method:
> SELECT
> COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
> COALESCE(t2.Price, t1.Price) AS Price
> FROM Table1 t1
> FULL JOIN Table2 t2 ON
> t1.ModelNo = t2.ModelNo
> ORDER BY 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <dbj> wrote in message news:%23D%23EntISFHA.2132@.TK2MSFTNGP14.phx.gbl...
>|||You design is wrong. Given a fixed hierarchy, each level should be in
the same row since it is an attribute of that entity. This is a basic
principle of data modeling. You might want to read what Chris Date and
Dave McGoveran said about this kind of design error. They called it
orthogonal design, while I prefer attribute splitting.
duplicate plan_no_name is found, is to display only one record [sic]
from the lowest in the hierarchy, which organization, business_unit,
subdivsion, and lot. Always use the lot record [sic] first (lowest),
subdivision second, business unit third, and finally organization last
(highest). <<
Row are not records and until you learn the differences, you will not
be able to write good SQL. You will think in terms of DML for
solutions that shoudl have been done with correct DDL. Since you did
not post DDL, it is impossible to make anything but a guess about the
data.
And using things like "view" or "tbl" in a data element name to tell us
about the storage method is a violationfo ISO-11179 and good data
mdoeling practices, too.|||On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:

>Hi Dan,
>Thanks for the help. It appears as though this is very close. But, I am
>still getting duplicate records for the same model number, when the desire
>is to only use the record from table 2 when the same model no is found in
>both.
>Here is the result set I am getting:
>Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
>When I am looking for:
>Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
>model no is in both tables.
>Below is the actual code being used:
(snip)
Hi Don,
Checking the code you posted, I see that you're not attempting to combine
two tables, but a total of four! Now, whereas a full outer join betwee two
tables is not very tough, a full outer join of three or more tables has
some gotchas in the ON clause.
Here's a simplified version of a four-way full outer join to show you how
you should approach this:
SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
FROM Table1 AS t1
FULL JOIN Table2 AS t2
ON t2.keycol = t1.keycol
FULL JOIN Table3 AS t3
ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
FULL JOIN Table4 AS t4
ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
I'll leave it too you to fill this pattern with your (IMO much too long)
table and column names.
Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
your query, and in the worst case introduces unnecessary overhead at
execution time. Please remove it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||<dbj> wrote in message news:OJ3NcqQSFHA.3144@.tk2msftngp13.phx.gbl...
> Hi Dan,
> Thanks for the help. It appears as though this is very close. But, I am
> still getting duplicate records for the same model number, when the desire
> is to only use the record from table 2 when the same model no is found in
> both.
> Here is the result set I am getting:
> Model No. Price
> X45 $30
> X50 $35
> X60 $50
> X60 $48
> When I am looking for:
> Model No. Price
> X45 $30
> X50 $35
> X60 $48 <-- Table 2 is specifically used when same
> model no is in both tables.
I get the expected results using the sample data provided in your original
post. Below is the complete script:
CREATE TABLE Table1
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table1 PRIMARY KEY,
price int NOT NULL
)
CREATE TABLE Table2
(
ModelNo char(3) NOT NULL
CONSTRAINT PK_Table2 PRIMARY KEY,
price int NOT NULL
)
INSERT INTO Table1 VALUES('X45', 30)
INSERT INTO Table1 VALUES('X60', 50)
INSERT INTO Table2 VALUES('X50', 35)
INSERT INTO Table2 VALUES('X60', 48)
SELECT
COALESCE(t1.ModelNo, t2.ModelNo) AS ModelNo,
COALESCE(t2.Price, t1.Price) AS Price
FROM Table1 t1
FULL JOIN Table2 t2 ON
t1.ModelNo = t2.ModelNo
ORDER BY 1
--results
ModelNo Price
-- --
X45 30
X50 35
X60 48

> Below is the actual code being used:
> SELECT TOP 100 PERCENT COALESCE
> (dbo.hb_view_organization_plan.plan_id,
> dbo.hb_view_business_unit_plan.plan_id,
> dbo.hb_view_subdivision_plan.plan_id,
> dbo.hb_view_lot_plan.plan_id) AS plan_id, COALESCE
> (dbo.hb_view_organization_plan.plan_no_name,
> dbo.hb_view_business_unit_plan.plan_no_name,
> dbo.hb_view_subdivision_plan.plan_no_name,
> dbo.hb_view_lot_plan.plan_no_name)
> AS plan_no_name, COALESCE
> (dbo.hb_view_organization_plan.current_retail_price,
> dbo.hb_view_business_unit_plan.current_retail_price,
> dbo.hb_view_subdivision_plan.current_retail_price,
> dbo.hb_view_lot_plan.current_retail_price) AS current_retail_price,
> COALESCE (dbo.hb_view_organization_plan.source,
> dbo.hb_view_business_unit_plan.source,
> dbo.hb_view_subdivision_plan.source,
> dbo.hb_view_lot_plan.source) AS source
> FROM dbo.hb_view_organization_plan FULL OUTER JOIN
> dbo.hb_view_business_unit_plan ON
> dbo.hb_view_organization_plan.plan_id =
> dbo.hb_view_business_unit_plan.plan_id FULL OUTER JOIN
> dbo.hb_view_lot_plan FULL OUTER JOIN
> dbo.hb_view_subdivision_plan ON
> dbo.hb_view_lot_plan.plan_id = dbo.hb_view_subdivision_plan.plan_id ON
> dbo.hb_view_business_unit_plan.plan_id =
> dbo.hb_view_subdivision_plan.plan_id
> ORDER BY dbo.hb_view_organization_plan.plan_no_name
> The logic here is that is to sum all the records and when a duplicate
> plan_no_name is found, is to display only one record from the lowest in
> the hierarchy, which organization, business_unit, subdivsion, and lot.
> Always use the lot record first (lowest), subdivision second, business
> unit third, and finally organization last (highest).
> Again, thanks for your help.
> Regards,
> Don
>
I wouldn't expect duplicate plan_ids as long as that is the is the primary
key of these tables. However, you might very well have duplicate
plan_no_name data. In that case, it is a symptom of a flaw in your data
model.
Hope this helps.
Dan Guzman
SQL Server MVP|||Call me old-fashioned or just plain dumb, but I'd go about it like
this:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM t1
LEFT JOIN t2 ON t1.modelno = t2.modelno
The existence of a row in t2 results in the t2 price; whereas an
absence of a row in t2 results in t1 price.|||> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>
True, but also the absence of a row in t1 should provide the t2 price based
on Don's requirements, hence the FULL JOIN. With his original data:
SELECT t1.modelno, price = isnull(t2.price,t1.price)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.modelno = t2.modelno
Results:
modelno price
-- --
X45 30
X60 48
Desired results:
modelno price
-- --
X45 30
X50 35
X60 48
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeme" <jeme.rey@.gmail.com> wrote in message
news:1114383919.282023.11520@.o13g2000cwo.googlegroups.com...
> Call me old-fashioned or just plain dumb, but I'd go about it like
> this:
> SELECT t1.modelno, price = isnull(t2.price,t1.price)
> FROM t1
> LEFT JOIN t2 ON t1.modelno = t2.modelno
> The existence of a row in t2 results in the t2 price; whereas an
> absence of a row in t2 results in t1 price.
>|||Hi Hugo,
Your suggestion worked. Thanks VERY MUCH for your help. And I would also
like to thank everyone else who responded as well. I am sure my
inexperience was apparent but really appreciate your patience helping me
through this. Below is your code that worked!
SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
t4.plan_id) AS plan_id, COALESCE (t1.plan_availability_pricing_id,
t2.plan_availability_pricing_id,
t3.plan_availability_pricing_id, t4.plan_availability_pricing_id) AS
plan_availability_pricing_id,
COALESCE (t1.plan_number, t2.plan_number,
t3.plan_number, t4.plan_number) AS plan_number, COALESCE (t1.plan_number,
t2.plan_name,
t3.plan_name, t4.plan_name) AS plan_namer, COALESCE
(t1.plan_no_name, t2.plan_no_name, t3.plan_no_name, t4.plan_no_name) AS
plan_no_name,
COALESCE (t1.current_retail_price,
t2.current_retail_price, t3.current_retail_price, t4.current_retail_price)
AS current_retail_price, COALESCE (t1.source,
t2.source, t3.source, t4.source) AS source
FROM dbo.hb_view_lot_plan t1 FULL OUTER JOIN
dbo.hb_view_subdivision_plan t2 ON t2.plan_id =
t1.plan_id FULL OUTER JOIN
dbo.hb_view_business_unit_plan t3 ON t3.plan_id =
COALESCE (t1.plan_id, t2.plan_id) FULL OUTER JOIN
dbo.hb_view_organization_plan t4 ON t4.plan_id =
COALESCE (t1.plan_id, t2.plan_id, t3.plan_id)
ORDER BY t1.plan_no_name
Regards,
Don
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vo3o61t3hm1doo7ou1f1hoimq6nrdq5b07@.
4ax.com...
> On Sun, 24 Apr 2005 15:17:14 -0500, <dbj> wrote:
>
> (snip)
> Hi Don,
> Checking the code you posted, I see that you're not attempting to combine
> two tables, but a total of four! Now, whereas a full outer join betwee two
> tables is not very tough, a full outer join of three or more tables has
> some gotchas in the ON clause.
> Here's a simplified version of a four-way full outer join to show you how
> you should approach this:
> SELECT COALESCE (t1.keycol, t2.keycol, t3.keycol, t4.keycol),
> COALESCE (t1.datacol, t2.datacol, t3.datacol, t4.datacol)
> FROM Table1 AS t1
> FULL JOIN Table2 AS t2
> ON t2.keycol = t1.keycol
> FULL JOIN Table3 AS t3
> ON t3.keycol = COALESCE(t1.keycol, t2.keycol) -- Gotcha!
> FULL JOIN Table4 AS t4
> ON t4.keycol = COALESCE(t1.keycol, t2.keycol, t3.keycol) -- Gotcha!
> I'll leave it too you to fill this pattern with your (IMO much too long)
> table and column names.
> Oh, and by the way - TOP 100 PERCENT is totally meaningless; it clutters
> your query, and in the worst case introduces unnecessary overhead at
> execution time. Please remove it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 22:48:27 -0500, <dbj> wrote:

>Hi Hugo,
>Your suggestion worked. Thanks VERY MUCH for your help. And I would also
>like to thank everyone else who responded as well. I am sure my
>inexperience was apparent but really appreciate your patience helping me
>through this. Below is your code that worked!
>SELECT TOP 100 PERCENT COALESCE (t1.plan_id, t2.plan_id, t3.plan_id,
(snip)
Hi Don,
Good to hear that it worked. Now all that's left to do is to get rid of
the totally useless TOP 100 PERCENT. :-)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Alternate rows from different table

Dear All,
I have a requirement in which I have to display a row from one
table & the corresponding row from the another table. e.g. say there
are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
corresponding record in T2 as R1' then the display would come as
R1 /* Data from Table 1 */
R1' /* Data from Table 2 */
R2
R2'
& so on.....
This is possible by manipulating the resultset in a program. But I
would like to know if it is possible in the SQL query.
Thanks & Regards,
Praveenpkb wrote:

> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
Looks like a UNION to me. Assuming r is the common column that
determines R1, R2, etc, try:
SELECT r, col1, col2, ...
FROM
(SELECT r, 1 AS tbl, col1, col2, ...
FROM tbl1
UNION ALL
SELECT r, 2 AS tbl, col1, col2, ...
FROM tbl2) AS T
ORDER BY r, tbl ;
If that's not what you wanted then my signature explains how to post
better questions so that you can get better answers. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"pkb" <praveen.bhushan@.gmail.com> wrote in message
news:1138803314.265069.114980@.g49g2000cwa.googlegroups.com...
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
You could:
select 1 AS rank, ... from T1 where ...
union
select 2 AS rank, ... from T2 where ...
Order by (you will have to add the corresponding columns, whatever that is),
rank
Sorry, can't give you anything more detailed without your table structure.|||Hi David, Raymond,
Thanks for your quick replies. The only problem in the above
solution will come when there is a record which is present in
one of the tables. Actually I wanted to make pairs from the two tables.
Well I have got the idea.
Regards,
Praveen

alternate row in table

I have a table with a header and a detail. How I can set the detail so that
it displays alternate color? Thanks.Try this:
=IIF(RowNumber(Nothing) Mod 2,"WhiteSmoke", "White")
WhiteSmoke and White being the two variable colors.
Select the detail row and in the background properties select "Expression".
Then put that formula in.
Cheers.
"tangolp" wrote:
> I have a table with a header and a detail. How I can set the detail so that
> it displays alternate color? Thanks.|||I have used this but I condionally set a row to hidden based on a value.
When a row is hidden SQL RS thinks it's still there and I get two back to
back colors. Any ideas how to fix this?
Thanks,
Don
"Michael Montgomery" wrote:
> Try this:
> =IIF(RowNumber(Nothing) Mod 2,"WhiteSmoke", "White")
> WhiteSmoke and White being the two variable colors.
> Select the detail row and in the background properties select "Expression".
> Then put that formula in.
> Cheers.
> "tangolp" wrote:
> > I have a table with a header and a detail. How I can set the detail so that
> > it displays alternate color? Thanks.

Alternate row in diffrerent colors

Hi,
I wish to change the back colour for alternate rows in a report (say Table).
I haven't found anything that tells me the current-rownum. The RowNum seems
more like a row-count. Please help!!
Thanks in advance,
Regards,
DattaTo have alternating colours in a table, use this formula
=iif(RowNumber(Nothing) Mod 2, "Gainsboro", "White")
A slightly more refined version can be found in Adlai Maschiach's blog:
http://dotnetjunkies.com/WebLog/adlaim/archive/2004/08/29/23594.aspx
This is the way to do it if you want to control the alternating colours in a
table with groups, as the first one will not take groupings into
consideration.
To do it in a matrix, you need to do it like Chris Hays writes
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:5FC4A7F0-B950-4F0C-A726-D383AC381960@.microsoft.com...
> Hi,
> I wish to change the back colour for alternate rows in a report (say
> Table).
> I haven't found anything that tells me the current-rownum. The RowNum
> seems
> more like a row-count. Please help!!
> Thanks in advance,
> Regards,
> Datta

alternate line shading in a table

Is there a way to set up line shading on a report that is created from a table in SRS? The report is great but I want to shade every other line to make it easy to read and being a beginner with SRS I have not been able to find a way to do this.

Any help would be appreciated.

Thanks,

Do you mean like setting an expression in the Backgroudcolor property like: =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White") ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks that did it.

Alternate item color

How do I change the row background color for table items?
In data grid control, I can use
<AlternatingItemStyle BackColor="#FFFFCC"></AlternatingItemStyle> but I am
not able to find this attribute in report services? Thanks for help.If the row has grouping
=iif(RunningValue(Fields!Grouped.Value,CountDistinct,Nothing) Mod 2,
"Cornsilk", "White")
or if it has no grouping
=iif(RowNumber(Nothing) Mod 2, "Cornsilk", "White")
"Help is in the way" wrote:
> How do I change the row background color for table items?
> In data grid control, I can use
> <AlternatingItemStyle BackColor="#FFFFCC"></AlternatingItemStyle> but I am
> not able to find this attribute in report services? Thanks for help.
>
>

Alternate Background Color

I have a table about 3 rows. I want to see every other row highlighted gray
so it easier for the eye to follow.
any ideas?
Thanks
Fab.You can use RowNumber(nothing) Mod 2 to find out if the row is even or odd,
and use this in an IIf to change the background color.
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Use this in the backgroundcolor property :
=iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
"Fab" wrote:
> I have a table about 3 rows. I want to see every other row highlighted gray
> so it easier for the eye to follow.
> any ideas?
> Thanks
> Fab.
>
>|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Hi
I would use a custom function for this as it is quite a common thing to
want to do.
' Alternate Row Colors
Public Function AltCol(ByVal RowNum As Integer) As String
Dim ReturnColor as String
If RowNum Mod 2
ReturnColor = "WhiteSmoke"
Else
ReturnColor = "White"
End If
Return ReturnColor
End Function
You could extend this by adding optional variables for the alterante
colors making this a completely generic function.
To use this go to expressions in the Row BG Color tab in properties.
add the following line: =Code!AltCol(RowNumber())
This is just pointer in the right direction but what it allows is for
you to uses this all over a report, groups with out having to IIF
everywhere.|||Is it possible to have different colors? like "LightGrey" for odd rows and
"WhiteSmoke" for even rows? How would I combine the two iif statements - one
for mod 2 =0 and other for mod 2 = 1?
Thanks.
"Eric" wrote:
> Use this in the backgroundcolor property :
> =iif(RowNumber(nothing) mod 2 =0,"LightGrey",Nothing)
>
> "Fab" wrote:
> > I have a table about 3 rows. I want to see every other row highlighted gray
> > so it easier for the eye to follow.
> >
> > any ideas?
> >
> > Thanks
> > Fab.
> >
> >
> >

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

Altering the identity seed of a table

Hi,

Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.

This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?

cheers
Pete StoreyDBCC Checkident.|||Thanks!