SQL & PL/SQL

Hi all, I have a bit of code using the standard string concatenation connect by code, but I also want to include other columns from other tables for example:

Select c.id,
         c.name,
         cp.type
         cp.number
         add.Addressline
  from customer c,
         cus_phone cp
      ,(select cus_id, 
               ltrim(max(sys_connect_by_path(Address,' ~ ')) keep (dense_rank last partition by cus_id),' ~ ') addressline
        from      (select cus_id, address, row_number() over (partition by cus_id order by seq_no) as rn
                   from   customer_address)
        connect by cus_id = prior cus_id and rn = prior rn + 1
        start with rn = 1
        group by   cus_id) add
 where c.id = add.cus_id;

Now the problem with this is it does a full table scan on the customer_address table. when I insert a "where cus_id = c.id" in the connect by view it obviously doesn't know what it is. "invalid identifier"

is there anyway I can inject that predicate to prevent the full table scan but still keep it all in one query. (preferably without using model clause aggregation).