Here is the scenario:
First, I do this select statement:
SELECT HISYSP.KDINDV4V.INDV_HRN,
HISYSP.KDINDV4V.PHNM_DISPL_NM,
HISYSP.KDINDV4V.INDV_DOB,
HISYSP.KDINDV4V.XXSEX_CD,
HISYSP.KDINDV4V.INDV_SSN,
HISYSP.KDADDR1V.ADDR_LN1,
HISYSP.KDADDR1V.ADDR_LN2,
HISYSP.KDADDR1V.ADDR_LN3,
HISYSP.KDADDR1V.ADDR_CITY,
HISYSP.KDADDR1V.XXSTATE_ID,
HISYSP.KDADDR1V.ZIP_CD
FROM HISYSP.KDINDV4V@dsnp KDINDV4V,
HISYSP.KDIADR2V@dsnp KDIADR2V,
HISYSP.KDADDR1V@dsnp KDADDR1V
WHERE HISYSP.KDINDV4V.XXRGN_ID = '12' AND
HISYSP.KDINDV4V.INDV_HRN = 9586613 AND
HISYSP.KDIADR2V.XXRGN_ID = HISYSP.KDINDV4V.XXRGN_ID AND
HISYSP.KDIADR2V.INDV_HRN = HISYSP.KDINDV4V.INDV_HRN AND
HISYSP.KDIADR2V.XXADDR_TYP_CD = 'RS' AND
HISYSP.KDIADR2V.IADR_EFF_FL = 'Y' AND
HISYSP.KDADDR1V.XXRGN_ID = HISYSP.KDINDV4V.XXRGN_ID AND
HISYSP.KDADDR1V.ADDR_ID = HISYSP.KDIADR2V.ADDR_ID
It's possible that the above select statement will return no results due to no matches in the KDIADR2V table.
If that happens, I still need the info from the KDINDV4V table - so I so this:
SELECT HISYSP.KDINDV4V.INDV_HRN,
HISYSP.KDINDV4V.PHNM_DISPL_NM, "
HISYSP.KDINDV4V.INDV_DOB,
HISYSP.KDINDV4V.XXSEX_CD,
HISYSP.KDINDV4V.INDV_SSN
FROM HISYSP.KDINDV4VV@dsnp KDINDV4V
WHERE HISYSP.KDINDV4V.INDV_HRN = 9586613
Then I run this one next - which may or may not have a matching record.
SELECT HISYSP.KDMEDI9V.INDV_HRN,
HISYSP.KDMEDI9V.MED_HIC_NBR
FROM HISYSP.KDMEDI9V@dsnp KDMEDI9V
WHERE HISYSP.KDMEDI9V.XXRGN_ID = '12' AND
HISYSP.KDMEDI9V.INDV_HRN = 9586613
I know that in VFP I could write this to use LEFT OUTTER JOIN a couple of times and get all of this in one SQL statement. I haven't worked with DB2 in a very long time and I don't remember the syntax well enough to do this DB2. Any of you guru's out here able to help me out here?
Thanks!
ICQ 10556 (ya), 254117