asked on

SQL HELP!

Experts I need to join 2 passthru queries:

1: Main data, including loan number

SELECT RDX_BDE.T_POINT_OF_CONTACT.LN_NO, RDX_BDE.T_POINT_OF_CONTACT.POC_PROCESSOR_ID
FROM RDX_BDE.T_POINT_OF_CONTACT;

2: Need to join to this passthru and join on POC_Processor_ID and PTY_ID
SELECT RDX_BDE.T_PARTY.PTY_ID, RDX_BDE.T_PARTY.PTY1_NM, RDX_BDE.T_PARTY.PTY_TELEPHONE_NO, RDX_BDE.T_PARTY.PTY_FAX_NO, RDX_BDE.T_PARTY.PTY_EMAIL_ADR_XX
FROM RDX_BDE.T_PARTY
WHERE (((RDX_BDE.T_PARTY.PTY_TITLE_XX) Like '%HPC%'));

Member_2_2484401

Something like this should do it:

SELECT RDX_BDE.T_PARTY.PTY_ID,
       RDX_BDE.T_PARTY.PTY1_NM, 
       RDX_BDE.T_PARTY.PTY_TELEPHONE_NO, 
       RDX_BDE.T_PARTY.PTY_FAX_NO, 
       RDX_BDE.T_PARTY.PTY_EMAIL_ADR_XX
  FROM RDX_BDE.T_PARTY
  join RDX_BDE.T_POINT_OF_CONTACT
    on RDX_BDE.T_POINT_OF_CONTACT.POC_PROCESSOR_ID = T_PARTY.POC_PROCESSOR_ID
   and RDX_BDE.T_POINT_OF_CONTACT.PTY_ID = T_PARTY.PTY_ID
 WHERE (((RDX_BDE.T_PARTY.PTY_TITLE_XX) Like '%HPC%'));

Open in new window

HTH,
DaveSlash

Member_2_2484401

Oops, this version includes both sets of columns:

SELECT RDX_BDE.T_PARTY.PTY_ID,
       RDX_BDE.T_PARTY.PTY1_NM, 
       RDX_BDE.T_PARTY.PTY_TELEPHONE_NO, 
       RDX_BDE.T_PARTY.PTY_FAX_NO, 
       RDX_BDE.T_PARTY.PTY_EMAIL_ADR_XX,
       RDX_BDE.T_POINT_OF_CONTACT.LN_NO,
       RDX_BDE.T_POINT_OF_CONTACT.POC_PROCESSOR_ID
  FROM RDX_BDE.T_PARTY
  join RDX_BDE.T_POINT_OF_CONTACT
    on RDX_BDE.T_POINT_OF_CONTACT.POC_PROCESSOR_ID = T_PARTY.POC_PROCESSOR_ID
   and RDX_BDE.T_POINT_OF_CONTACT.PTY_ID = T_PARTY.PTY_ID
 WHERE (((RDX_BDE.T_PARTY.PTY_TITLE_XX) Like '%HPC%'));

Open in new window

MrAutomate

ASKER

Dave, thanks for the quick response....ODBC call failed:

T_PARTY.POC_Processor ID Not valid in context where it is used : SQLSTATE42703

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

MrAutomate

ASKER

Dave. Thanks! with 2 changes, I appear to have gotten it:

SELECT
a.PTY_ID,
a.PTY1_NM,
a.PTY_TELEPHONE_NO,
a.PTY_FAX_NO,
a.PTY_EMAIL_ADR_XX,
o.LN_NO,
o.POC_PROCESSOR_ID

FROM RDX_BDE.T_PARTY a

join RDX_BDE.T_POINT_OF_CONTACT o
*** on o.POC_PROCESSOR_ID = a.PTY_ID
*** and o.POC_PROCESSOR_ID = a.PTY_ID
WHERE (((a.PTY_TITLE_XX) Like '%HPC%'));

MrAutomate

ASKER

Very responsive and helpful.