Finding MAX value row in a view

From: <Jim.Pickett_at_cubic.com>
Date: 1998/03/17
Message-ID: <Forum.890176017.1766.Pickett@ferret.corp.cubic.com>#1/1

Dear SQL Gurus :

I am trying to find a row in rqlg table with the greatest log_date and log_time value that matches the req_nbr field of the rqsd table.

Can I do a compound MAX statement of this type ?

Can someone help me with the syntax ?

Please see this "and" clause at the end of the view.

      and select  max (rqlg.log_date,rqlg.log_time)
             from  csiowner.rqlg 
            where rqsd.req_nbr = rqlg.req_nbr (+)

Thank you !

Complete view text follows:

Create or replace view TEST_VIEW as select

        oord.control_id,
        oord.part_nbr,
        part.part_desc,
        oord.qty_on_ord,
        oord.ord_nbr,
        rqsd.req_nbr,
        oord.ord_stat,
        oord.ord_type,
        oord.plan_last_open_date,
        oord.plan_recv_date,
        part.plnnr_id,                                                                                                              
        rqlg.log_date,
        rqlg.log_time,
        rqlg.log_dept,
        bprt.buyer_id
    from
        oord,
        part,
        rqlg,
        rqsd,
        bprt
    where oord.control_id    like '1542-91%'
      and oord.control_id    = rqsd.control_id(+)
      and (oord.ord_nbr not like 'A%' and oord.ord_nbr not like 'D%')
      and oord.ord_type = 'PO'
      and oord.ord_stat <> 'CL'
      and oord.ord_nbr       = rqsd.or_order_nbr(+)
      and oord.date_closed   = rqsd.date_closed(+)
      and oord.part_nbr      = part.part_nbr
      and oord.part_nbr      = bprt.part_nbr(+)

      and select  max (rqlg.log_date,rqlg.log_time)
             from  rqlg 
            where rqsd.req_nbr = rqlg.req_nbr (+)

/

---

  Jim Pickett
  Cubic Corporation

  Work: (619)505-2868
  Pager: (619)494-5539
  E-mail: jim_pickett_at_cubic.com   

                    03/17/98  15:00:57


======================================

Received on Tue Mar 17 1998 - 00:00:00 CST