August 27, 2009 at 7:39 am
Hi,
Even with my limited experience, I should be able to figure this out, but I'm stumped.
In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.
The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.
The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.
So here is the SQL query that I wrote:
SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,
CLMMSTP.DOS, PHMSTP.PHNAME,
RPMSTP.REFDOC, RPMSTP.RNAME
FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP
LEFT JOIN TXBTESTDTA.RPMSTP
ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC
WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)
AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')
AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')
Yet, this is the result I'm getting:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.CLIENT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.REFDOC" could not be bound.
Any help on this would be appreciated.
August 27, 2009 at 7:48 am
SparTodd (8/27/2009)
Hi,Even with my limited experience, I should be able to figure this out, but I'm stumped.
In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.
The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.
The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.
So here is the SQL query that I wrote:
SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,CLMMSTP.DOS, PHMSTP.PHNAME,
RPMSTP.REFDOC, RPMSTP.RNAME
FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP
LEFT JOIN TXBTESTDTA.RPMSTP
ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC
WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)
AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')
AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')
Yet, this is the result I'm getting:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.CLIENT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.REFDOC" could not be bound.
Any help on this would be appreciated.
Does this query work for you?
SELECT CLMMSTP.CLIENT,
CLMMSTP.CLMNUM,
CLMMSTP.MACTNM,
CLMMSTP.DOCTOR,
CLMMSTP.DOS,
PHMSTP.PHNAME,
RPMSTP.REFDOC,
RPMSTP.RNAME
FROM TXBTESTDTA.CLMMSTP
INNER JOIN TXBTESTDTA.PHMSTP
ON CLMMSTP.CLIENT = PHMSTP.CLIENT
AND CLMMSTP.DOCTOR = PHMSTP.DOCTOR
LEFT JOIN TXBTESTDTA.RPMSTP
ON CLMMSTP.CLIENT = RPMSTP.CLIENT
AND CLMMSTP.REFDOC = RPMSTP.REFDOC
WHERE (CLMMSTP.CLIENT = N'ACA')
AND (CLMMSTP.MACTNM = '985')
AND (CLMMSTP.CLMNUM = '8268060')
/*(CLMMSTP.CLIENT = PHMSTP.CLIENT)
AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR)
AND */
Cheers,
J-F
August 27, 2009 at 7:51 am
The error:
You have to use aliases for your tables or use a complete qualified column name like "SELECT TXBTESTDTA.CLMMSTP.CIENT".
BTW:
Try to get rid of the old JOIN syntax as combination of FROM and WHERE. Use the ANSI JOIN:
SELECT
cl.CLIENT
,cl.CLMNUM
,cl.MACTNM
,cl.DOCTOR
,cl.DOS
,pc.PHNAME
,rp.REFDOC
,rp.RNAME
FROM TXBTESTDTA.CLMMSTP cl
JOIN TXBTESTDTA.PHMSTP pc ON cl.CLIENT = pc.CLIENT AND cl.DOCTOR = pc.DOCTOR
LEFT JOIN TXBTESTDTA.RPMSTP rp ON cl.CLIENT = rp.CLIENT AND cl.REFDOC = rp.REFDOC
WHERE (cl.MACTNM = '985')
AND (cl.CLMNUM = '8268060')
Greets
Flo
August 27, 2009 at 7:55 am
J-F Bergeron (8/27/2009)
SparTodd (8/27/2009)
Hi,Even with my limited experience, I should be able to figure this out, but I'm stumped.
In SQL Server 2005 I have a DB2 Database on a linked server. There are three tables that I'm trying to select from in a particular query. The tables are CLMMSTP, PHMSTP and RPMSTP.
The tables CLMMSTP and PHMSTP have a 1:1 relationship on the columns CLIENT and DOCTOR.
The tables CLMMSTP and RPMSTP have a Left Join relationship on the columns CLIENT and REFDOC. The Left Join is required because not all records in CLMMSTP have a REFDOC.
So here is the SQL query that I wrote:
SELECT CLMMSTP.CLIENT, CLMMSTP.CLMNUM, CLMMSTP.MACTNM, CLMMSTP.DOCTOR,CLMMSTP.DOS, PHMSTP.PHNAME,
RPMSTP.REFDOC, RPMSTP.RNAME
FROM TXBTESTDTA.CLMMSTP, TXBTESTDTA.PHMSTP
LEFT JOIN TXBTESTDTA.RPMSTP
ON CLMMSTP.CLIENT = RPMSTP.CLIENT AND CLMMSTP.REFDOC = RPMSTP.REFDOC
WHERE (CLMMSTP.CLIENT = PHMSTP.CLIENT)
AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR) AND (CLMMSTP.CLIENT = N'ACA')
AND (CLMMSTP.MACTNM = '985') AND (CLMMSTP.CLMNUM = '8268060')
Yet, this is the result I'm getting:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.CLIENT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "CLMMSTP.REFDOC" could not be bound.
Any help on this would be appreciated.
Does this query work for you?
SELECT CLMMSTP.CLIENT,CLMMSTP.CLMNUM,
CLMMSTP.MACTNM,
CLMMSTP.DOCTOR,
CLMMSTP.DOS,
PHMSTP.PHNAME,
RPMSTP.REFDOC,
RPMSTP.RNAME
FROM TXBTESTDTA.CLMMSTP
INNER JOIN TXBTESTDTA.PHMSTP
ON CLMMSTP.CLIENT = PHMSTP.CLIENT
AND CLMMSTP.DOCTOR = PHMSTP.DOCTOR
LEFT JOIN TXBTESTDTA.RPMSTP
ON CLMMSTP.CLIENT = RPMSTP.CLIENT
AND CLMMSTP.REFDOC = RPMSTP.REFDOC
WHERE (CLMMSTP.CLIENT = N'ACA')
AND (CLMMSTP.MACTNM = '985')
AND (CLMMSTP.CLMNUM = '8268060')
/*(CLMMSTP.CLIENT = PHMSTP.CLIENT)
AND (CLMMSTP.DOCTOR = PHMSTP.DOCTOR)
AND */
Thank you very much, J-F! That works and most importantly, I get it.
EDIT: Do we mark these threads as resolved and attribute a particular response to the resolution on here? Sorry, I'm a newbie here but I'm not seeing that option.
August 27, 2009 at 8:06 am
You can't mark it as the answer here, but just saying it is enough.
Thanks for the feedback, and as Flo noted, I also propose you use table aliases, they are a lot easier to work with, and shorter to write.
Cheers,
J-F
August 27, 2009 at 8:07 am
Thank for your insight, too, Flo.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply