Need an example for CROSS APPLY!

Does anybody knows, how this cross apply works'
I need a simple example, because I have no idea what it's for!
(I can't found any good example in the net!?!?)
thanks in advance...This is not a great example as the query would be better done with a join,
but it should help you understand the syntax:
USE AdventureWorks
GO
CREATE FUNCTION GetLineItemsForOrder(@.SalesOrderID INT)
RETURNS TABLE
AS
RETURN
(
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @.SalesOrderID
)
GO
SELECT
SH.SalesOrderID,
LI.SalesOrderDetailID
FROM Sales.SalesOrderHeader SH
CROSS APPLY GetLineItemsForOrder(SH.SalesOrderID) LI
WHERE TotalDue > 20000
GO
DROP FUNCTION GetLineItemsForOrder
GO
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nullstring" <johannes.veit@.datapec.de> wrote in message
news:1127133941.948110.85450@.g44g2000cwa.googlegroups.com...
> Does anybody knows, how this cross apply works'
> I need a simple example, because I have no idea what it's for!
> (I can't found any good example in the net!?!?)
>
> thanks in advance...
>

Need an example for CROSS APPLY!

Does anybody knows, how this cross apply works?
I need a simple example, because I have no idea what it's for!
(I can't found any good example in the net!?!?)
thanks in advance...
This is not a great example as the query would be better done with a join,
but it should help you understand the syntax:
USE AdventureWorks
GO
CREATE FUNCTION GetLineItemsForOrder(@.SalesOrderID INT)
RETURNS TABLE
AS
RETURN
(
SELECT SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @.SalesOrderID
)
GO
SELECT
SH.SalesOrderID,
LI.SalesOrderDetailID
FROM Sales.SalesOrderHeader SH
CROSS APPLY GetLineItemsForOrder(SH.SalesOrderID) LI
WHERE TotalDue > 20000
GO
DROP FUNCTION GetLineItemsForOrder
GO
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"nullstring" <johannes.veit@.datapec.de> wrote in message
news:1127133941.948110.85450@.g44g2000cwa.googlegro ups.com...
> Does anybody knows, how this cross apply works?
> I need a simple example, because I have no idea what it's for!
> (I can't found any good example in the net!?!?)
>
> thanks in advance...
>

Need - help to create DLOOKUP with SSIS

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.
I find the Lookup component works best for this. Jamie wrote an article that compares lookup as well as other methods.
http://www.sqlis.com/default.aspx?311
Adrian

Near parameter

Hello:
Somebody knows how much distance between words uses the "NEAR" parameter?
PD: Pardon for my English.
IIRC its 50 words, after 50 words with a FreeText query the rank drops to 0,
after IIRC 1365 words the words are no longer considered to be near.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:u3RkhIq6FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hello:
> Somebody knows how much distance between words uses the "NEAR" parameter?
> PD: Pardon for my English.
>
|||Hi Hilary:
My query uses "containstable" clause to search with "NEAR" parameter
(CONTAINSTABLE(table, field, '"actor" NEAR "gana"'),
but not return all records existent. I try with "freetexttable", but return
records that not correspond with the query.
Any suggestion?.
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:uTOC$Zq6FHA.4076@.tk2msftngp13.phx.gbl...
> IIRC its 50 words, after 50 words with a FreeText query the rank drops to
> 0, after IIRC 1365 words the words are no longer considered to be near.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:u3RkhIq6FHA.476@.TK2MSFTNGP15.phx.gbl...
>
|||Vas,
Could you post the full output of -- SELECT @.@.version -- as this is helpful
info in understanding your environment.
Additionally, is the FT-enabled "table"'s column "field" defined as an IMAGE
datatype and contains binary files, such as MS Word or Adobe PDF files? If
not, does the text in the "field" column contain any Carriage Returns (CR)
or Line Feed (LF) characters? Finally, how many rows are in your FT-enabled
"table"?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Vas" <vas@.europeadederecho.com> wrote in message
news:ewjFtyq6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Hilary:
> My query uses "containstable" clause to search with "NEAR" parameter
> (CONTAINSTABLE(table, field, '"actor" NEAR "gana"'),
> but not return all records existent. I try with "freetexttable", but
> return records that not correspond with the query.
> Any suggestion?.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:uTOC$Zq6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||Hi John:
I will attempt answering to everything with my english, jeje.
Number of records: 384.000
Colum type: Image.
Colum Content: HTM Documents.
I have another colum with extensin of documents (htm) (varchar(3)).
My query is:
SELECT [KEY] FROM CONTAINSTABLE(ST, Texto, '"actor" near "gana"')
The number of result is 50, when really is 72.
SELECT @.@.version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Thanks.
"John Kane" <jt-kane@.comcast.net> escribi en el mensaje
news:%23OPiXgs6FHA.1000@.tk2msftngp13.phx.gbl...
> Vas,
> Could you post the full output of -- SELECT @.@.version -- as this is
> helpful info in understanding your environment.
> Additionally, is the FT-enabled "table"'s column "field" defined as an
> IMAGE datatype and contains binary files, such as MS Word or Adobe PDF
> files? If not, does the text in the "field" column contain any Carriage
> Returns (CR) or Line Feed (LF) characters? Finally, how many rows are in
> your FT-enabled "table"?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:ewjFtyq6FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||can you do this query? This will tell you which rows don't show up. Then
check the separation distance in these rows
select * from (select [key] from containstable(ST, Texto, '"actor" near
"gana"')) as X
right join (select [key] from containstable(ST, Texto, '"actor" OR
"gana"')) as Y on y.[key]=x.[key]
where x.[key] is null
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:eYi6JHt6FHA.224@.TK2MSFTNGP10.phx.gbl...
> Hi John:
> I will attempt answering to everything with my english, jeje.
> Number of records: 384.000
> Colum type: Image.
> Colum Content: HTM Documents.
> I have another colum with extensin of documents (htm) (varchar(3)).
> My query is:
> SELECT [KEY] FROM CONTAINSTABLEST, Texto, '"actor" near "gana"')
> The number of result is 50, when really is 72.
> SELECT @.@.version:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> Thanks.
> "John Kane" <jt-kane@.comcast.net> escribi en el mensaje
> news:%23OPiXgs6FHA.1000@.tk2msftngp13.phx.gbl...
>
|||Hi Hilary:
This query return all records that not correspond with the condition. Is
possible to change the number of words of distance to evaluate?.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:ef$qphu6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> can you do this query? This will tell you which rows don't show up. Then
> check the separation distance in these rows
> select * from (select [key] from containstable(ST, Texto, '"actor" near
> "gana"')) as X
> right join (select [key] from containstable(ST, Texto, '"actor" OR
> "gana"')) as Y on y.[key]=x.[key]
> where x.[key] is null
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:eYi6JHt6FHA.224@.TK2MSFTNGP10.phx.gbl...
>
|||No, if the data was stored in the text or char columns I could write
something to do this, but not when they are stored in binary.
What you need to do is look in these rows returned by the query. Figure out
what the word separation or if there is another problem preventing them from
showing up in your near searches. Sometimes the angle brackets in html tags
prevent words from being indexed.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:em7pxW06FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary:
> This query return all records that not correspond with the condition.
> Is possible to change the number of words of distance to evaluate?.
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:ef$qphu6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>