September 22, 2004 at 10:57 am
I'm trying to write a query against data that should be one field in one record that is 2 fields per record and can be in multiple fields.
I can't change the structure because we are maintaining a copy of the mainframe data on our SQL Server.
These are legal descriptions for property. The structure is:
CREATE TABLE [dbo].[legal] (
[dp] [char] (14) NOT NULL ,
[num] [smallint] NOT NULL ,
...
[descr1] [char] (24) NULL ,
[descr2] [char] (24) NULL ,
...
)
dp is the primary key for a parcel in general. num is a sequential number within the parcel. descr1 and descr2 are two parts of the legal description in this record. There can be up to 16 of the legal records. The "True" legal description is a combination of all the descr1 and descr2 fields from all records for dp in num order (removing trailing spaces and adding a single space between each one).
I would like to be able to submit SQL to find a legal similiar to
SELECT * from legal where rtrim(descr1) + ' ' + rtrim(descr2) like '%AIRPORT COMMERCE PARK WEST PLAT 1%'
The problem is that the data for this particular instance is contained in descr2 of record 1 (num = 1) and descr1 of record 2 (num = 2)
Steve
September 22, 2004 at 12:34 pm
it sounds like you mean that it is a two part primary key dp + num.
so then you could do exactly the same thing except with a self join
select...
FROM legal a
JOIN legal b on a.dp = b.dp
AND a.num = 1 and b.num = 2
WHERE rtrim(a.descr1) + ' ' + rtrim(b.descr2) like '%AIRPORT COMMERCE PARK WEST PLAT 1%'
September 22, 2004 at 1:18 pm
Thanks.
That gives me a direction I can try. It works when there are two records per dp but if there is only one row it ignores the other rows if using an inner join or returns NULL if using a full outer join. I think I'll set up a query for each number of rows (up to 16) and UNION them. If that works I'll use that query to create a view.
The other idea I had was to create a temp table such as
create table dbo.[#legal_full] (
dp char(14) NOT NULL,
legal_desc varchar(800) NULL
)
go
insert into #legal_full
select dp, rtrim(descr1) + ' ' + rtrim(descr2)
from legal
where num = 1
go
update #legal_full set legal_desc = legal_desc +
(select ' ' + rtrim(descr1) + ' ' + rtrim(descr2)
from legal
where dp = #legal_full.dp and num = 2)
where dp in (select dp from legal
where dp = #legal_full.dp and num = 2)
go
...
update #legal_full set legal_desc = legal_desc +
(select ' ' + rtrim(descr1) + ' ' + rtrim(descr2)
from legal
where dp = #legal_full.dp and num = 16)
where dp in (select dp from legal
where dp = #legal_full.dp and num = 16)
go
SELECT * from #legal_full
where legal_desc like '%AIRPORT COMMERCE PARK WEST PLAT 1%'
With almost 375,000 rows it might not be too responsive for queries.
My other option is to create a regular table of legal_full and reload it every night (this table is only updated at night from transactions applied to the mainframe during the day or uploaded from us).
September 23, 2004 at 12:36 pm
Just an update.
The query that involved a self join took too long (5-8 sec).
I opted instead to create a permanent table of legal_full and reload it every night from a stored procedure that's kicked off after the rest of the daily processing is done.
Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply