March 18, 2008 at 8:51 am
I am transferring a database from Access to SQL server 2005. I need to match 2 tables on a single column. I generated the query in Access:
SELECT DISTINCT PCMBD_RFMT.mrn, CMBD_RFMT.mrn
FROM PCMBD_RFMT INNER JOIN CMBD_RFMT ON PCMBD_RFMT.mrn = CMBD_RFMT.mrn
WHERE (((PCMBD_RFMT.mrn)=[cmbd_rfmt].[mrn]));
This works fine in Access finding over 10,000 matches. I copied and pasted the same query into a stored procedure and executed it. It finds no matches.
I know the matches are there because I can see them in the tables.
What is the trick here?
March 18, 2008 at 9:49 am
Can't say I see why it's not working in SQL server. That being said - your WHERE clause is unnecessary, since it's your ON clause as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 11:00 am
I lost the where clause but it still doesn't work. Maybe I am going about this the wrong way by copying the way it was done in Access. (I didn't write this application - I inherited it).
All I am trying to do is find a single occurrance of each mrn in the 2 tables. The tables are a bunch of invoices identified by the Medical Record Number (mrn). A given mrn may occur multiple times in the CMBD table, but not the PCMBD table or in the PCMBD table but not the CMBD table, or it may occur multiiple times in both tables. Is there an easier way to create a master table with one occurance of each mrn?
March 18, 2008 at 11:18 am
Well - if you want a "master" of all MRN's out of both tables, I'd assume you want any mrn's in either table, and not just the ones that exist in both tables (you'd miss all of the "orphans" that way). Considering MRN's are usually numeric and not alpha-numeric - I'd also start by making sure that both are stored as BIGINT.
Assuming you make sure that both MRN columns are typed the same way, then try something like this:
Select MRN
from PCMBD_RFMT
UNION
Select MRN
from CMBD_RFMT
You would then end up with unique values of the MRN across both tables. (UNION has an implicit DISTINCT operation already going on - so you don't need to specify it again.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 12:02 pm
I tried your suggestion and it looks like it worked. I wanted one occurance of each MRN no matter which table it appeared in or in both and it looks like that's what I got.
I LOVE this forum. You guys actually help people who are just starting out with this without being snide about our ignorance.
Really, an old COBOL programmer like me expects to be ridiculed. So thank you. I have no doubt I'll be back.:D
March 18, 2008 at 12:14 pm
Happy to help. Thanks for the feedback...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 12:23 pm
jfletcher (3/18/2008)
I tried your suggestion and it looks like it worked. I wanted one occurance of each MRN no matter which table it appeared in or in both and it looks like that's what I got.I LOVE this forum. You guys actually help people who are just starting out with this without being snide about our ignorance.
Really, an old COBOL programmer like me expects to be ridiculed. So thank you. I have no doubt I'll be back.:D
You actually admit to knowing COBOL? Me and a cowork will vehemently deny ever having programmed in COBOL.
:w00t:
March 18, 2008 at 12:27 pm
Well, yeah. A year or so ago nobody would believe I could learn to program in C# - which I have. The problem with having been a COBOL programmer is people tend to assume you can't learn anything else. EVER.:rolleyes:
March 18, 2008 at 12:32 pm
jfletcher (3/18/2008)
Well, yeah. A year or so ago nobody would believe I could learn to program in C# - which I have. The problem with having been a COBOL programmer is people tend to assume you can't learn anything else. EVER.:rolleyes:
Just tell them you've been a .NET programmer for longer than they've ever dreamt possible. When they ask you to back that up...send them here:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 12:43 pm
That's a pretty neat site. I wish I had known about it a few months ago. Unfortunately, the only client we had whose application was written in cobol has gone away, so I'm not doing it any more.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply