/* Formatted on 2008/10/01 13:43 (Formatter Plus v4.8.7) */
SELECT a.conernm, a.conercd, a.prodcd, a.seq, a.prodnm, b.dealday
  FROM (SELECT conercd, conernm, prodcd, seq, prodnm
          FROM conerprod_vw
         WHERE restcd = 'S11196A'
           AND prodcd = 'D0200014'
           AND seq = 'A001'
           AND conercd = 'CILA') a,
       (SELECT   conercd, prodcd, seq, MAX (dealday) AS dealday
            FROM sale_bp
           WHERE restcd = 'S11196A'
             AND prodcd = 'D0200014'
             AND seq = 'A001'
             AND tpanqty <> 0
             AND conercd = 'CILA'
        GROUP BY conercd, prodcd, seq) b
 WHERE a.conercd = b.conercd AND a.prodcd = b.prodcd AND a.seq = b.seq