outer join in view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: outer join in view

  1. #1
    Join Date
    Oct 2001
    Posts
    7

    Question

    please help!!! In concept, I think I need an outer join with a table to create this view, because there may or may not be records in this one table and if there are no records in this one table, no data is being pulled for the entire view. i used an outer join for 2 other 'and' statements with this table and it works fine if i have 0 or 1 record, however, i also need to only select the max id record (it is possible to have multiple records) from this table and this is causing the trouble. so, i added a statement to select the max and this is what is giving me a problem... if there are no rows in this table, NO data is pulled for a specific record in other tables. here is the problem statement:

    and b.i_detail_id = (select max(k.i_detail_id)
    from IMT_DET_HST k
    where k.jur_id = b.jur_id
    and k.fis_yr = b.fis_yr)

    i am only interested in the data for the most current (i.e. max ID) record, so I do not want to take this statement out and have multiple records in my view if there are multiple records in the i_detail_hist table. however, i DO want the rest of the information in the view if there are NO records in the i_detail_hist table. any thoughts? (using Oracle8i on Win2000)

    for reference, here is the entire create view statement. I just added the ** to easily identify the line in question:

    CREATE OR REPLACE VIEW V_USER_APPLICATION AS
    select a.CO_SALARY_AVG,
    a.CURR_SUBMIT_DT,
    a.IDAYS_TTL,
    a.IPOP_AVG,
    b.TTL_RECORD_NBR,
    c.CREATE_DT,
    h.STATE_DESC,
    (e.J_TYPE_DESC||' of '||d.LEGAL_J_NAME),
    f.STATE_TERR_ABB,
    l.LAST_NAME,
    g.TITLE,
    (((b.ttl_record_nbr - b.valid_record_nbr)/b.ttl_record_nbr)*100),
    ((a.CO_SALARY_AVG/i.mean_amt)*100),
    a.jur_id,
    a.fis_yr,
    n.fis_yr
    from FY_APP a,
    IMT_DET_HST b,
    JU_ST_HST c,
    J_PROFILE d,
    J_TYPE e,
    STATEf,
    USER_PROFILE g,
    USER_PROFILE l,
    J_USER_STATE h,
    YEARLY_STATE_MEAN i,
    ADDRESS m,
    FY_SYSTEM_ACCESS n
    where d.j_type_id = e.j_type_id
    and a.jur_id = d.jur_id
    and f.state_terr_id = m.state_terr_id
    and m.address_id = a.j_address_id
    and a.c_user_id = g.user_id
    and c.jur_id = a.jur_id
    and c.fis_yr = a.fis_yr
    and c.state_id = (select max(j.state_id)
    from JU_ST_HST j
    where state_id in (7,8))
    and a.jur_id = b.jur_id(+)
    and a.fis_yr = b.fis_yr(+)
    ** and b.i_detail_id = (select max(k.i_detail_id)
    from IMT_DET_HST k
    where k.jur_id = b.jur_id
    and k.fis_yr = b.fis_yr)
    and i.state_terr_id = m.state_terr_id
    and i.fis_yr = (n.fis_yr - 1)
    and h.state_id = a.curr_j_user_state_id
    and l.user_id = c.create_id
    and n.fis_yr = (select max(fis_yr)
    from fy_system_access);


    ANY HELP TRUELY APPRECIATED! CAN THIS BE DONE?? THANKS!!


  2. #2
    Join Date
    Oct 2001
    Posts
    7

    Unhappy

    (sorry, formatting looks much nicer in my message text when typing! didn't come through very nicely!!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width