Odd Selection results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Odd Selection results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:33:48

Aaron writes "Hi. I'm using windows 2000 service pack 4 with sql server 2000 SP4

I'm working with three tables to filter query results so they can be inserted.

The first table grabs a directory using the xp..cmdshell then parses out some data from the short file name generated by the DIR statement into the other rows of the table.

The other two tables are... almost identical. I stripped out a few columns on one to make it simpler to insert data into that table.

I have the table definitions here:
--begin table definition here
CREATE TABLE [dreamhome].[dbo].[bcltbl] (
[sfn] varchar (255) NULL,
[ordno] varchar (8) NULL,
[pickno] varchar (8) NULL,
[dir] varchar (3) NULL
)

CREATE TABLE [dreamhome].[dbo].[document_link_staging] (
[document_link_uid] int NULL,
[source_area_cd] int NULL,
[key1_cd] varchar (255) NOT NULL,
[key1_value] varchar (255) NOT NULL,
[key2_cd] varchar (255) NULL,
[key2_value] varchar (255) NULL,
[key3_cd] varchar (255) NULL,
[key3_value] varchar (255) NULL,
[link_name] varchar (255) NULL,
[link_path] varchar (4099) NULL,
[row_status_flag] int NULL,
[outside_use_flag] char (1) NULL,
[mandatory_flag] char (1) NULL
)

CREATE TABLE [dreamhome].[dbo].[document_link_storage] (
[document_link_uid] int NOT NULL,
[source_area_cd] int NOT NULL,
[key1_cd] varchar (255) NOT NULL,
[key1_value] varchar (255) NOT NULL,
[key2_cd] varchar (255) NULL,
[key2_value] varchar (255) NULL,
[key3_cd] varchar (255) NULL,
[key3_value] varchar (255) NULL,
[link_name] varchar (255) NOT NULL,
[link_path] varchar (4099) NOT NULL,
[row_status_flag] int NOT NULL,
[date_created] datetime NOT NULL,
[created_by] varchar (255) NOT NULL,
[date_last_modified] datetime NOT NULL,
[last_maintained_by] varchar (255) NOT NULL,
[outside_use_flag] char (1) NULL,
[mandatory_flag] char (1) NULL
)

--end table definition here.

I'm using these to generate data to be inserted into a fourth table (document_link), which is used for object link embedding.

The procedure looks kinda like this:
grab directory information and parse it,
insert into document_link_staging where the pickno not in document_link_staging and document_link_storage.
And I'm using the pickno from bcltbl to cross reference the data and make certain that I am inserting the right data.
There is more to it, but that is where it gets snagged.

You see, lets say I had 300 rows in bcltbl and 100 rows of identical data in each of document_link_staging and document_link_storage.

--
SELECT *
FROM bcltbl
WHERE (pickno NOT IN
(SELECT key2_value
FROM document_link_staging))
--

returns 200 rows

while

--
SELECT *
FROM bcltbl
WHERE (pickno NOT IN
(SELECT key2_value
FROM document_link_storage))
returns 0 rows
--

And I cannot figure out why.

I used the same seed table, there are no constraints on either of the tables in question, and the column definitions are identical between the two tables I am trying use as criteria.
I'm unsure what to attribute this anomoly to, and am a little frustrated.

Essentially I am just trying to get this query working correctly:

--
insert into document_link_staging ()
SELECT
FROM bcltbl
WHERE (pickno NOT IN
(SELECT key2_value
FROM document_link_storage)) and
(pickno NOT IN
(SELECT key2_value
FROM document_link_staging))
--

In the mean time though, I've worked around this anomoly by doing this:
<

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources