How to simplify/optimize this query?

Hi,
Could the following query be simplified/optimized? The database has a table
that maps users to locations, locations to chapters, and sections to
chapters. I need to issue a query that would return a list of sections that
can be assigned to a user (excluding the ones that have already been
assigned) .
@.UserId int
[...]
SELECT SectionID, [Name],
FROM Section
WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID = @.UserID) AND
SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
WHERE ChapterID IN
(SELECT DISTINCT ChapterID FROM ChapterLocation
WHERE LocationID IN
(SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
)
);
I realize that this might not be as clear as it should be but I'd appreciate
_any_ suggestions.
Thanks,
Dan> Could the following query be simplified/optimized?
I think the query below is equivalent. I think indexes on the columns in
the WHERE/JOIN clauses may help performance(e.g. a composite index on
UserSection UserID, SectionID).
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
) AND
EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Could the following query be simplified/optimized? The database has a
> table that maps users to locations, locations to chapters, and sections to
> chapters. I need to issue a query that would return a list of sections
> that can be assigned to a user (excluding the ones that have already been
> assigned) .
> @.UserId int
> [...]
> SELECT SectionID, [Name],
> FROM Section
> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID => @.UserID) AND
> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
> WHERE ChapterID IN
> (SELECT DISTINCT ChapterID FROM ChapterLocation
> WHERE LocationID IN
> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
> )
> );
> I realize that this might not be as clear as it should be but I'd
> appreciate _any_ suggestions.
> Thanks,
> Dan
>|||Thanks for the reply. The query looks clean but there seems to be a problem
with EXISTS() in the WHERE clause. Some of the sections included in the
result set are not assigned to the user's location(s) (implicitly through
chapters). I.e. the query selects chapters that are not assigned to user's
locations in addition to the chapters that are assigned.
Any suggestions?
Thanks,
Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
> I think the query below is equivalent. I think indexes on the columns in
> the WHERE/JOIN clauses may help performance(e.g. a composite index on
> UserSection UserID, SectionID).
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> ) AND
> EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of sections
>> that can be assigned to a user (excluding the ones that have already been
>> assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||> Thanks for the reply. The query looks clean but there seems to be a
> problem
> with EXISTS() in the WHERE clause. Some of the sections included in the
> result set are not assigned to the user's location(s) (implicitly through
> chapters). I.e. the query selects chapters that are not assigned to
> user's locations in addition to the chapters that are assigned.
> Any suggestions?
I missed the join from ChapterSection.SectionID back to Section.SectionID.
See the corrected query below. If this still doesn't work for you, please
post the table DDL and sample data so that I can test the solution.
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
)
AND EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID
AND cs.SectionID = s.SectionID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
> Thanks for the reply. The query looks clean but there seems to be a
> problem with EXISTS() in the WHERE clause. Some of the sections included
> in the result set are not assigned to the user's location(s) (implicitly
> through chapters). I.e. the query selects chapters that are not assigned
> to user's locations in addition to the chapters that are assigned.
> Any suggestions?
> Thanks,
> Dan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns in
>> the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||Thanks. It works now.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5F09A613-CEF3-45CA-B2EA-6A3D39CB69D5@.microsoft.com...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem
>> with EXISTS() in the WHERE clause. Some of the sections included in the
>> result set are not assigned to the user's location(s) (implicitly through
>> chapters). I.e. the query selects chapters that are not assigned to
>> user's locations in addition to the chapters that are assigned.
>> Any suggestions?
> I missed the join from ChapterSection.SectionID back to Section.SectionID.
> See the corrected query below. If this still doesn't work for you, please
> post the table DDL and sample data so that I can test the solution.
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> )
> AND EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID
> AND cs.SectionID = s.SectionID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem with EXISTS() in the WHERE clause. Some of the sections included
>> in the result set are not assigned to the user's location(s) (implicitly
>> through chapters). I.e. the query selects chapters that are not assigned
>> to user's locations in addition to the chapters that are assigned.
>> Any suggestions?
>> Thanks,
>> Dan
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns
>> in the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID
>> = @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID =>> @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>>
>

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!! :(
Is there any way to write a reasonably simple query to get the above schema'
Thanks
GregHi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
John
"Greg Bacchus" wrote:
> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!! :(
> Is there any way to write a reasonably simple query to get the above schema'
> Thanks
> Greg|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
> http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
> John
> "Greg Bacchus" wrote:
> > Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> > just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> > schema that I want to produce is quite large (with a reasonable amount of
> > repitition) this seems unnecessarily complicated. A simplified version of the
> > schema I want to produce is:
> >
> > <ArrayOfEntity>
> > <Entity Key=123>
> > <Name>...</Name>
> > <Prop1>...</Prop1>
> > <Prop2>...</Prop2>
> > <Prop3>...</Prop3>
> > <Node1 Key=32>text</Node1>
> > <Node2 Key=43>text</Node2>
> > <Node3 Key=54>text</Node3>
> > <Node4 Key=65>text</Node4>
> > </Entity>
> > <Entity Key=234>
> > ...
> > </Entity>
> > </ArrayOfEntity>
> >
> > The thing is, there are quite a few of these child "Node"s, which will only
> > contain a key and text value. There are also more complicated child nodes
> > (not shown here).
> >
> > I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> > because I could then write a function to generate the child node. But this
> > does not seem to allow me to specify the Node Key's!! :(
> >
> > Is there any way to write a reasonably simple query to get the above schema'
> > Thanks
> > Greg|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
.NET
John
"Greg Bacchus" wrote:
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
> > Hi Greg
> >
> > You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> > data in this format. A different approach you be to apply a transform after
> > the data has been extracted. You could use XSLT to do this, possibly use the
> > document()
> > function to include and export the data in fragments.
> >
> > http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&
> >
> > John
> >
> > "Greg Bacchus" wrote:
> >
> > > Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> > > just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> > > schema that I want to produce is quite large (with a reasonable amount of
> > > repitition) this seems unnecessarily complicated. A simplified version of the
> > > schema I want to produce is:
> > >
> > > <ArrayOfEntity>
> > > <Entity Key=123>
> > > <Name>...</Name>
> > > <Prop1>...</Prop1>
> > > <Prop2>...</Prop2>
> > > <Prop3>...</Prop3>
> > > <Node1 Key=32>text</Node1>
> > > <Node2 Key=43>text</Node2>
> > > <Node3 Key=54>text</Node3>
> > > <Node4 Key=65>text</Node4>
> > > </Entity>
> > > <Entity Key=234>
> > > ...
> > > </Entity>
> > > </ArrayOfEntity>
> > >
> > > The thing is, there are quite a few of these child "Node"s, which will only
> > > contain a key and text value. There are also more complicated child nodes
> > > (not shown here).
> > >
> > > I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> > > because I could then write a function to generate the child node. But this
> > > does not seem to allow me to specify the Node Key's!! :(
> > >
> > > Is there any way to write a reasonably simple query to get the above schema'
> > > Thanks
> > > Greg

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
Thanks
Greg
Hi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/micro...f8fbde6?hl=en&
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schema?
> Thanks
> Greg
|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
> http://groups.google.com/group/micro...f8fbde6?hl=en&
> John
> "Greg Bacchus" wrote:
|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
..NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of th
e
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
..
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column n
ame,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
?
Thanks
GregHi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
[url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[/u
rl]
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more th
an
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as t
he
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of
the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will onl
y
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column
name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schem
a'
> Thanks
> Greg|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve t
he
> data in this format. A different approach you be to apply a transform afte
r
> the data has been extracted. You could use XSLT to do this, possibly use t
he
> document()
> function to include and export the data in fragments.
> [url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[
/url]
> John
> "Greg Bacchus" wrote:
>|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
.NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
>sql

How to simplify my endless query. Thanks.

Hello group!

I am having a problem with simplying my query...

I would like to get customers' balance info based on how many months
since they opened their accounts. The tricky part here is accounts
starting with '28' are treated differently than other accounts, they
are given 3 months grace period. In other words, for all other
accounts, their month0 balance is the balance of their open_month, and
month1 balance is the balance after the account is opened 1 month, and
so on. But accounts starting with '28' month0 balance would be the
balance after the account is opened 3 months, and month1 balance would
be the balance after the account is opened 4 months, and so on.

My query below works, but since some customers are more than 10 years
old (more than 120 months), my query is endless! Does anyone know a
better way to do the same job? Many thanks!

create table a
(person_id int
,account int
,open_date datetime)

insert into a values(1,200001,'11/15/2004')
insert into a values(2,280001,'8/20/2004')

create table b
(account int
,balance_date datetime
,balance money)

insert into b values(200001,'11/30/2004',700)
insert into b values(200001,'12/31/2004',800)
insert into b values(200001,'1/31/2005',900)
insert into b values(200001,'2/28/2005',1000)
insert into b values(280001,'8/30/2004',7000)
insert into b values(280001,'9/30/2004',8000)
insert into b values(280001,'10/31/2004',9000)
insert into b values(280001,'11/30/2004',10000)
insert into b values(280001,'12/31/2004',15000)
insert into b values(280001,'1/31/2005',20000)
insert into b values(280001,'2/28/2005',30000)

--Ideal output--

person_idacc_nomonth0_balancemonth1_balancemonth2_balancemonth3_balance
120000017008009001000
2280000110000150002000030000

select a.person_id
,a.account
,month0_balance=case
when a.account like '2%' and a.account not like '28%'
then
sum(case datediff(mm, a.open_date, balance_date) when 0
then b.balance else 0 end)
else sum(case datediff(mm, a.open_date, balance_date)
when 3 then b.balance else 0 end)
end
,month1_balance =case
when a.account like '2%' and a.account not like '28%'
then
sum(case datediff(mm, a.open_date, balance_date) when 1
then b.balance else 0 end)
else sum(case datediff(mm, a.open_date, balance_date)
when 4 then b.balance else 0 end)
end
from a as a
join b as b
on a.account=b.account
group by a.person_id, a.account(rong.guo@.gmail.com) writes:
> My query below works, but since some customers are more than 10 years
> old (more than 120 months), my query is endless! Does anyone know a
> better way to do the same job? Many thanks!

Hm, first I read endless as "query runs forever", but now I realize
that you want one column for each month.

That's a dynamic cross-tab, which you cannot to in SQL out-of-box,
since a query always produces a table with a well-defined set of
columns.

You could use dynamic SQL to create the beast; you can read about
dynamic SQL on my web site: http://www.sommarskog.se/dynamic_sql.html.
You may also consider the third-party tool RAC, which is very good
for this sort of things. (They say. I have never used it myself, but
it's a standard recommendation.) It's at http://www.rac4sql.net/.

Since I first misread your question, I looked at improving the query.
Since this is a simple join, good indexing is probably the best for
performance. But this maybe somewhat more effective:

select a.person_id, a.account,
month0_balance = SUM(CASE datediff(mm, a.open_date, balance_date)
WHEN CASE WHEN a.account LIKE '2%' AND
a.account NOT LIKE '28%'
THEN 0
ELSE 3
END THEN b.balance
ELSE 0
END),
month1_balance = SUM(CASE datediff(mm, a.open_date, balance_date)
WHEN CASE WHEN a.account LIKE '2%' AND
a.account NOT LIKE '28%'
THEN 1
ELSE 4
END THEN b.balance
ELSE 0
END)
from a as a
join b as b on a.account=b.account
group by a.person_id, a.account

(Egads! I have never had a CASE in the WHEN part of a CASE expression
before!)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland!
Sorry for the misleading word 'endless'... And you are right, I've
never had a CASE in the WHEN part of a CASE expression:-)) but it is so
exciting to learn! Thanks again!|||On Fri, 18 Mar 2005 23:20:17 +0000 (UTC), Erland Sommarskog wrote:

> (rong.guo@.gmail.com) writes:
>> My query below works, but since some customers are more than 10 years
>> old (more than 120 months), my query is endless! Does anyone know a
>> better way to do the same job? Many thanks!
> Hm, first I read endless as "query runs forever", but now I realize
> that you want one column for each month.
> That's a dynamic cross-tab, which you cannot to in SQL out-of-box,
> since a query always produces a table with a well-defined set of
> columns.

I would start with this (perhaps as a view) to collect the monthly balances
and do the 3 month correction for 28s:

select a.person_id, a.account,
sum(b.balance) AS MonthBalance,
'Month' + convert(varchar(),
datediff(mm, a.open_date, balance_date) -
case when a.account LIKE '2%' AND a.account NOT like '28%' then 0
else 3 END)
AS MonthNum
FROM a as a
JOIN b as b on a.account=b.account
GROUP BY a.person_id, a.account,
datediff(mm, a.open_date, balance_date) -
case when a.account LIKE '2%' AND a.account NOT like '28%' then 0
else 3 END

Then I'd put a crosstab tool on the client to consume this.

How to show duplicate rows in Report Builder ?

Hi!
I've created a report in Report Builder, based on a query. When I run the report in Report Builder, it doesn't show the duplicates rows, even if it works fine in Data source view.

How can I see al rows in Report Builder(even if they're duplicates) ?

Thank you in advance.

Best regards,
VV
Report Builder shows groups of data, if the data you gropued does not contain a unique column, the data will be grouped, if you want to display all rows, you will need to display column which is different in the mutli-instanced rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to setup this query?

hi,
i have a table which has an ID, Time (unix time in seconds), and a score. th
e data looks something like this:
ID Time Score
1 1079748053937 1.0
2 1079748053945 0.67
3 1079748053945 0.32
4 1079748053945 0.01
5 1079748053950 0.90
6 1079748053950 0.1
my problem is that i want to write a query that will get the highest score a
t a given time. can anyone offer any help'
thanks!If I understand what you want, the highest score for each
time can be obtained with something like:
select time, max(score)
from your table
group by time
-Sue
On Mon, 22 Mar 2004 11:51:14 -0800, "mike"
<anonymous@.discussions.microsoft.com> wrote:

>hi,
> i have a table which has an ID, Time (unix time in seconds), and a score.
the data looks something like this:
>ID Time Score
>1 1079748053937 1.0
>2 1079748053945 0.67
>3 1079748053945 0.32
>4 1079748053945 0.01
>5 1079748053950 0.90
>6 1079748053950 0.1
>my problem is that i want to write a query that will get the highest score
at a given time. can anyone offer any help'
>thanks!

How to set up a regular job which will put the quesry result into file and email

Dear forum people,

I created a query which suppose to run every 2 weeks. I know how to schedule a job, my question is how to get the query result into a text or excel file.
If I need to use DTS package, please give me a step by step explanation how to move the result into text file and how to email this file.

I am appreciate every answer.

Thank you.

Annyou can do this whole thing with SQLMail.|||Thrasymachus,
please explain me how can I use SQLMail. Please do not get angry with me as I am not sql person, I just create some queries and know how to use some other things from SQL Enterprise Management.
Thank you.|||I do not get angry but I do not repeat readily available information. So, if you go to Start-->Programs-->Microsoft SQL Server-->SQL Server books Online-->Contents-->Admnistering SQL Server-->Managing Servers-->SQL Mail you can read all about this.

If you want to see examples type xp_sendmail in http://www.google.com/codesearch/advanced_code_search.

How to set right utf-8 in sql server 2005 db?

Hello,

I try to insert some data in Vietnamese into sql server db from Sql server management studio express.

I enter an insert query with some unicode data like this 'm?t hai ba b?n n?m sáu b?y tám chín m??i', and I expect the db should contain the same text as above.

However, what I retrieved is 'm?t hai ba b?n nam sáu b?y tám chín mu?i'.

Can anyone help? Many thanks.

maivangho

Hi~ you may check you server's regional settings in control panel, and set all those to your language related, and check<globalization> section of config file.

If it still doesn't work, underSystem.Text there are some classes for encoding/decoding, you may try encode your data before putting them into DB... though this is not a good walkaround..

How to set primary Key

hey can any one pls reply me how to set primary key in a table.

pls post a query.

thanks

Head over to the Transact-SQL forum for query related help.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

how to SET CONCAT_NULL_YIELDS_NULL OFF?

I'm writing a select query that's, in effect, like this: Select FirstName + ' ' + LastName FROM Users.

How do I get just the first name to display in my gridview if last name is null?

I'm familiar with aspx and aspx.cs files, but stored procedures are beyond me right now.

You need to use ISNULL or COALESCE.

Select FirstName + ' ' + ISNULL(LastName,'') FROM Users

how to set a database to readwrite mode

Database was copied to another location in read only mode using enterprise manager. how can you reverse this using query analyzer in the new location?master.dbo.sp_dboption N'<DB Name>', N'read only', N'false'

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through ODBC Client and use ACCESS to query database ,some client can use SQL server 2000 client to manage the database. Now I don't want the ODBC client install SQL client and connect to
SQL server 2000. I have install a host firewall in SQL server,but because ODBC client and SQL client can use two way (name pipe and TCP port 1433),so I also can't reject ODBC client use SQL client. Does someone any suggestion.
Depending on your environment several possibilites come to mind.
You can take out named pipe connections all together and only allow TCP/IP connections to your SQL server. Client DSNs used by Access are frequently set up to used named pipe connections instead of TCP/IP.
You can reassing SQL to use some other than the standard port '1433' - some higher number preferably.
You can hide the SQL port all together (Using Server Network Utility) This will most certainly also cause disruption for the already existing SQL client connections. You can also just monitor the current connections to your SQL server and isolate the non
SQL clients one at a time by isolating their connection properties.
|||Many thanks Sassan!
I can disable the port 1433 or name pipe, but I must enable the client connect to the SQL Server use ODBC, but how can I limit the user only connect the SQL server through ODBC, and reject the sql client connect one the same computer.
|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through O
DBC Client and use ACCESS to query database ,some client can use SQL server
2000 client to manage the database. Now I don't want the ODBC client install
SQL client and connect to
SQL server 2000. I have install a host firewall in SQL server,but because OD
BC client and SQL client can use two way (name pipe and TCP port 1433),so I
also can't reject ODBC client use SQL client. Does someone any suggestion.Depending on your environment several possibilites come to mind.
You can take out named pipe connections all together and only allow TCP/IP c
onnections to your SQL server. Client DSNs used by Access are frequently set
up to used named pipe connections instead of TCP/IP.
You can reassing SQL to use some other than the standard port '1433' - some
higher number preferably.
You can hide the SQL port all together (Using Server Network Utility) This
will most certainly also cause disruption for the already existing SQL clien
t connections. You can also just monitor the current connections to your SQL
server and isolate the non
SQL clients one at a time by isolating their connection properties.|||Many thanks Sassan!
I can disable the port 1433 or name pipe, but I must enable the client conne
ct to the SQL Server use ODBC, but how can I limit the user only connect th
e SQL server through ODBC, and reject the sql client connect one the same co
mputer.|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through ODBC Client and use ACCESS to query database ,some client can use SQL server 2000 client to manage the database. Now I don't want the ODBC client install SQL client and connect to SQL server 2000. I have install a host firewall in SQL server,but because ODBC client and SQL client can use two way (name pipe and TCP port 1433),so I also can't reject ODBC client use SQL client. Does someone any suggestion.Depending on your environment several possibilites come to mind
You can take out named pipe connections all together and only allow TCP/IP connections to your SQL server. Client DSNs used by Access are frequently set up to used named pipe connections instead of TCP/IP
You can reassing SQL to use some other than the standard port '1433' - some higher number preferably
You can hide the SQL port all together (Using Server Network Utility) This will most certainly also cause disruption for the already existing SQL client connections. You can also just monitor the current connections to your SQL server and isolate the non SQL clients one at a time by isolating their connection properties.|||Many thanks Sassan
I can disable the port 1433 or name pipe, but I must enable the client connect to the SQL Server use ODBC, but how can I limit the user only connect the SQL server through ODBC, and reject the sql client connect one the same computer.|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

how to send query from SQL Server using post method

Hello Everyone
i am new to SQL Server. i have a problem. i have to send a query string
to a server using post method from a stored procedure.
i got a stored procedure in some group for sending Http Post Request
from Stored Procedure. but i dont know how to pass parameters and Http
Header Information.
i have to send 4 parameters (Control name are)
1. Name (character string)
2. msisdn (Numeric string like Mobile No.)
3. toAddress (Numaric String like Mobile No.)
4. message (character string)
Plz can anyone help me.
the HTTP_post stored procedure which i found at some group is as
follows:
****************************************
**************************
CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
out)
As
Declare
@.obj int
,@.hr int
,@.status int
,@.msg varchar(255)
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
'Content-Type', 'application/x-www-form-urlencoded'
if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
goto eh end
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
eh
end
if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
str(@.status) goto eh end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
eh end
exec @.hr = sp_OADestroy @.obj
return
eh:
exec @.hr = sp_OADestroy @.obj
Raiserror(@.msg, 16, 1)
return
GO
****************************************
**************************Hi,
I think you need to modify this bit to add the parameters in...
exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
change to...
declare @.parms varchar(500)
set @.parms = 'Var1=' + @.yourparm + '&Var2=' + @.yourparm2
exec @.hr = sp_OAMethod @.obj, 'send', NULL, @.parms
if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<vineet.jsl@.gmail.com> wrote in message
news:1130749924.513462.309240@.f14g2000cwb.googlegroups.com...
> Hello Everyone
> i am new to SQL Server. i have a problem. i have to send a query string
> to a server using post method from a stored procedure.
> i got a stored procedure in some group for sending Http Post Request
> from Stored Procedure. but i dont know how to pass parameters and Http
> Header Information.
> i have to send 4 parameters (Control name are)
> 1. Name (character string)
> 2. msisdn (Numeric string like Mobile No.)
> 3. toAddress (Numaric String like Mobile No.)
> 4. message (character string)
> Plz can anyone help me.
> the HTTP_post stored procedure which i found at some group is as
> follows:
> ****************************************
**************************
> CREATE procedure HTTP_POST( @.sUrl varchar(200), @.response varchar(8000)
> out)
> As
>
> Declare
> @.obj int
> ,@.hr int
> ,@.status int
> ,@.msg varchar(255)
>
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @.obj OUT
> if @.hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
> failed', 16,1) return end
>
> exec @.hr = sp_OAMethod @.obj, 'open', NULL, 'POST', @.sUrl, false
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Open failed' goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'setRequestHeader', NULL,
> 'Content-Type', 'application/x-www-form-urlencoded'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod setRequestHeader failed'
> goto eh end
>
> exec @.hr = sp_OAMethod @.obj, 'send', NULL, 'Var1=Test1&Var2=Test2'
> if @.hr <>0 begin set @.msg = 'sp_OAMethod Send failed' goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read status failed' goto
> eh
> end
>
> if @.status <> 200 begin set @.msg = 'sp_OAMethod http status ' +
> str(@.status) goto eh end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <>0 begin set @.msg = 'sp_OAMethod read response failed' goto
> eh end
>
> exec @.hr = sp_OADestroy @.obj
> return
>
> eh:
> exec @.hr = sp_OADestroy @.obj
> Raiserror(@.msg, 16, 1)
> return
> GO
> ****************************************
**************************
>|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL but this time i have to
send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate|||First, very very thank you for replying and giving me suggestion.
second, again i have another problem just like the first one.
now i have to send http post request to an URL from stored procedure
but this time i have to send data in XML Format
the data is
<ERECHARGE>
<TRANS_ID>Vendor's Transaction ID </TRANS_ID>
<SOURCE_ID> Vendor's Name </SOURCE_ID>
<MDN> Vendor's MDN </MDN>
<DEALER_ID> Vendor's Dealer ID </DEALER_ID>
<REQUEST>
<REQUEST_TYPE>ERECHARGE</REQUEST_TYPE>
<ATTRIBUTES>
<ATTRIBUTE>
<NAME>TARGET_MDN</NAME>
<VALUE>MDN where recharge amount to be transferred </VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>AMOUNT</NAME>
<VALUE>Recharge Amount</VALUE>
</ATTRIBUTE>
<ATTRIBUTE>
<NAME>PIN</NAME>
<VALUE>Unique PIN given to distributor on registration</VALUE>
</ATTRIBUTE>
</ATTRIBUTES>
</REQUEST>
</ERECHARGE>
i dont know what approach i shud take.
Any help will be appreciate