March 25, 2008 at 8:02 am
This is a follow up to a question I asked last week which I thought I had answered, but unfortunately didn't. Please help, I am at my wit's end.
I have 2 tables of invoices. The identifying number is the medical record number (mrn). A given number may occur in only one of the 2 tables or may occur in both and will often occur multiple times (once for each invoice). I need to create a master table of one occurrence of each number.
Here is short example of the numbers:
CMBD PCMBD
000005 014215
014215 017539
017539 017930
020426 G51243
Last week I received this answer which I tried and thought it solved the problem but, it didn’t:
`SELECT mrn
FROM cmbc_rfmt
UNION
SELECT mrn
FROM pcmbc_rfmt
ORDER BY mrn
Here is what I acutally get, which is not what I need
014215
017539
G51243
014215
017539
Here are some other things I have tried which were copied from the ACCESS database where this application currently resides:
1. Get the numbers from the CMBD table which do not occur in the PCMBD table. I get a single occurrence of ALL the numbers in the CMBD table as if there were no matches in PCMBD which I know there are.
ELECT DISTINCT CMBD_RFMT.mrn
FROM CMBD_RFMT LEFT JOIN PCMBD_RFMT ON CMBD_RFMT.mrn = PCMBD_RFMT.mrn
WHERE (((PCMBD_RFMT.mrn) Is Null))ORDER BY CMBD_RFMT.mrn;
This SELECT should give me a single occurrence of all the numbers that match, but it gives me nothing:
SELECT DISTINCT PCMBD_RFMT.mrn
FROM PCMBD_RFMT INNER JOIN CMBD_RFMT ON PCMBD_RFMT.mrn = CMBD_RFMT.mrn;
All of this is pretty straight forward that even a novice like me can understand, so why doesn’t it work?
It’s as though SQL simply cannot match these fields. I’m beginning to think it may be something in the data itself. Both fields are defined as NVARCHAR(10). They are defined as Text(10) in ACCESS, and ACCESS has no trouble matching them.
March 25, 2008 at 8:17 am
Your match should work. I am guessing that for some reason one of the tables might have spaces or some other non-visible characters that are causing the matches to miss.
Have you tried looking at the data carefully, perhaps with the LEN () function for two rows that should match?
March 25, 2008 at 8:43 am
OK, I ran the length function against the mrn field in both tables. In the CMBD table the actual length of all entries is 6. In the PCMBD table the actual length is 7 (though most of them are really only 6.) However I found a couple at the end of the table where the numbers are 7 characters (R001003), but the length is shown as 8. When I first saw this I thought I could just change the fields to NVARCHAR(6), but obviously I can't do that now that I have found the 7 character entries.
So, are there any suggestions as to how to define this field?
March 25, 2008 at 9:08 am
With the varchar it doesn't matter. If they are supposed to be six, you can trim them with the SUBSTRING function.
I'd use CHARINDEX to find the space, then SUBSTRING up to there.
substring( myfield, 1, CHARINDEX( ' ', myfield) - 1)
March 25, 2008 at 1:19 pm
Your suggestion didn't work, but it put me on the right track and I really appreciate that. It turns out to be a programming problem that occurred before I load the data into the tables. I have to format the data from the client from comma delimited into pipe delimited. I eliminated the initial quote from each line and there was a space there instead. So, the extra space was at the beginning of the field instead of at the end, which you really can't tell by looking at it. So, I did a trim on that field, reformatted the data and reloaded it into the table and all the matches work correctly. Am I happy now! I was getting really tired of this problem. So, thank you so much for the help.
😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply