October 11, 2007 at 4:35 pm
Hrmm, Im seeing some weird reults here. If i do a select * from upload I see the people code id's of the people who should NOT be appearing in my final query. One for example is P000008270. BUT when i do a select * from upload where people_code_id ='P000008270' I get NO results. Any ideas as to why this is happening?
October 11, 2007 at 5:08 pm
My original upload file contained over 6,000 entries, could this explain the weird query behavior?
October 12, 2007 at 2:32 am
can you try to execute the following T-SQL statement:
select * from upload where RTRIM(people_code_id) ='P000008270'
what is the result?
October 12, 2007 at 8:58 am
Tried select * from upload where RTRIM(people_code_id) ='P000008270'
and still returned zero results, im going to break my original upload file into 3 smaller files, and reupload. Any other ideas why it would apear with a select * but not with a where statement?
Its weird too because when i run
select distinct p.first_name,p.last_name,p.people_code_id,up.people_code_id
from people as p
inner join peopletype as pt
on p.people_code_id=pt.people_code_id
left outer join upload as up
on p.people_code_id = up.people_code_id
where p.deceased_flag='n' and pt.people_type='alum'
and up.people_code_id is null
I get the correct results showing for p.people_code_id but the records are still showing as null on the up.people_code_id column. Strange how some work and some dont. Upon further investigation i noticed they appear in blocks. 4 sequential blocks. First block contains 12 id's, 2nd 3, 3rd 16 and 4th 14 id's. That leads me to believe its a problem with the amount im uploading in a single file because all the incorrect ones are in a sequentail order, does that make sense?
October 12, 2007 at 9:02 am
Its possible you have some weird ascii character that may be invisible but still there. Its an unusual thing to have happen but it does happen. Try running your select * and copy the column out and paste it into your where clause.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 12, 2007 at 9:04 am
Kenneth Fisher (10/12/2007)
Its possible you have some weird ascii character that may be invisible but still there. Its an unusual thing to have happen but it does happen. Try running your select * and copy the column out and paste it into your where clause.
That was the first thing i tried. and same results.
October 12, 2007 at 9:15 am
Ok .. just to see try
select * from upload where LTRIM(RTRIM(people_code_id)) ='P000008270'
Also confirm that the string 'P000008270' is correct in this particular query. You may be dealing with several problems at once. For example (and don't laugh this has happend to me) you could have O's in the string instead of 0's (letter instead of number). And then on top of that your upload table may have a char column (instead of varchar) which is padding spaces at the end of the field.
Copying the string out of the select * should take care of the first and the ltrim(rtrim()) should take care of the second.
Also come to think of it confirm that the type on the upload.people_code_id is varchar and not nvarchar. It shouldn't make a difference but you never know with some of the settings available to SQL.
Also speeking of settings if your system is set to be case sensative you might need to confirm that all of your characters are the same case.
(If it sounds like I'm throwing out random ideas .. I am 🙂 )
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 12, 2007 at 9:25 am
You could also try something like this
INSERT INTO Upload
SELECT
t1.first_name,
t1.last_name,
t1.people_code_id
FROM
(
SELECT DISTINCT
p.first_name,
p.last_name,
p.people_code_id
FROM people p,
peopletype pt
WHERE p.people_code_id=pt.people_code_id
AND p.deceased_flag='n'
AND pt.people_type='alum'
) t1 LEFT OUTER JOIN
Upload u
ON t1.people_code_id = u.people_code_id
WHERE u.people_code_id IS NULL
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 12, 2007 at 9:32 am
Found it!! When i looked at the actual table values using enterprise manager, i noticed all the records that were appearing in correctly had something like this
P000008269**
P000008270**
But the * was actually a small square, i deleted the extra "squares" from the end of the id, and it worked! Any ideas what those squares represent or could be? and why it happened? Any reason i shouldnt edit the table using enterprise manager? I couldnt use QA because the squares were not showing up, just blank spaces.
October 12, 2007 at 9:40 am
small squares like this ? represent carriage returns
October 12, 2007 at 9:43 am
weird, i wonder how that could have been added to this file.
October 12, 2007 at 10:19 am
If you are pulling a file from excel it could have been loaded into it orriginally. Also the small squares arn't always carrage returns.
There are some functions out there that will strip out non standard characters. I don't have one off hand but you should be able to search the web a bit and find one.
If you can confirm its a carrage return you could always do a replace with char(13) and replace it with ''.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
October 12, 2007 at 10:21 am
luckily there were only about 50, and i just removed them by hand. Everything works now, thanks for the insight guys!!
October 12, 2007 at 5:07 pm
Because you are performing an outer join, the data not contained in the upload table will show nulls, so you can test for null.
select distinct p.first_name
,p.last_name
,p.people_code_id
from people as p
inner join peopletype as pt
on p.people_code_id=pt.people_code_id
LEFT OUTER JOIN UploadTable ON P.People_cod_id = UploadTable.People_cod_id
where p.deceased_flag='n'
and pt.people_type='alum'
AND UploadTable.PK_UploadTable IS NULL
I think this will give you what you want; A list of people in the people table not found in the upload table.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply