You are really close, but need one extra step.
In the joined sub-query, you actually need to name the sub-query itself which creates a virtual table with that name. Then link that virtual table as normal.
e.g., in the below I named the sub-query ‘dzn_subquery’ and changed the joins on dzn to dzn_subquery (all changes are on the bottom line).
inner join ( select dzn.acct_nbr_id, dzn.order_no, t.act_open_date from tb_dzn_order_scd dzn GROUP BY dzn.acct_id , dzn.order_nbr -- changed following line ) AS dzn_subquery on a.acct_id = dzn_subquery.acct_nbr_id and a.order_no= dzn_subquery.order_no
CLICK HERE to find out more related problems solutions.