Actually, I thought I was using a code from a query that is already in use here. However, now I have moved on, using your help--thanks. The next piece, which is the error I started my day with is in the FROM clause at the bottom of this big query. The first where clause in the FROM clause (where PA1.ADDRESS_TYPE = '1') results in this message: JOIN expression not supported.
select PATRON.TITLE,
PATRON.FIRST_NAME,
PATRON.MIDDLE_NAME,
PATRON.LAST_NAME,
' ',
' ',
' ',
' ',
'5692',
PATRON.BARCODE,
' ',
' ',
(Select PATRON_GROUP.PATRON_GROUP_NAME from
(PATRON_GROUP Inner Join PATRON_BARCODE on
PATRON_GROUP.PATRON_GROUP_ID = PATRON_BARCODE.PATRON_GROUP_ID) inner join
PATRON ON PATRON_BARCODE.PATRON_ID =
PATRON.PATRON_ID) AS borrowerCategory,
format(PURGE_DATE ,'YYYY-MM-DD'),
(Select location_Code from LOCATION inner join
patron on
PATRON.home_location=location.location_id) AS
regionalLib,
PA1.ADDRESS_LINE1,
PA1.ADDRESS_LINE2,
PA1.CITY,
PA1.STATE_PROVINCE,
PA1.ZIP_POSTAL,
PA1.COUNTRY,
(select PATRON_PHONE.PHONE_NUMBER
FROM ((((PATRON LEFT JOIN
VOYAGER_DATABASES ON PATRON.DB_ID =
VOYAGER_DATABASES.DB_ID) INNER JOIN
PATRON_ADDRESS ON PATRON.PATRON_ID =
PATRON_ADDRESS.PATRON_ID) INNER JOIN
PATRON_BARCODE ON PATRON.PATRON_ID =
PATRON_BARCODE.PATRON_ID) INNER JOIN
PATRON_GROUP ON
PATRON_BARCODE.PATRON_GROUP_ID =
PATRON_GROUP.PATRON_GROUP_ID) INNER
JOIN PATRON_PHONE ON
PATRON_ADDRESS.ADDRESS_ID =
PATRON_PHONE.ADDRESS_ID) AS patronPhone,
' ',
PA2.ADDRESS_LINE1,
PA2.ADDRESS_LINE2,
PA2.CITY,
PA2.STATE_PROVINCE,
PA2.ZIP_POSTAL,
PA2.COUNTRY,
PA3.ADDRESS_LINE1
FROM
(( Patron P
INNER JOIN Patron_Address PA1 ON
PA1.patron_id = P.patron_id AND
PA1.address_type = '1')
LEFT OUTER JOIN Patron_Address PA2 ON
PA2.patron_id = P.patron_id AND
PA2.address_type = '2')
LEFT OUTER JOIN Patron_Address PA3 ON
PA3.patron_id = P.patron_id AND
PA3.address_type = '3';
What I am trying to accomplish is to provide information from 1-3 rows in the PATRON_ADDRESS table and they are distinguished by the address type. So I need to get each row for every PATRON_ID in the PATRON tale. There will always be an ADDRESS_TYPE '1' record, but the others might not be there.