SELECT DISTINCT AC.CUST_NBR
FROM DB2INST1.ACCOUNT.AC
INNER JOIN DB2INST1.PRODUCTS PR ON AC.CUST_NBR = PR.CUST_NBR AND AC.ACCOUNT_NBR = PR.ACCOUNT_NBR
WHERE PR.TYPE IN (26,27) AND
AC.OPEN_DATE <= '2019-01-01' AND
(AC.CLOSE_DATE >= '2019-01-01' OR
AC.CLOSE_DATE IS NULL)
ASKER
SELECT DISTINCT AC.CUST_NBR
FROM DB2INST1.ACCOUNT.AC
INNER JOIN DB2INST1.PRODUCTS PR ON AC.CUST_NBR = PR.CUST_NBR AND AC.ACCOUNT_NBR = PR.ACCOUNT_NBR
LEFT OUTER JOIN DB2INST1.FEES FEE ON AC.CUST_NBR = FEE.CUST_NBR
WHERE PR.TYPE IN (26,27) AND
AC.OPEN_DATE <= '2019-01-01' AND
(AC.CLOSE_DATE >= '2019-01-01' OR
AC.CLOSE_DATE IS NULL) AND
FEE.TYPE = 'LATE'
ASKER
WITH customerlist as (
<your customerlist query>
), customerfees as (
<your customerfees query which could be a group by query to sum upp all the fees>
)
select .... from customerlist cl, customerfees cf
where cl.cust_nbr = cf.cust_nbr ...
ASKER
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
TRUSTED BY
If you only want customers with fees, remove the "left".