Help - Select with newid()

While trying to get a list from joined tables that return 2 GUIDs, I ran
into an interesting gotcha. I'm trying to get 1 guid that's unique for all
the rows returned, and 1 guid that is unique only for each of the child
rows. I _thought_ that could be accomplished using a derived table.
To illustrate, here's a query of the pubs db.
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join ( select title_id, title, newid() as TitleGUID from titles ) T on
sales.title_id = T.title_id
I would hope this would return something like:
StoreA 10 TitleA 100
StoreB 11 TitleA 100
StoreB 12 TitleB 101
StoreC 13 TitleA 100
StoreC 14 TitleC 102
etc...
Instead, both of the GUIDs are unique for each row. In looking at the
execution plan, it appears that the NewId() functions are being resolved at
the parent query.
Is there any way to have the NewId() function within the derived table stay
static? Or, any other ideas on how to generate the results would be
appreciated.
- RickThis works exactly as how it should work. The newid() as TitleStoreGUID
applies to each row in the resultset generated by the join. The newid() as
TitleGUID applies to each row in the resultset generated by titles. So, by
design each call to newid() should generate an unique id. I would be very
troubled if they generate a same id.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> While trying to get a list from joined tables that return 2 GUIDs, I ran
> into an interesting gotcha. I'm trying to get 1 guid that's unique for
all
> the rows returned, and 1 guid that is unique only for each of the child
> rows. I _thought_ that could be accomplished using a derived table.
> To illustrate, here's a query of the pubs db.
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join ( select title_id, title, newid() as TitleGUID from titles ) T on
> sales.title_id = T.title_id
> I would hope this would return something like:
> StoreA 10 TitleA 100
> StoreB 11 TitleA 100
> StoreB 12 TitleB 101
> StoreC 13 TitleA 100
> StoreC 14 TitleC 102
> etc...
> Instead, both of the GUIDs are unique for each row. In looking at the
> execution plan, it appears that the NewId() functions are being resolved
at
> the parent query.
> Is there any way to have the NewId() function within the derived table
stay
> static? Or, any other ideas on how to generate the results would be
> appreciated.
> - Rick
>|||Well, oj, it may work exactly as how it should. But I'm not sure this is
precisely documented or widely known.
There is no question about the uniqueness of TitleStoreGUID. The question is
why TitleGUID is unique even for the same title.
If you look at the query plan, it definitely supports your explanation
because newid() as TutleGUID is indeed evaluated at the outer most layer in
the final resultset. Therefore, we see the uniqueness of its value for each
row, given the nature of newid().
However, I'm struggling to find where in the BOL or in the SQL specs does it
say that we shouldn't expect the subquery to be evaluated first and that the
subsequent evaluation of the query uses the cached resultset. In other
words, why can't it be evaluated as if the derived table is a real tmp table
or a table variable as something similar to the following:
declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
uniqueidentifier)
insert into @.tmp
select title_id, title, newid() as TitleGUID from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join @.tmp T on
sales.title_id = T.title_id
order by title
Maybe this type of subquery is always considered a correlated subquery, and
therefore all this confusion would go away :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"oj" <nospam_ojngo@.home.com> wrote in message
news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> This works exactly as how it should work. The newid() as TitleStoreGUID
> applies to each row in the resultset generated by the join. The newid() as
> TitleGUID applies to each row in the resultset generated by titles. So, by
> design each call to newid() should generate an unique id. I would be very
> troubled if they generate a same id.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > While trying to get a list from joined tables that return 2 GUIDs, I ran
> > into an interesting gotcha. I'm trying to get 1 guid that's unique for
> all
> > the rows returned, and 1 guid that is unique only for each of the child
> > rows. I _thought_ that could be accomplished using a derived table.
> >
> > To illustrate, here's a query of the pubs db.
> > select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join ( select title_id, title, newid() as TitleGUID from titles ) T
on
> > sales.title_id = T.title_id
> >
> > I would hope this would return something like:
> > StoreA 10 TitleA 100
> > StoreB 11 TitleA 100
> > StoreB 12 TitleB 101
> > StoreC 13 TitleA 100
> > StoreC 14 TitleC 102
> > etc...
> >
> > Instead, both of the GUIDs are unique for each row. In looking at the
> > execution plan, it appears that the NewId() functions are being resolved
> at
> > the parent query.
> >
> > Is there any way to have the NewId() function within the derived table
> stay
> > static? Or, any other ideas on how to generate the results would be
> > appreciated.
> >
> > - Rick
> >
> >
>|||<< Maybe this type of subquery is always considered a correlated subquery,
and therefore all this confusion would go away :-) >>
I've always worked under the belief that what made a _correlated_ subquery
was a reference to an outer query object from within the inner query, thus
causing the behavior of re-evaluation of the inner query for each row of the
outer query.
The #tmp table does work. I could toss the statements:
select title_id, title, newid() as TitleGUID into #Tmp from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join #Tmp T on sales.title_id = T.title_id
into a stored procedure. I will if I have to, though I'm hoping the greater
minds in this group might be able to help me with a better way.
TIA,
- Rick
BTW - OJ, your utilities look awesome!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> Well, oj, it may work exactly as how it should. But I'm not sure this is
> precisely documented or widely known.
> There is no question about the uniqueness of TitleStoreGUID. The question
is
> why TitleGUID is unique even for the same title.
> If you look at the query plan, it definitely supports your explanation
> because newid() as TutleGUID is indeed evaluated at the outer most layer
in
> the final resultset. Therefore, we see the uniqueness of its value for
each
> row, given the nature of newid().
> However, I'm struggling to find where in the BOL or in the SQL specs does
it
> say that we shouldn't expect the subquery to be evaluated first and that
the
> subsequent evaluation of the query uses the cached resultset. In other
> words, why can't it be evaluated as if the derived table is a real tmp
table
> or a table variable as something similar to the following:
> declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> uniqueidentifier)
> insert into @.tmp
> select title_id, title, newid() as TitleGUID from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join @.tmp T on
> sales.title_id = T.title_id
> order by title
> Maybe this type of subquery is always considered a correlated subquery,
and
> therefore all this confusion would go away :-)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > This works exactly as how it should work. The newid() as TitleStoreGUID
> > applies to each row in the resultset generated by the join. The newid()
as
> > TitleGUID applies to each row in the resultset generated by titles. So,
by
> > design each call to newid() should generate an unique id. I would be
very
> > troubled if they generate a same id.
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > While trying to get a list from joined tables that return 2 GUIDs, I
ran
> > > into an interesting gotcha. I'm trying to get 1 guid that's unique
for
> > all
> > > the rows returned, and 1 guid that is unique only for each of the
child
> > > rows. I _thought_ that could be accomplished using a derived table.
> > >
> > > To illustrate, here's a query of the pubs db.
> > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> T.TitleGUID
> > > from stores
> > > join sales on stores.stor_id = sales.stor_id
> > > join ( select title_id, title, newid() as TitleGUID from titles )
T
> on
> > > sales.title_id = T.title_id
> > >
> > > I would hope this would return something like:
> > > StoreA 10 TitleA 100
> > > StoreB 11 TitleA 100
> > > StoreB 12 TitleB 101
> > > StoreC 13 TitleA 100
> > > StoreC 14 TitleC 102
> > > etc...
> > >
> > > Instead, both of the GUIDs are unique for each row. In looking at the
> > > execution plan, it appears that the NewId() functions are being
resolved
> > at
> > > the parent query.
> > >
> > > Is there any way to have the NewId() function within the derived table
> > stay
> > > static? Or, any other ideas on how to generate the results would be
> > > appreciated.
> > >
> > > - Rick
> > >
> > >
> >
> >
>|||RickT;
My understanding is the same as yours.
I'm deliberately cross posting this to the sqlserver.programming group,
hoping that Joe Celko has time to straighten us out :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:u1SmlAWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> << Maybe this type of subquery is always considered a correlated subquery,
> and therefore all this confusion would go away :-) >>
> I've always worked under the belief that what made a _correlated_ subquery
> was a reference to an outer query object from within the inner query, thus
> causing the behavior of re-evaluation of the inner query for each row of
the
> outer query.
> The #tmp table does work. I could toss the statements:
> select title_id, title, newid() as TitleGUID into #Tmp from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join #Tmp T on sales.title_id = T.title_id
> into a stored procedure. I will if I have to, though I'm hoping the
greater
> minds in this group might be able to help me with a better way.
> TIA,
> - Rick
> BTW - OJ, your utilities look awesome!
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> > Well, oj, it may work exactly as how it should. But I'm not sure this is
> > precisely documented or widely known.
> >
> > There is no question about the uniqueness of TitleStoreGUID. The
question
> is
> > why TitleGUID is unique even for the same title.
> >
> > If you look at the query plan, it definitely supports your explanation
> > because newid() as TutleGUID is indeed evaluated at the outer most layer
> in
> > the final resultset. Therefore, we see the uniqueness of its value for
> each
> > row, given the nature of newid().
> >
> > However, I'm struggling to find where in the BOL or in the SQL specs
does
> it
> > say that we shouldn't expect the subquery to be evaluated first and that
> the
> > subsequent evaluation of the query uses the cached resultset. In other
> > words, why can't it be evaluated as if the derived table is a real tmp
> table
> > or a table variable as something similar to the following:
> >
> > declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> > uniqueidentifier)
> > insert into @.tmp
> > select title_id, title, newid() as TitleGUID from titles
> >
> > select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join @.tmp T on
> > sales.title_id = T.title_id
> > order by title
> >
> > Maybe this type of subquery is always considered a correlated subquery,
> and
> > therefore all this confusion would go away :-)
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > > This works exactly as how it should work. The newid() as
TitleStoreGUID
> > > applies to each row in the resultset generated by the join. The
newid()
> as
> > > TitleGUID applies to each row in the resultset generated by titles.
So,
> by
> > > design each call to newid() should generate an unique id. I would be
> very
> > > troubled if they generate a same id.
> > >
> > > --
> > > -oj
> > > RAC v2.2 & QALite!
> > > http://www.rac4sql.net
> > >
> > >
> > > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > > While trying to get a list from joined tables that return 2 GUIDs, I
> ran
> > > > into an interesting gotcha. I'm trying to get 1 guid that's unique
> for
> > > all
> > > > the rows returned, and 1 guid that is unique only for each of the
> child
> > > > rows. I _thought_ that could be accomplished using a derived table.
> > > >
> > > > To illustrate, here's a query of the pubs db.
> > > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> > T.TitleGUID
> > > > from stores
> > > > join sales on stores.stor_id = sales.stor_id
> > > > join ( select title_id, title, newid() as TitleGUID from
titles )
> T
> > on
> > > > sales.title_id = T.title_id
> > > >
> > > > I would hope this would return something like:
> > > > StoreA 10 TitleA 100
> > > > StoreB 11 TitleA 100
> > > > StoreB 12 TitleB 101
> > > > StoreC 13 TitleA 100
> > > > StoreC 14 TitleC 102
> > > > etc...
> > > >
> > > > Instead, both of the GUIDs are unique for each row. In looking at
the
> > > > execution plan, it appears that the NewId() functions are being
> resolved
> > > at
> > > > the parent query.
> > > >
> > > > Is there any way to have the NewId() function within the derived
table
> > > stay
> > > > static? Or, any other ideas on how to generate the results would be
> > > > appreciated.
> > > >
> > > > - Rick
> > > >
> > > >
> > >
> > >
> >
> >
>

HELP - Designing a Subscripion System for a Group of Reports

We're developing an ASP.NET 2.0 (C#) application that will act as a
front-end to SQL Server 2005 Reporting Services. We have an interesting
requirement that [we believe] can't be solved using RS out-of-the-box. So,
I'm writing today to get some ideas on possible solutions.
The requirement is that the app. needs to support, what we're calling, a
report group. A report group is simply a group of similar reports that can
be executed as a single unit (instead of having to execute each report
individually). This was not too bad to solve. We simply created our own
database tables that track which report group contains which reports.
The tricky part is that we now have to figure out a way to create a
subscription that executes the report group (thereby executing each
individual report in the report group automatically). Creating a
subscription for a single report is fairly straightforward, but it appears
this concept of a report group is not a part of RS.
Does anyone out there have any good ideas/suggestions on how we can
implement such functionality?
The best thought we have so far is to write another application that manages
the execution of each report in a report group. So, the SQL Server 2005 job
would call this additional little application, this additional app. would
lookup which reports are in the report group, and then it would loop through
all the reports and execute them one-by-one. But, this has some issues-like
how do we handle the output type (PDF, Excel, etc.) and how do we handle the
destination (e-mail, printer, etc.) of each report. Plus, it seems like we
would be somewhat reinventing the wheel as far as subscriptions go.
Thanks.Why aren't you using one master report with all the other reports as
sub-reports?
Tom Bizannes
oiduts wrote:
> We're developing an ASP.NET 2.0 (C#) application that will act as a
> front-end to SQL Server 2005 Reporting Services. We have an interesting
> requirement that [we believe] can't be solved using RS out-of-the-box. So,
> I'm writing today to get some ideas on possible solutions.
> The requirement is that the app. needs to support, what we're calling, a
> report group. A report group is simply a group of similar reports that can
> be executed as a single unit (instead of having to execute each report
> individually). This was not too bad to solve. We simply created our own
> database tables that track which report group contains which reports.
> The tricky part is that we now have to figure out a way to create a
> subscription that executes the report group (thereby executing each
> individual report in the report group automatically). Creating a
> subscription for a single report is fairly straightforward, but it appears
> this concept of a report group is not a part of RS.
> Does anyone out there have any good ideas/suggestions on how we can
> implement such functionality?
> The best thought we have so far is to write another application that manages
> the execution of each report in a report group. So, the SQL Server 2005 job
> would call this additional little application, this additional app. would
> lookup which reports are in the report group, and then it would loop through
> all the reports and execute them one-by-one. But, this has some issues-like
> how do we handle the output type (PDF, Excel, etc.) and how do we handle the
> destination (e-mail, printer, etc.) of each report. Plus, it seems like we
> would be somewhat reinventing the wheel as far as subscriptions go.
> Thanks.|||You could try setting up a shared schedule. It would still require
individual subscriptions for each report but they would all start
firing at the same scheduled time.