I need some help with the @.LanguageId part of this query to exclude/include
rows to be returned.
ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
'nn'.
If rows with the specified @.LanguageId exists, then I want only those rows,
otherwise I want only the 'nn' rows (a fallback mechanism).
Now the query returns all the rows for both the specified @.LanguageId and
those with @.LanguageId = 'nn'. Any ideas?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
/tedHow about this?
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
@.LanguageId END
HTH,
Pierre
/"\ ASCII Ribbon Campaign
\ /
X Against HTML
/ \ in e-mail & news|||I'm afraid it will not solve the problem. @.LanguageId will always be
something as input parameter. The clue is that I want the 'nn' rows if the
specified 'dk' rows does not exist in the database.
/ted :)
"Pierre Albisser" <pierre.no_spam@.albisser.de> skrev i melding
news:%23079C5GXFHA.4032@.tk2msftngp13.phx.gbl...
> How about this?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND CONTENTREL.languageId = CASE WHEN @.LanguageId = '' THEN 'NN' ELSE
> @.LanguageId END
> --
> HTH,
> Pierre
> /"\ ASCII Ribbon Campaign
> \ /
> X Against HTML
> / \ in e-mail & news|||Try,
if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageId = @.LanguageId)
else
select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
@.ContentId AND (CONTENTREL.languageid = 'NN')
AMB
"ted" wrote:
> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Hi Ted
Probable you can try this way
SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND CONTENTREL.languageId = ISNULL(@.LanguageId,'NN')
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ted" wrote:
> I need some help with the @.LanguageId part of this query to exclude/includ
e
> rows to be returned.
> ctbl_content_rel can contain rows with different language ids, e.g 'dk' an
d
> 'nn'.
> If rows with the specified @.LanguageId exists, then I want only those rows
,
> otherwise I want only the 'nn' rows (a fallback mechanism).
> Now the query returns all the rows for both the specified @.LanguageId and
> those with @.LanguageId = 'nn'. Any ideas?
> SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> FROM ctbl_content_rel CONTENTREL
> JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
> WHERE CONTENT.pk_content = @.ContentId
> AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
> /ted
>
>|||Thanks :) It seems to be a way to go.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> skrev i melding
news:2E7D1FD3-54AC-4A40-87E4-8EA9EEFF5F6F@.microsoft.com...
> Try,
> if exists(select * from ctbl_content_rel CONTENTREL JOIN ctbl_content
> CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content WHERE
> CONTENT.pk_content = @.ContentId AND (CONTENTREL.languageId = @.LanguageId))
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageId = @.LanguageId)
> else
> select distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
> from ctbl_content_rel CONTENTREL JOIN ctbl_content CONTENT ON
> CONTENTREL.fk_content = CONTENT.pk_content WHERE CONTENT.pk_content =
> @.ContentId AND (CONTENTREL.languageid = 'NN')
>
> AMB
> "ted" 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.
Yoiu might also want to start following proper data modeling
conventions and stop using prefixes that tell us HOW something is used
in one table, and come with names that tell us what something is in the
whole data model. A prefix that tells us something is a table in a
language that hsa only one data strucutre is absurd.
See ISO-11179 for the standards. Can I assume that you meant to use
the ISO language codes and not a identifier?|||On Thu, 19 May 2005 14:19:58 +0200, ted wrote:
>I need some help with the @.LanguageId part of this query to exclude/include
>rows to be returned.
>ctbl_content_rel can contain rows with different language ids, e.g 'dk' and
>'nn'.
>If rows with the specified @.LanguageId exists, then I want only those rows,
>otherwise I want only the 'nn' rows (a fallback mechanism).
>Now the query returns all the rows for both the specified @.LanguageId and
>those with @.LanguageId = 'nn'. Any ideas?
>SELECT distinct CONTENTREL.fk_filegroup, CONTENTREL.languageid
>FROM ctbl_content_rel CONTENTREL
>JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
>WHERE CONTENT.pk_content = @.ContentId
>AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid = 'NN')
>/ted
>
Hi ted,
If you're not afraid of using proprietary SQL that won't port to other
databases, you can use this:
SELECT TOP 1 CONTENTREL.fk_filegroup, CONTENTREL.languageid
FROM ctbl_content_rel CONTENTREL
JOIN ctbl_content CONTENT ON CONTENTREL.fk_content = CONTENT.pk_content
WHERE CONTENT.pk_content = @.ContentId
AND (CONTENTREL.languageId = @.LanguageId OR CONTENTREL.languageid =
'NN')
ORDER BY CASE WHEN CONTENTREL.languageid = 'NN' THEN 2 ELSE 1 END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
Showing posts with label returned. Show all posts
Showing posts with label returned. Show all posts
Friday, March 30, 2012
query xml datatype
In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?
You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegro ups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?
You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegro ups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>
query xml datatype
In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegroups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegroups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>
Monday, March 26, 2012
Query with another query input parameter
Dear Friends,
I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.
SELECT DIR FROM DIRECCAO
BIG QUERY with DIR input parameter.
How can I do?
Thanks.
SELECT @.DIR = DIR FROM DIRECCAO
EXEC BIG_QUERY @.DIR
HTH,
Babu
|||IT WORKS AND THE QUERY IS:
ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]
AS
DECLARE @.DIR nvarchar(10)
SELECT @.DIR = DIR_NOME FROM Direccao
EXECUTE dbo.GD_SP_FACTURA_ValorTotal @.DIR
BUT How can I SUM all the values returned by the BIGQuery?
THANKS!!
|||How can I have the sum and it's possible to return a list of all values returned by the bigQuery? THANKS!!!|||Could anyone help me?
Thanks!
Wednesday, March 7, 2012
Query timeout when rows returned < TOP (n)?
SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".
Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.
The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.
Obviously there's something I'm missing, but I'm completely clueless as to what it is.
Thanks - James
Query 1:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
37759 ms
66 rows
IO Cost: 188.225
Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds
Query 2:
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')
ORDER BY date DESC
334 ms
100 rows
IO Cost: 188.225
It really depends on how many records there are of Attenna 1 and Antenna 2.
Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.
On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.
|||Query plans are your friend.
Look at the query plan and you will see the difference between both queries.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Yep query plans are the same for both. I've got indices for the other fields also.
The odd thing is with a if I give it a point name that doesn't exist like
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')
ORDER BY date DESC
It uses the index for point then hits the clustered index, but the same query with a valid point name
SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName
FROM history
WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')
ORDER BY date DESC
it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.
Thanks again - James
|||Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.
In the second query, it found 100 records very quickly so there was no need to continue.
If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).
Subscribe to:
Posts (Atom)