Re: Finding MAX value row in a view

On Tue, 17 Mar 98 15:00:56 -0800, Jim.Pickett_at_cubic.com wrote: Hi,

first of all since Oracle's date-fields are real timestamps, there is no need to separate date and time in different columns since, depending on how you fill the column, your time-field will also get a date part.

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,
	select  max (rqlg.log_date) maximum
             from  rqlg  maxdate
    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 
		rqsd.req_nbr = rqlg.req_nbr  
	and
		rqlg.log_date = 	maxdate.maximum
	and
		rqlg.log_time = Select max(log_time) 
				from
					rqlg
				where
					rqlg.log_date = 	maxdate.maximum
	,

The last 'and'_clause could be ommitted if log_date would also contain the time-portion. If log_time also contains a date-portion you should alter the 'and'-clause to

			Select max(to_date(to_char(log_time,HH24:MI))) 
				from
					rqlg
				where
					rqlg.log_date = 	maxdate.maximum

to eliminate different date-portions. This will then lead to a full table scan on rqlg.

>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
>======================================
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de