April 1, 2004 at 8:28 pm
Start with an exclusion_list table.
CREATE TABLE [dbo].[ExclusionList] (
[ExclusionList_Id] [int] IDENTITY (100, 1) NOT NULL ,
[user_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_mod_date] [datetime] NOT NULL ,
[spec_name] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prospect_col_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[exclude_value] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[requested_by] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_date] [datetime] NOT NULL
where the prospect_col_name references the name fields in the Prospect table and exclude_value contains a value which will result in a record being placed on hold (an entry gets written into the prospect_hold table). spec_name is the name of a host - things can duplicate betwen hosts, so must ensure host type is factored in.
Records get inserted into the Prospect table from import of host downloads. After the file is imported, need to scan through the table to determine if any of the new rows need to be excluded.
So I wrote this rather ugly thing which creates Insert statements by Selecting from Prospect where the prospect.<column name> = one of the exclude values (prospect_col_name = prospect.<column name>.
The thing works for strings. Performance isn't near as bad as one would think (there are roughly 4000 exclusions, 210,000 records in Prospect - and the server is pretty decent).
Problem? - this line
SELECT @scan_sql = @scan_sql + ' AND LTRIM(RTRIM(UPPER(prospect.' + @col_name + '))) = LTRIM(RTRIM(UPPER(exclusionlist.exclude_value))))'
will obviously only work for string values. Would be ok if it weren't for the money, int, decimal, and date values.
Tried to throw in some stuff to read the column type from the system tables,
SELECT c1.name, t.name as col_Type
FROM syscolumns c1, master..spt_values t
WHERE c1.id = object_id('prospect')
and t.type = 'J'
and c1.type = t.low
order by c1.name
which I am using for some other purposes, but it is getting even uglier than it already is.
I need to find a way to CASE or whatever the line above when the field type is not a varchar - returning as moneyn, intn, datetimn when I run the above query.
Current process below. ANY suggestion on improving this will be accepted. Don't worry about insulting the design, I'm too limited in SQL Server to not believe there isn't a better way to go about this. For now, I can live with the cursors if I can get the data type stuff running - I have a serious deadline on delivery of this process.
/*
Uses a dual cursor loop construct to find exclusions for
each host. Outer loop is host, inner is field types for
each host.
Inner cursor is constructed as each field type is completed.
Only way I could find to get the @host_name to get picked
up in cursor construction.
*/
DECLARE @col_name varchar (50)
DECLARE @scan_sql varchar (2000)
DECLARE @host_name varchar (8)
DECLARE HostCursor CURSOR FOR
SELECT DISTINCT spec_name
FROM exclusionlist
OPEN HostCursor
FETCH NEXT FROM HostCursor
INTO @host_name
-- Check @@FETCH_STATUS to see if a host was available
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE RowCursor CURSOR FOR
SELECT DISTINCT prospect_col_name
FROM exclusionlist
WHERE spec_name = @host_name
OPEN RowCursor
FETCH NEXT FROM RowCursor
INTO @col_name
-- Check @@FETCH_STATUS to see if there were any more rows for this host.
WHILE @@FETCH_STATUS = 0
BEGIN
-- build the Insert statement
SELECT @scan_sql = 'INSERT prospect_hold(prospectid,exclude_value, end_dt, hold_type, status, start_dt, insert_dt, insert_id) '
SELECT @scan_sql = @scan_sql + ' SELECT prospectid,'
SELECT @scan_sql = @scan_sql + @col_name + ','
SELECT @scan_sql = @scan_sql + 'end_date,'
SELECT @scan_sql = @scan_sql + ' hold_type = ''' + @col_name + ''','
SELECT @scan_sql = @scan_sql + ' status = ''A'','
SELECT @scan_sql = @scan_sql + ' start_dt = getdate(),'
SELECT @scan_sql = @scan_sql + ' insert_dt = getdate(),'
SELECT @scan_sql = @scan_sql + ' insert_id = ''Init Load'''
SELECT @scan_sql = @scan_sql + ' FROM prospect, exclusionlist'
SELECT @scan_sql = @scan_sql + ' WHERE (LTRIM(RTRIM(UPPER('
SELECT @scan_sql = @scan_sql + @col_name + '))) IN (SELECT LTRIM(RTRIM(UPPER(exclude_value)))'
SELECT @scan_sql = @scan_sql + ' FROM exclusionlist WHERE spec_name = ''' + @host_name + ''''
SELECT @scan_sql = @scan_sql + ' AND end_date > getdate()'
SELECT @scan_sql = @scan_sql + ' AND prospect_col_name = ''' + @col_name + ''')'
SELECT @scan_sql = @scan_sql + ' AND LTRIM(RTRIM(UPPER(prospect.' + @col_name + '))) = LTRIM(RTRIM(UPPER(exclusionlist.exclude_value))))'
SELECT @scan_sql = @scan_sql + ' AND (prospectid NOT IN '
SELECT @scan_sql = @scan_sql + '( SELECT prospectid'
SELECT @scan_sql = @scan_sql + ' FROM prospect_hold))'
SELECT @scan_sql = @scan_sql + ' ORDER BY ProspectID'
-- uncomment to watch the values
-- PRINT @scan_sql
EXECUTE (@scan_sql)
FETCH NEXT FROM RowCursor
INTO @col_name
END
CLOSE RowCursor
DEALLOCATE RowCursor
-- check for next host
FETCH NEXT FROM HostCursor
INTO @host_name
END
CLOSE HostCursor
DEALLOCATE HostCursor
GO
April 2, 2004 at 10:24 am
You will always convert to string for the exec() statement. However, I think for other values, you should be able to determine the field type from information_schema.columns and then set the 2nd side of your = statement to match correctly. You would leave out the ltrim() rtrim() part for non-strings.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply