Can someone help me determine why my subquery below is not totaling. Without
having to dig to deeply into the logic and data, I am hoping that seeing the
working/non working versions together might reveal something obvious. I have
removed the other selected columns from the main query.
I have tried using distinct and grouping in the subquery and using grouping
in the outer query to no avail.
Version 1 'unexpected' result with syntax is as it would be normally in the
query
TaxablePremium
--
1000.00
2000.00
Version 2 expected result with the FKs that would be passed from the outer
query assigned
TaxablePremium
--
3000.00
SELECT distinct
/* SubQuery version 1 - syntax is as it would be normally in the query*/
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
) as TaxablePremium
/* SubQuery version 2- FKs that would be passed from the outer query are
assigned */
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
(4298, 4353)))
AS TaxablePremium
FROM InvoiceLineItem ILI1
INNER JOIN InvoiceLineItemTax ON ILI1.ID =
InvoiceLineItemTax.InvoiceLineItemID
INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
AND (Invoice.InvoiceNumber =@.InvNbr1)Mike
If you show us a table structute with sample data we'd probably suggest
something.
In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I think
a problem. Try to look at your tables more carefully to see what is going
on when you're trying to JOIN them and to assign values.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not totaling.
> Without having to dig to deeply into the logic and data, I am hoping that
> seeing the working/non working versions together might reveal something
> obvious. I have removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
> grouping in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be normally in
> the query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
> AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not
totaling. Without
> having to dig to deeply into the logic and data, I am hoping that
seeing the
> working/non working versions together might reveal something
obvious. I have
> removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
grouping
> in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be
normally in the
> query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from
the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the
query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID
in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer
query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND
(QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID =
Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <>
0)
> AND (ILI1.IsContraDistribution = 0) AND
(ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>
Mike Harbinger,
Without a fully reproducible script to test with
(http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
say for sure.
However, I do spot something interesting.
Query 1:
>AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
This seems a bit unusual at first glance. Why specify IN (the
shortcut for <this> OR <that> OR <the other> ) when there is but one
column named? I would have thought it should just be:
AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
And then I see:
Query 2:
> WHERE (QuoteLine.InvoiceID = 5947)
> AND (QuoteTaxPremium.TaxLineID IN (4298, 4353)))
> AS TaxablePremium
This is supposed to be the equivalent of Query 1, only with literals
assigned. Except that two literals are assigned in the IN() list.
In Query 1, only 1 value will be present.
This makes Query 1 slightly different from what is implied in the
construction of the IN() in Query 2.
Is it enough of a difference to be causing the problem in question?
My apologies, but I can't say for sure.
Sincererely,
Chris O.|||Uri
Thank you for your prompt reply.
Below is the data from my earlier sample post.
I have omitted only those columns that are not
material to this portion of the result set.
What we have is an invoice that contains an
insurance premium amount and a tax amount. I am
trying to show a specific premium which is subject
to a specific tax.
That would be indicated by an invoice that has
a tax amount (ILI1) and a premium (QuotePremium)
joined on TaxLOB and Tax.
Path to tax via premium amount:
Invoice\QuoteLine\QuotePremium\QuoteTaxP
remium\TaxLOB\Tax
Path to tax via tax amount:
Invoice\InvoiceLineItem (as ILI1)\InvicelineItemTax\TaxLOB\Tax
Table- QuoteLine
ID InvoiceID
20370 5947
20369 5947
Table- QuotePremium
ID QuoteLineID Premium
19865 20370 1000
19864 20369 2000
Table- QuoteTaxPremium
ID QuotePremiumID TaxLineID
181069 19865 4298
181070 19864 4353
InvoiceLineItemTax.InvoiceLineItemID
50405
50405
InvoiceLineItemTax.TaxLOBID
4298
4353
Table- TaxLOB
ID TaxID
4298 54
4353 54
Tax.ID
54
54
Invoice.ID
5947
5947
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QmpQJZ7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Mike
> If you show us a table structute with sample data we'd probably suggest
> something.
>
> In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I
> think a problem. Try to look at your tables more carefully to see what is
> going on when you're trying to JOIN them and to assign values.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
>|||Chris
Actually the IN was recently added in debugging and didn't affect anything.
It originally was '='
There are 2 records in the test data because there are 2 when the query is
run resulting in the
2 premium amounts (1,000 and 2,000). What I don't understand is why the SUM
is not totalling
no matter how many records there are. Perhaps I don't fully understand how
the function works.
My apologies for not following protocol and thank you for passing along that
link. I was reluctant to bother you with the entire script and was hoping I
had missed something fairly obvious. In order for me to replicate the data
set I would have to dump many tables and force you to to deal with FK
constraints. I was not aware you would be willing to deal with all that. I
am reviewing the link to see if I can properly ge the data togehter.
Many thanks for your time on this!!
Mike
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> totaling. Without
> seeing the
> obvious. I have
> grouping
> normally in the
> the outer
> query*/
> in(InvoiceLineItemTax.taxlobId))
> query are
> (QuoteTaxPremium.TaxLineID IN
> Office2.ID
> 0)
> (ILI1.LineItemAccountingType = 2)
> Mike Harbinger,
> Without a fully reproducible script to test with
> (http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
> say for sure.
> However, I do spot something interesting.
> Query 1:
>
> This seems a bit unusual at first glance. Why specify IN (the
> shortcut for <this> OR <that> OR <the other> ) when there is but one
> column named? I would have thought it should just be:
> AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
>
> And then I see:
> Query 2:
>
> This is supposed to be the equivalent of Query 1, only with literals
> assigned. Except that two literals are assigned in the IN() list.
> In Query 1, only 1 value will be present.
> This makes Query 1 slightly different from what is implied in the
> construction of the IN() in Query 2.
> Is it enough of a difference to be causing the problem in question?
> My apologies, but I can't say for sure.
>
> Sincererely,
> Chris O.
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23edXata7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>
> "Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in
message
> news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
<snip>
> Chris
> Actually the IN was recently added in debugging and didn't affect
anything.
> It originally was '='
> There are 2 records in the test data because there are 2 when the
query is
> run resulting in the
> 2 premium amounts (1,000 and 2,000). What I don't understand is
why the SUM
> is not totalling
> no matter how many records there are. Perhaps I don't fully
understand how
> the function works.
> My apologies for not following protocol and thank you for passing
along that
> link. I was reluctant to bother you with the entire script and was
hoping I
> had missed something fairly obvious. In order for me to replicate
the data
> set I would have to dump many tables and force you to to deal with
FK
> constraints. I was not aware you would be willing to deal with all
that. I
> am reviewing the link to see if I can properly ge the data
togehter.
> Many thanks for your time on this!!
> Mike
Mike,
Please don't hestitate to force me to deal with the necessities of
your database! :)
As for dumping several tables, we only need a few key rows pere
table, with the data embedded in INSERT statements.
With a script along the lines of . . .
CREATE TABLE . . .
CREATE TABLE . . .
INSERT . . .
INSERT . . .
EXPECTED RESULTS . . .
QUERY/STORED PROC/ETC. . . .
ACTUAL RESULTS . . .
DROP TABLE . . .
DROP TABLE . . .
Anyone here can copy and paste a script like the above into QA or
Express Manager (or even OSQL!), and away we go with trying to fix
your problem instead of trying to figure out the answers for any of
a number of intermediary questions that are necessary to reaching a
solution.
Sincerely,
Chris O.
Showing posts with label below. Show all posts
Showing posts with label below. Show all posts
Friday, March 23, 2012
Wednesday, March 7, 2012
freetextable and multiple tables
In the below FREETEXTTABLE query, I have the SELECT fields indexed in the FT
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.
Here is an example of such a parent child relationship:
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.
This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
usage is:
EXEC search 'microsoft sql server',''
Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @.@.error system variable.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the
FT
> catalog. I should be getting results and I'm not. The problem, as I see
it,
> is that I need to be searching all 3 tables when actually I'm only
searching
> 1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria).
At
> the very bottom, I removed 2 tables and the query works. How do I search
all
> 3 tables and get results?
> thanks
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> ========================================
> --This one works...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> --ItemTitles.Title,
> --ItemTitles.Artist,
> --ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
>
|||Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
-- --
1 06897
1 19713
*/
select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
-- -- -- -- -
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/
In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
-- -- --
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:
SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-- --
Davolio Nancy
(1 row(s) affected)
*/
Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> you can't query multiple tables in a single full text query unless there
is
> some sort of a parent child relationship between ItemStock.OrderNo and
> ItemHardware, and ItemTitles.
> Here is an example of such a parent child relationship:
>
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I am storing the text data in the child table and both the parent and the
> child share a common pk. There is no DRI constraint.
>
> This makes the ContainsTable query a little more complex. Here is my
stored
> procedure that I use to query this table.
>
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> usage is:
>
> EXEC search 'microsoft sql server',''
>
> Notice that what we are doing is returning the results set from MSSearch
as
> a derived table and joining this derived table (named FTTABLE) against the
> parent table. The child table which contains our text information is not
> queried as all. Also notice that I am not doing any error checking or
> returning any return codes from this stored procedure. My experience is
that
> building the search string can be made error free, and the errors
generated[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
FROM ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo = ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS FS_TABLE ON
FS_TABLE.[KEY] = ItemStock.OrderNo
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> Not sure if this going to help or not. Below are my 3 tables. I have a
PK/FK
> relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
> ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
> rebuilt the catalog and still not getting results. Any further help would
be
> appreciated. This is my query...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
> CREATE TABLE [dbo].[ItemStock] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_Update] [datetime] NULL ,
> [Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Inv] [decimal](5, 0) NULL ,
> [d_Date] [datetime] NULL ,
> [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManSort] [int] NULL ,
> [Weight] [decimal](5, 2) NULL ,
> [Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemTitles] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SortKey] [decimal](5, 0) NOT NULL ,
> [Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemHardware] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.
Here is an example of such a parent child relationship:
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.
This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
usage is:
EXEC search 'microsoft sql server',''
Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @.@.error system variable.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the
FT
> catalog. I should be getting results and I'm not. The problem, as I see
it,
> is that I need to be searching all 3 tables when actually I'm only
searching
> 1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria).
At
> the very bottom, I removed 2 tables and the query works. How do I search
all
> 3 tables and get results?
> thanks
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> ========================================
> --This one works...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> --ItemTitles.Title,
> --ItemTitles.Artist,
> --ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
>
|||Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
-- --
1 06897
1 19713
*/
select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
-- -- -- -- -
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/
In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
-- -- --
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:
SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-- --
Davolio Nancy
(1 row(s) affected)
*/
Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> you can't query multiple tables in a single full text query unless there
is
> some sort of a parent child relationship between ItemStock.OrderNo and
> ItemHardware, and ItemTitles.
> Here is an example of such a parent child relationship:
>
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I am storing the text data in the child table and both the parent and the
> child share a common pk. There is no DRI constraint.
>
> This makes the ContainsTable query a little more complex. Here is my
stored
> procedure that I use to query this table.
>
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> usage is:
>
> EXEC search 'microsoft sql server',''
>
> Notice that what we are doing is returning the results set from MSSearch
as
> a derived table and joining this derived table (named FTTABLE) against the
> parent table. The child table which contains our text information is not
> queried as all. Also notice that I am not doing any error checking or
> returning any return codes from this stored procedure. My experience is
that
> building the search string can be made error free, and the errors
generated[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
FROM ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo = ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS FS_TABLE ON
FS_TABLE.[KEY] = ItemStock.OrderNo
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> Not sure if this going to help or not. Below are my 3 tables. I have a
PK/FK
> relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
> ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
> rebuilt the catalog and still not getting results. Any further help would
be
> appreciated. This is my query...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
> CREATE TABLE [dbo].[ItemStock] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_Update] [datetime] NULL ,
> [Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Inv] [decimal](5, 0) NULL ,
> [d_Date] [datetime] NULL ,
> [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManSort] [int] NULL ,
> [Weight] [decimal](5, 2) NULL ,
> [Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemTitles] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SortKey] [decimal](5, 0) NOT NULL ,
> [Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemHardware] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
Subscribe to:
Posts (Atom)