Query Question

Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amount
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
--
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:
> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are having
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
--
David Portas
SQL Server MVP
--

Query Question

Greetings,
I am new to SQL Server and am trying to write a query for an application I
am working on. The table I am working with has three dollar amounts in
seperate columns that are prices on the same product from multiple
distributors. I am having trouble building a query that does a comparison on
these fields and returns a list of records based upon the lowest dollar amou
nt
The results would be then used to populate a new table using only the lowest
price as returned from the SELECT statement
Is there someone who might be able to point me to some sample code that I
could use to help me figure out how this could be written
Thanks,
Joe.SELECT col1, col2, col3, ...
(SELECT MIN(price)
FROM
(SELECT price1 AS price UNION ALL
SELECT price2 UNION ALL
SELECT price3) AS T) AS min_price
FROM YourTable
The three price columns collectively represent a "repeating group". In
relational design this is a serious error and the difficulty you are having
is a consequence of the design problem. Hopefully your intention is to fix
this.
David Portas
SQL Server MVP
--|||David,
I assume that MIN will still work in the same manner if the price values
being compared are in seperate tables. I am working on different ways to get
rid of the repeating problem.
Thanks for your help
Joe.
"David Portas" wrote:

> SELECT col1, col2, col3, ...
> (SELECT MIN(price)
> FROM
> (SELECT price1 AS price UNION ALL
> SELECT price2 UNION ALL
> SELECT price3) AS T) AS min_price
> FROM YourTable
> The three price columns collectively represent a "repeating group". In
> relational design this is a serious error and the difficulty you are havin
g
> is a consequence of the design problem. Hopefully your intention is to fix
> this.
> --
> David Portas
> SQL Server MVP
> --
>
>|||MIN retrieves the lowest non-NULL value of a set. If you can join the
additional table into the query then you should be able to make use of MIN.
My point about your design was that it would be easier and more efficient to
do this if your design was correctly normalized.
David Portas
SQL Server MVP
--

Query performance.

I am working at a client site with an accounting database, and we are dealing with a simple query:

select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID

Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.

Our test server is an exact restore of our production server.

Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.

The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).

Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.

Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.

The problem occurs on our production server regardless of whether other users are using the system.

I have a couple questions:

1) Any ideas on why the query would run so much slower on our production server?

2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.

3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?

Thanks!

blindmanRunning trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
--------------
Check indexes for fragmentation.|||I tried dropping and recreating the indexes, to no effect.

I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.

I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.

blindman|||Originally posted by blindman
I tried dropping and recreating the indexes, to no effect.

I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.

I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.

blindman

Try to use performance monitor - very useful thing. Compare results for servers.|||uhhh...bounce the box?

This is MS BTW

Can you be down for a couple?

What's in the Error Log?

Did you do DBCC CHECKDB?|||blocking ?

Originally posted by blindman
I am working at a client site with an accounting database, and we are dealing with a simple query:

select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID

Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.

Our test server is an exact restore of our production server.

Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.

The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).

Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.

Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.

The problem occurs on our production server regardless of whether other users are using the system.

I have a couple questions:

1) Any ideas on why the query would run so much slower on our production server?

2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.

3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?

Thanks!

blindman|||Bouncing the server did speed up the processing the other day, but the performance quickly degraded again.

Problem with buffer pages, perhaps?

Nothing in the Error Log.

DBCC checks done regularly, and databases are spittin' images of eachother.

No blocks detected, and problem occurs regardless of whether other users are logged in.

blindman|||How big are the tranny logs?

How about the allocation to tempdb?

Is it a high level of OLTP?

But..

The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).

Should be an Index Scan...shouldn't it?

And 45 minutes is a long time....(7 hours is an eternity)...

Can it be the network? Are you executing localy on dev and remotley to PROD (even that shouldn't matter)

I'll keep thinking...

You can try the brain trust at:

http://www.sqlteam.com/Default.asp|||I know SQL Server is supposed to be doing this on its own every now and then, but what happens when you run

update statistics table with fullscan

on both tables? Kind of reaching here, because the test database is a restore of the other (and therefore identical). How does the hardware stack up?|||How does the hardware stack up?

...And if I read the REST of the post... Disregard that last bit, Blindman.

Query Parallelism Configuration

I am working with a SQL Server 7 (SP4) box with 8 processors. When I
bring up Enterprise Manager and go to the Processor tab under server
properties, I can see the configuration for the parallel execution of
queries. In the list box, it is set to use only 1 processor. I set
it to use 2 processors, and click Apply, and then OK. When I go back
to the dialog box, it is set to use only 1 processor again, as if I
had never set the option. When I run sp_configure, it would indicate
that I have, in fact, set the parallel execution to use 2 processors
(max degree of parallelism = 2). It's only from the processor tab
that I think I've only set it to use 1 processor.
Is there a way I can verify that 2 processors are getting used in the
query parallelism? Or is it possible that another configuration
setting is preventing this from happening?
Here is the complete sp_configure, if it helps:
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 2 2
max server memory (MB) 4 2147483647 5500 5500
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 500 500
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 5500 5500
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
spin counter 1 2147483647 20000 20000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0Sp_configure looks right. Might be a bug in EM?
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"AAAWalrus" <aaawalrus@.yahoo.com> wrote in message
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||You can either inspect the query execution plan (should see parallelism for
queries/subqueries that cost more than 5)
or/and to take a look in Processor Usage in Performance Monitor if you can
afford to be the only one to make traffic on the server
at certain time
Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> bring up Enterprise Manager and go to the Processor tab under server
> properties, I can see the configuration for the parallel execution of
> queries. In the list box, it is set to use only 1 processor. I set
> it to use 2 processors, and click Apply, and then OK. When I go back
> to the dialog box, it is set to use only 1 processor again, as if I
> had never set the option. When I run sp_configure, it would indicate
> that I have, in fact, set the parallel execution to use 2 processors
> (max degree of parallelism = 2). It's only from the processor tab
> that I think I've only set it to use 1 processor.
> Is there a way I can verify that 2 processors are getting used in the
> query parallelism? Or is it possible that another configuration
> setting is preventing this from happening?
> Here is the complete sp_configure, if it helps:
> affinity mask 0 2147483647 0 0
> allow updates 0 1 1 1
> cost threshold for parallelism 0 32767 5 5
> cursor threshold -1 2147483647 -1 -1
> default language 0 9999 0 0
> default sortorder id 0 255 52 52
> extended memory size (MB) 0 2147483647 0 0
> fill factor (%) 0 100 0 0
> index create memory (KB) 704 1600000 0 0
> language in cache 3 100 3 3
> language neutral full-text 0 1 0 0
> lightweight pooling 0 1 0 0
> locks 5000 2147483647 0 0
> max async IO 1 255 32 32
> max degree of parallelism 0 32 2 2
> max server memory (MB) 4 2147483647 5500 5500
> max text repl size (B) 0 2147483647 65536 65536
> max worker threads 10 1024 500 500
> media retention 0 365 0 0
> min memory per query (KB) 512 2147483647 1024 1024
> min server memory (MB) 0 2147483647 5500 5500
> nested triggers 0 1 1 1
> network packet size (B) 512 65535 4096 4096
> open objects 0 2147483647 0 0
> priority boost 0 1 1 1
> query governor cost limit 0 2147483647 0 0
> query wait (s) -1 2147483647 -1 -1
> recovery interval (min) 0 32767 0 0
> remote access 0 1 1 1
> remote login timeout (s) 0 2147483647 5 5
> remote proc trans 0 1 0 0
> remote query timeout (s) 0 2147483647 0 0
> resource timeout (s) 5 2147483647 10 10
> scan for startup procs 0 1 0 0
> set working set size 0 1 1 1
> show advanced options 0 1 1 1
> spin counter 1 2147483647 20000 20000
> time slice (ms) 50 1000 100 100
> two digit year cutoff 1753 9999 2049 2049
> Unicode comparison style 0 2147483647 196609 196609
> Unicode locale id 0 2147483647 1033 1033
> user connections 0 32767 0 0
> user options 0 4095 0 0|||the best way is to use profiler, capture the classes for degree of
parallelism.
BTW, just having a cost of 5 does not guarentee parallism, only certain
query plan operators can be run in parallel.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tomasz" <tp@.nospam.com> wrote in message
news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> You can either inspect the query execution plan (should see parallelism
for
> queries/subqueries that cost more than 5)
> or/and to take a look in Processor Usage in Performance Monitor if you can
> afford to be the only one to make traffic on the server
> at certain time
> Uzytkownik "AAAWalrus" <aaawalrus@.yahoo.com> napisal w wiadomosci
> news:8b266bc2.0311110819.3541dc88@.posting.google.com...
> > I am working with a SQL Server 7 (SP4) box with 8 processors. When I
> > bring up Enterprise Manager and go to the Processor tab under server
> > properties, I can see the configuration for the parallel execution of
> > queries. In the list box, it is set to use only 1 processor. I set
> > it to use 2 processors, and click Apply, and then OK. When I go back
> > to the dialog box, it is set to use only 1 processor again, as if I
> > had never set the option. When I run sp_configure, it would indicate
> > that I have, in fact, set the parallel execution to use 2 processors
> > (max degree of parallelism = 2). It's only from the processor tab
> > that I think I've only set it to use 1 processor.
> >
> > Is there a way I can verify that 2 processors are getting used in the
> > query parallelism? Or is it possible that another configuration
> > setting is preventing this from happening?
> >
> > Here is the complete sp_configure, if it helps:
> >
> > affinity mask 0 2147483647 0 0
> > allow updates 0 1 1 1
> > cost threshold for parallelism 0 32767 5 5
> > cursor threshold -1 2147483647 -1 -1
> > default language 0 9999 0 0
> > default sortorder id 0 255 52 52
> > extended memory size (MB) 0 2147483647 0 0
> > fill factor (%) 0 100 0 0
> > index create memory (KB) 704 1600000 0 0
> > language in cache 3 100 3 3
> > language neutral full-text 0 1 0 0
> > lightweight pooling 0 1 0 0
> > locks 5000 2147483647 0 0
> > max async IO 1 255 32 32
> > max degree of parallelism 0 32 2 2
> > max server memory (MB) 4 2147483647 5500 5500
> > max text repl size (B) 0 2147483647 65536 65536
> > max worker threads 10 1024 500 500
> > media retention 0 365 0 0
> > min memory per query (KB) 512 2147483647 1024 1024
> > min server memory (MB) 0 2147483647 5500 5500
> > nested triggers 0 1 1 1
> > network packet size (B) 512 65535 4096 4096
> > open objects 0 2147483647 0 0
> > priority boost 0 1 1 1
> > query governor cost limit 0 2147483647 0 0
> > query wait (s) -1 2147483647 -1 -1
> > recovery interval (min) 0 32767 0 0
> > remote access 0 1 1 1
> > remote login timeout (s) 0 2147483647 5 5
> > remote proc trans 0 1 0 0
> > remote query timeout (s) 0 2147483647 0 0
> > resource timeout (s) 5 2147483647 10 10
> > scan for startup procs 0 1 0 0
> > set working set size 0 1 1 1
> > show advanced options 0 1 1 1
> > spin counter 1 2147483647 20000 20000
> > time slice (ms) 50 1000 100 100
> > two digit year cutoff 1753 9999 2049 2049
> > Unicode comparison style 0 2147483647 196609 196609
> > Unicode locale id 0 2147483647 1033 1033
> > user connections 0 32767 0 0
> > user options 0 4095 0 0
>|||I am chalking this up to a bug in the SQL 7 Enterprise Manager. When
I view the same option on the SQL 7 database from SQL 2000 Enterprise
Manager (personal edition installed on my PC), it properly updates and
shows the number of processors to use on parallel queries. What makes
me a little worried, though, is that I have not found a relative MS
support article. I've been contemplating calling MS support about it,
but that's a whole other set of hassles.
Thanks for your help!
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message news:<#JdolkHqDHA.1676@.TK2MSFTNGP09.phx.gbl>...
> the best way is to use profiler, capture the classes for degree of
> parallelism.
> BTW, just having a cost of 5 does not guarentee parallism, only certain
> query plan operators can be run in parallel.
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Tomasz" <tp@.nospam.com> wrote in message
> news:efKp2EHqDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > You can either inspect the query execution plan (should see parallelism
> for
> > queries/subqueries that cost more than 5)
> > or/and to take a look in Processor Usage in Performance Monitor if you can
> > afford to be the only one to make traffic on the server
> > at certain time

query optimisation question

I have a quick question regarding a query I'm working on. I was wondering why the first one runs much quicker than the second as I can't understand it myself.

Query 1:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1

Query 2:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1

The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?

thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!

Query on XML column: fn:lower-case not working?

Hi everybody, I'm looking to issue a query to an XML column like this:
SELECT *
FROM dbo.ContactRecords
WHERE XmlContent.exist(
'declare namespace
my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2004-03-09T14-09
-40";
// my:myFields[my:AddressInfo[fn:contains(f
n:lower-case(my:Company[1]),"lego"
)]]')
= 1
(i skipped some other conditions, but they are working)
--> SQL 2005 tells me: There is no function
'{http://www.w3.org/2004/07/xpath-functions}:lower-case()'
however, there is one...
How can I compare string values in SQL 2005 case-insensitive?
I appreciate your help, have a good day,
MichaelHello michael.hofer@.getronics.com,
Well, you've stumbled into the chasm caused by Microsoft trying to get a
product released and the W3C XQuery WG trying to get things perfect the firs
t
time. There's a number of functions that just aren't implemented in MS's
XQuery heap but are in the spec. We might get them in service packs, or we
might get them in the next version of SQL Server. String-Upper and String-L
ower
are a couple of the common ones that we'd like to have but don't.
Interestingly enough, I see that contains() has a optional collation pattern
.
I try to find a collation that ignored case to test with but didn't have
much immediate luck. If you might want to go down that path. Or not. No prom
ises.
I'd like to offer a suggestion other than .value(path,'varchar(n)') like
'%pattern%' (e.g., do the comparsion in T-SQL as shown below) since this
is fairly ugly for the kind of stuff you're doing. But it does work.
declare @.x xml
set @.x = '<book>Ender''s Game</book><book>Xenocide</book><book>Children of
the Mind</book><book>The Hive Queen</book><book>The Hegemon</book>'
select p.x.value('.','varchar(100)')
from @.x.nodes('/book') as p(x)
where p.x.value('.','varchar(100)') like '%the%'
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

query on recursive table

Hi,
Suppose I'm working on a database containing 2 tables:
tStudent (student information)
========
|--|
| studentID | studentName | deptID |
|--|
| 504001 | John Doe | 10001 |
| 504005 | JI Jane | 200 |
|--|
tItems (organisational information)
======
|----|
| itemID | KindOfItem | itemName | parentID |
|----|
| 1 | top | top | <NULL> |
| 10 | school | Univ of Geel | 1 |
| 11 | school | Univ of Antwerp | 1 |
| 12 | school | Univ of Brussels | 1 |
| 100 | dept | science dept | 10 |
| 101 | dept | human science dept | 10 |
| 200 | dept | agriculture dept | 11 |
| 1001 | dept | computer science dept | 100 |
| 10001 | dept | Windows comp science dept | 1001 |
| 10002 | dept | Unix comp science dept | 1001 |
|----|
I need to write a query using Reporting Services on a MS SQL server that
gives me for every student the dept he is in (that's the easy part), but
also the top level school the department resides under. Result should be
like:
504001, John Doe , Windows comp science dept, Univ of Geel
504005, JI Jane, Agriculture dept, Univ of Antwerp
Can anybody give me any clues on how to do this (if possible)?
ErikWhy not add the parent_id for say Univ of Geel is 1 to a new column in the
first table and use an update to achieve this. Then you all you need is a
simple equi-join.
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||You didn't specify if the dept of the recursion is variable. Assuming
it is not then:
select x.studentID, x.studentName, y.DeptName, y.School
from tStudent x,
(
select a.deptID, a.itemName as DeptName, b.itemName as School
from tItems a, tItems b
where a.parentID = b.deptID
) y
where x.deptID = y.deptID
Ranny

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
User submitted from AEWNET (http://www.aewnet.com/)|||This smells more like homework instead of real world scenario, but I'll play
along! how about this:
set nocount on
declare @.organization table (itemid int, kindofitem varchar(20), itemname
varchar(30), parentid int, topparentid int)
declare @.studentinfo table (studentid int, studentname varchar(30), deptid
int)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10, 'school', 'Univ of Geel', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(11, 'school', 'Univ of Antwerp', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(12, 'school', 'Univ of Brussels', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(100, 'dept', 'science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(101, 'dept', 'human science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(200, 'dept', 'agriculture dept', 11)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(1001, 'dept', 'computer science dept', 100)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10001, 'dept', 'Windows comp science dept', 1001)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10002, 'dept', 'Unix comp science dept', 1001)
insert into @.studentinfo (studentid, studentname, deptid) values(504001,
'John Doe', 10001)
insert into @.studentinfo (studentid, studentname, deptid) values(504005, 'JI
Doe', 200)
update @.organization set topparentid = itemid where parentid = 1
while 1 = 1
begin
update @.organization
set topparentid = k.topparentid
from @.organization o
join (select itemid, topparentid from @.organization where topparentid is
not null) k
on o.parentid = k.itemid
where o.topparentid is null
if @.@.rowcount = 0
break
end
select * from @.organization
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||@.aew_nospam.com schreef:
> You didn't specify if the dept of the recursion is variable. Assuming
> it is not then:
It IS variable, as you can see from the example:
Student JI Jane resides under dept "agriculture dept", that resides
directly under school "Univ of Antwerp".
Student John Doe resides under dept "Windows comp sc dept", that resides
under dept "computer science dept" that resides under dept "science
dept" that resides under school "Univ of Geel".

> select x.studentID, x.studentName, y.DeptName, y.School
> from tStudent x,
> (
> select a.deptID, a.itemName as DeptName, b.itemName as School
> from tItems a, tItems b
> where a.parentID = b.deptID
> ) y
> where x.deptID = y.deptID
> Ranny
>
>
>
> User submitted from AEWNET (http://www.aewnet.com/)|||
> This smells more like homework instead of real world scenario, but I'll pl
ay
> along! how about this:
Please have your nose checked, because this is no homework but
real-world scenario :) It is part of a student administration system
we're implementing in a group of schools.
My job is to create report(s) from the database. The report mentionned
in OP should have the data on the student, the dept he works in and the
school this dept belongs to. Hope it's clear now :)
> set nocount on
> declare @.organization table (itemid int, kindofitem varchar(20), itemname
> varchar(30), parentid int, topparentid int)
> declare @.studentinfo table (studentid int, studentname varchar(30), deptid
> int)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10, 'school', 'Univ of Geel', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(11, 'school', 'Univ of Antwerp', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(12, 'school', 'Univ of Brussels', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(100, 'dept', 'science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(101, 'dept', 'human science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(200, 'dept', 'agriculture dept', 11)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(1001, 'dept', 'computer science dept', 100)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10001, 'dept', 'Windows comp science dept', 1001)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10002, 'dept', 'Unix comp science dept', 1001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504001,
> 'John Doe', 10001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504005, '
JI
> Doe', 200)
> update @.organization set topparentid = itemid where parentid = 1
> while 1 = 1
> begin
> update @.organization
> set topparentid = k.topparentid
> from @.organization o
> join (select itemid, topparentid from @.organization where topparentid i
s
> not null) k
> on o.parentid = k.itemid
> where o.topparentid is null
> if @.@.rowcount = 0
> break
> end
> select * from @.organization
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||marcmc schreef:
> Why not add the parent_id for say Univ of Geel is 1 to a new column in the
> first table and use an update to achieve this. Then you all you need is a
> simple equi-join.
Yes, that is true. But the information I need is available already in
the database, so adding the new column in the first table would make the
information redundant...
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Have you thought about designing a normalized schema which does not mix
data and metadata? This is a better approach in the long run than
looking for kludges. Just from this posting and the silly 'T-"
prefixes that you are an OO programmer no training in SQL. OO models do
not work with SQL.

Query Notification stops working after 10 minutes

Hi All

We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.

When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...

Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.

What can be the problem? is our Cache component object are being collected by GC?

Does IIS disposes the SQL Connection that the Query notification uses?

We are on a crisis...

Thanks in advance.

It sounds like there's something wrong with your server logic so this probably isn't the right newsgroup to ask. check the sys.dm_qn_subscriptions view to see if there is a subscription active. If not, then your service logic didn't create a new subscription after processing a notification. If it all worked in your test setup you might have an issue with re-entrancy in your component so maybe a lock is required to handle two notifications coming in so close together that only one suscription is registered.|||Could be the issue described in KB 913364: http://support.microsoft.com/Default.aspx?kbid=913364

HTH,
~ Remus|||

I dont see how can this be a problem on the server logic...

I am running the same code in my Console application and in my web application , the context is the difference.

I ran a check to see if the Cache object is disposed , and it doesnt, meaning the Cache component is alive , but notifications are not coming after 10 minutes,

I'll download the fix in the KB , and try it tomorrow morning...I hope this would do the trick.

Eden

|||

Well , Remus, you are right...

The FIX that i've installed on my station solved the problem (I Hope)

Notification events are coming after several hours without any problems...

Just to be sure I'll test it with a few days delay to see if every thing is ok....

I think that my issue is a big bug , and microsoft should publish it approprietly appropriately,

It took my several days to get to this forum , and to Remus answer,

Anyhow , thanks to all of you.

Query Notification & Windows Service

I could get Query Notification working for a windows forms client using the
SQL Dependency object but the same code doesn't work from a simple windows
service.
The OnChange event doesn't seem to be raised up to the windows service.
Looking at the SQL trace, it doesn't look like the a notification is sent to
the windows service from the SQL server.
Appreciate your response.
RamaThe notifications are sent using Service Broker, try following the steps in
this post http://blogs.msdn.com/remusrusanu/a.../20/506221.aspx
to figure out the cause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rama" <rama.bhandaru@.eclipsys.com> wrote in message
news:en6X%23S$XGHA.1192@.TK2MSFTNGP03.phx.gbl...
>I could get Query Notification working for a windows forms client using the
> SQL Dependency object but the same code doesn't work from a simple windows
> service.
> The OnChange event doesn't seem to be raised up to the windows service.
> Looking at the SQL trace, it doesn't look like the a notification is sent
> to
> the windows service from the SQL server.
> Appreciate your response.
> Rama
>

Query Not Working Right!

Hello All,
I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. ThesearchString,Typeand theLocation (where the user who is searching belongs to).
When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.

For example:
Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c.
So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters.
Any help would be greatfull.

Query:

SELECT
Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate

FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID

WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @.SR + '%' OR [Client.SSN] LIKE '%' + @.SR + '%' OR [Client.LastName] LIKE '%' + @.SR + '%' OR [Client.FirstName] LIKE '%' + @.SR + '%' OR [Client.MiddleName] LIKE '%' + @.SR + '%' )

The last search criteria in your query will return rows when anyone of the[ClientDocuments.LeafName]/[Client.SSN]/[Client.LastName]/ [Client.FirstName][Client.MiddleName] contains@.SR, right? Make sure you need to do such a search, because there are so many fields to be searched so maybe some field meets the criteria while you're not aware of this. BTW, LIKE '%' + @.SR + '%'means any string contains@.SR, not only string which starts with@.SR|||

Start by either deleting all your brackets or use them correctly. Brackets are for quoting identifiers. The period in Client.MiddleName separates two identifiers (Table Name, and Field Name). [Client.MiddleName] would be a field named... Client.MiddleName. It's incorrect usage, and while it might fly sometimes, you should fix it. It's possibly causing the SQL Parser to go a little crazy.

The brackets as is are not necessary since neither Client nor MiddleName, etc have any characters in it that would otherwise be illegal without. You specify it as either Client.MiddleName or [Client].[MiddleName], but not [Client.MiddleName].

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

|||

Motley:

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

Haha, Hey I did not even see that, yeah it is a typo. Anyways, I will try breaking out the brackets. But do you think that the brackets would have cause the query to act funny?|||Looking it over, it appears to me that everything else is ok, but then again, sometimes I miss the obvious. Your logic is sound atleast.|||

Hey Thanks everyone for there help. I got the query to work perfectly.

Thanks again.

query not working

Hi
look at the queries
1.
Examination_timetable consists of rows like this
ClassId ExamDate SubId Invigilation
CLD00001 3/12/2006 SUB00001 STA00001,STA00002
select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001'
then i got following correct answer which is correct.
invigilation
STA00001,STA00002
2.
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
('STA00001','STA00002')
then i didnt get any rows which is also correct, since except
STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
teaching staff's id's).
3.
now i joine teh both 1st and 2nd queries like this
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
(select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001')
then i'm getting result like this, whics is wrong result
StaffId
STA00001
STA00002
it shouldn't display any rows since no rows matches the given condition.
why it is displaying wrong answer when i mixed 2 separates queries in to
single query, where both individually giving correct answer?
hope you got my query.
can any one tell me where i went wrong?
thanx in advance
yoshithaHow about
select staffid from staff_details INNER JOIN faculty_type
ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
Examination_timetable ON staff_details.staffid NOT LIKE '%' +
Examination_timetable.Invigilation + '%'
where facultytype='teaching'
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||Your usage of IN is wrong. When you get the "invigilationID", it is a comma
separated string. However, for IN to search through this, you cannot
substitute this string directly for the IN clause, but rather, you need to
do it dynamically. Here is an example that shows how it does not work:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN 'Test1' IN (@.testString) THEN 1 ELSE 0 END
=====
When you execute the above snippet, you will always get 0 (although Test1 is
present inside the string). To get around this problem, you can do the
following
(1) Use dynamic SQL to build your query as shown:
=====
DECLARE @.testString VARCHAR(100)
DECLARE @.strSQL VARCHAR(8000)
SET @.testString = '''Test1'', ''Test2'''
SET @.strSQL = 'SELECT CASE WHEN ''Test1'' IN (' + @.testString + ') THEN 1
ELSE 0 END'
EXEC (@.strSQL)
=====
(2) Use CHARINDEX as shown:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN CHARINDEX('Test1', @.testString) > 0 THEN 1 ELSE 0 END
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||still it is displaying both ids.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> How about
> select staffid from staff_details INNER JOIN faculty_type
> ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
> Examination_timetable ON staff_details.staffid NOT LIKE '%' +
> Examination_timetable.Invigilation + '%'
> where facultytype='teaching'
>
>
> "yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
> news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||Well, can you post DDL+ sample data + expected result for both tables?
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:e9CoRrHMGHA.2992@.tk2msftngp13.phx.gbl...
> still it is displaying both ids.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Query not using clustered index

I am working with some software vendors with sql query performance on their application that we installed. On one of the tables there are four indexes, one is clustered. When the vendor restores a copy of our database and run a query against that table i
t uses the clustered index and completes in .02 seconds. When I run the same query on the same database it uses a different index than the clustered index and takes 1.7 seconds to run. Is there some way I can get this query to use the clustered index to
run faster? I have a maintinence plan that runs once a week to rebuild the indexes and update statistics using 50%. Any ideas would be appreciated.
When you say the same query on the same database are you talking the same
machine? If so what is different in the way you run the query than when he
does?
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> I am working with some software vendors with sql query performance on
their application that we installed. On one of the tables there are four
indexes, one is clustered. When the vendor restores a copy of our database
and run a query against that table it uses the clustered index and completes
in .02 seconds. When I run the same query on the same database it uses a
different index than the clustered index and takes 1.7 seconds to run. Is
there some way I can get this query to use the clustered index to run
faster? I have a maintinence plan that runs once a week to rebuild the
indexes and update statistics using 50%. Any ideas would be appreciated.
|||When you say the same query on the same database are you talking the same
machine? If so what is different in the way you run the query than when he
does?
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> I am working with some software vendors with sql query performance on
their application that we installed. On one of the tables there are four
indexes, one is clustered. When the vendor restores a copy of our database
and run a query against that table it uses the clustered index and completes
in .02 seconds. When I run the same query on the same database it uses a
different index than the clustered index and takes 1.7 seconds to run. Is
there some way I can get this query to use the clustered index to run
faster? I have a maintinence plan that runs once a week to rebuild the
indexes and update statistics using 50%. Any ideas would be appreciated.
|||It is on a different machine but the same database and indexes. We are both running the query in Query Analyzer.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||Any time you restore a database you should run Update statistics to ensure
they are all set. I would run it the same way on both systems and then see
if the results are the same.
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:242F4B00-1365-43C1-807D-882A72DC306E@.microsoft.com...
> It is on a differnet server, but it is the same database and indexes. We
are both using query analyzer to run the query.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
same[vbcol=seagreen]
he[vbcol=seagreen]
four[vbcol=seagreen]
database[vbcol=seagreen]
completes[vbcol=seagreen]
a[vbcol=seagreen]
Is[vbcol=seagreen]
appreciated.[vbcol=seagreen]
|||It is on a different machine but the same database and indexes. We are both running the query in Query Analyzer.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>

query no longer working?

UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID =
ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name] WHERE
(((Len([name]))>0))
When I try to run this I get the error message
An Error occurred while executing query: Inccorrect syntax near the keyword
‘inner’
We used to have local support that set this up we were able to run it once
when it was set up and was told we could re run it when ever we needed to
update the infor. Also was told they would send me a new query that would
update this info constantly. Never got the email they disappeared along with
$1000.00 in pre paid support.
Original goal was under a specific customer in the purchase history tab to
have the Customer name from the ship to tab appear in the Reference # field
in the purchase history tab
Any help would be appreciatedHi
Did you ever get this query working?
(untested)
UPDATE PUBLIC_PUBLIC_Transaction SET ReferenceNumber=(SELECT
[dbo_ShipTo].[Name]
FROM ShipTo JOIN PUBLIC_Transaction ON PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0 )
WHERE EXISTS (SELECT * FROM ShipTo JOIN PUBLIC_Transaction ON
PUBLIC_Transaction.ShipToID =
ShipTo.ID WHERE Len([name])>0)
"shoeman240" <shoeman240@.discussions.microsoft.com> wrote in message
news:CA97C711-EFD2-49A1-9C08-3EDC81DB4CE4@.microsoft.com...
> UPDATE PUBLIC_Transaction INNER JOIN ShipTo ON PUBLIC_Transaction.ShipToID
> =
> ShipTo.ID SET PUBLIC_Transaction.ReferenceNumber = [dbo_ShipTo].[Name]
> WHERE
> (((Len([name]))>0))
>
> When I try to run this I get the error message
> An Error occurred while executing query: Inccorrect syntax near the
> keyword
> inner
> We used to have local support that set this up we were able to run it once
> when it was set up and was told we could re run it when ever we needed to
> update the infor. Also was told they would send me a new query that would
> update this info constantly. Never got the email they disappeared along
> with
> $1000.00 in pre paid support.
> Original goal was under a specific customer in the purchase history tab to
> have the Customer name from the ship to tab appear in the Reference #
> field
> in the purchase history tab
> Any help would be appreciated
>|||Hello,
This query never worked in SQL Server (it probably worked in Microsoft
Access). An equivalent T-SQL query would be:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = ShipTo.Name
FROM PUBLIC_Transaction INNER JOIN ShipTo
ON PUBLIC_Transaction.ShipToID = ShipTo.ID
WHERE Len(name)>0
And another equivalent query (in standard ANSI-SQL) is:
UPDATE PUBLIC_Transaction
SET ReferenceNumber = (
SELECT Name FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
) WHERE EXISTS (
SELECT * FROM ShipTo
WHERE PUBLIC_Transaction.ShipToID = ShipTo.ID
AND Len(name)>0
)
Razvan

query needed for path navigation through a web site

hi experts,
i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
is this possible and if not, what are the other approches that can i make to get the report i want.
also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
thanksHow do you determine the order of the pages visited? Are you storing timestamps or incrementally increasing IDs?|||hi,
thanks for the answer, actually i'm storing also the timestamp, and i think you have a point there, a timestamp is a good way to know the order, but i think a lot of information will be stored in the database if i decided to store the timestamp, do you have any other approach because at this time we can not afford tos tore a lot of information?
thanks|||A datetime column is not going to take up a lot of disk space. I can't think of any solution that would be cheaper in terms of bytes except an auto-incrementing identity surrogate key, and this is not the intended use of surrogate keys.|||there are plenty of 3rd party apps that do all this - you are reinventing the wheel if you code it yourself.

have you looked at google analytics?
http://www.google.com/analytics/|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.

what about the report for showing the returning visitors?
thanks|||I use google analytics to analyze traffic on my website. I find it very good, and it's free. You can do all sorts of stuff, like see where people are referred from, most common navigation paths they take through your site, etc.

Did you look at the link I provided?

also there are a ton of 3rd party apps for parsing web logs, many are free and the ones that aren't almost always have trial versions. all you need to do is look for them:

http://www.google.com/search?q=web+log+analysis|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.

what about the report for showing the returning visitors?
thanks
Okay, assuming you have the timestamp that will give you the order in which the pages were visited. Then you just need a way to differentiate between the different people/logins/connections that visit each page. What is your method of accomplishing this?|||thanks for your answers and sorry for being late to answer back.
i have all th information stored about the user, i have sessionid,siteid, i did not think of it yet, but in your point of view which one is the best?
any ideas?!!!
i will take a look also to the link you give me.
thanks|||So can't you just sort your data by sessionid and timestamp to get the order of the pages visited?|||hi,
can you give me an example or a query how to do it?
thanks|||select * from YourData order by SessionID, TimeStamp

What is the problem here?|||it seems too easy but i think you are right.
i will work on the report and i will post back if there is a problem, thaks for your help.
what about the report to store the IP's any idea about that.
thanks again and have a nice week end

Query Merging/ Query Transformation ??

Hello,

Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.

Plz help.
Shigs.
=============================
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
=============================Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Hi Satya

Thanks a lot. Query Merging that I referred was not related to merging of one table's data into another, but if I fire a query, which is containing a subquery in it, then the execution may take two different strategies, 1. Execute the subquery, fetch the result and compare this data with the parent querie's data. 2. Merge the subquery into Parent query and execute them as if the query wasn't subquery but a single level '0' query.

The best xample is Oracle's COMPLEX_QUERY_MERGING option. I want to understand, how Oarcle internally merges these queries? Is there any Optimizer Doc, which gives the detail?

Waiting...|||Is your question in regards to Oracle? This is a SQL Server forum, and SQL Server generally does a good job of optimizing queries, even with complex embedded subqueries.
If you are having slow response times on SQL Server, then post your query and someone on the forum might be able to help you optimize it.|||You can check the execution plan of that query in query analyzer which gives you better idea about the table scans and performance.|||I have a question...are there fewer and fewer Oracle projects out there these days?|||One can only hope...

Query logic not working...

I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_id

I'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.

SELECT
DISTINCT times.employee_id, employee.employee_name
FROM employee
INNER JOIN times ON employee.employee_id = times.employee_id
WHERE (times.job_id <> @.job_id)

Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!

Try a subquery:

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE employee_id not in
(SELECT employee_id FROM times
WHERE (job_id= @.job_id) )

|||That worked great, I've totally forgot about sub-queries. Thanks a lot Iori Jay.|||

OR

SELECT
DISTINCT employee_id, employee_name
FROM employee
WHERE not exists (SELECT employee_id FROM times
WHERE (job_id= @.job_id) )