Re: How to modify the view to set one field with min value of its field

mullin wrote:
> Now, after running the orginal view, i will have records with same
> db_tran_id and those may have different a.ADUIT_DATE.
> But, I want to make them have the min a.AUDIT_DATE for all records
> with same_tran_id when select from the view:
>
> Anyone can help me for the following
>
>
> Original View
> ==============
> CREATE OR REPLACE VIEW VW_RPT_AUDIT_ROOT_SYMBOL
> (COMM_CODE, USER_ID, AUDIT_DATE, AUDIT_TIME, TYPE,
> DB_TRAN_ID, TOPIC, TOPIC_KEY, SUBTOPIC, SUBTOPIC_KEY,
> DESCRIPTION, SEQ_NO, DATA_TYPE, DEFAULT_VALUE, PRECISION,
> NEW_VALUE, OLD_VALUE)
> AS
> select det1.text_1 as business_key,
> u.user_id,
> to_date(to_char(a.AUDIT_DATE,'YYYYMMDD'),'YYYYMMDD'),
> to_char(a.AUDIT_DATE,'HH24:MI:SS'), a.DB_ACTION, a.db_tran_id,
> a.topic, a.topic_key, a.subtopic, a.subtopic_key,
> fd.description, fd.seq_no, fd.data_type, default_value,
> precision, det2.text_1 as value1, det2.text_2 as value2
> from sds_audit_log a, scs_user u, sds_audit_log_detail det1,
> sds_audit_log_detail det2, sds_audit_rpt_field fd
> where a.topic = 'SDS_ROOT_SYMBOL'
> and a.subtopic = 'SDS_ROOT_SYMBOL'
> and a.id = det1.audit_id
> and det1.KEYWORD = 'SDS_ROOT_SYMBOL'
> and det1.FIELD_NAME = 'COMM_CODE'
> and a.id = det2.audit_id
> and a.update_user = u.id
> and fd.topic = a.topic
> and fd.subtopic = a.subtopic
> and fd.keyword = det2.keyword
> and fd.field_name = det2.field_name
> and (det2.text_1 IS NOT NULL OR det2.text_2 IS NOT NULL)
>
> Way to get the min of a.AUDIT_DATE and its db_tran_id
> ======================================================
> select min(a.audit_date), db_tran_id from sds_audit_log a
> where a.topic = 'SDS_ROOT_SYMBOL'
> and a.subtopic = 'SDS_ROOT_SYMBOL'
> group by a.db_tran_id

Then use that as an inline view in your main view's query and draw the min date value via a join on db_tran_id from there.

    robert Received on Thu Sep 01 2005 - 07:53:21 CDT