Help with Oracle Analytic Functions



  • Hello,

     

    I have come to seek some help about an oracle querry thats driving me crazy... I managed to return the results i was supposed to return, but I feel like posting the querry here would result in an front page feature for myself ;)

     Anyway.. here is the setup and the requirement (simplified):

    Tables:

    1. Requirements (ID,TargetID,TargetType)

    2. Deadline (ID,Req_ID,Deadline_Date, Status_ID)

    3. Status (ID, Name) (Just a lookup)

    4. Target (ID, Status_ID) (Might be "missing" from the requirement)

    - Each Requirement has N  Deadlines

    - The Status_ID of the Target tells us which is the current Deadline_Date

    - If the Target is missing, return the the 1st Deadline date (min)

    Question:

    Return me the next Deadline Date for all Requirements

    if the Target is missing, return the minimum deadline date for that requirement

    If the target exists, return the requirement date that is bigger then the current status of the deadline

     

    Can anyone give me some help regarding the analytic function of oracle for this - The solution i am currently

    looking at uses a nasty subquerry with case statements to destinguish between the cases?

     

     

    Thanks a lot in advance

     

    rdrunner



  • I must be mis-reading what you said... 

    If the TARGET entry is missing, and that's the only link from the REQUIREMENTS table to the DEADLINE one, how is it possible to return the minimum date for that requirement?

    You could return the minimum date of all deadlines I suppose, but unless I'm reading this wrong you can't match it to the requirement at all unless TARGET IS there?

     

    Could probably do what you want with a UNION, with one half returning results where there is an Existing ID, the other half returning a linked date...  Sorta like:

    select  r.requirement, max(d.deadline_date) as DeadlineDate

    from requirements r

    left outer join target t on (t.id=r.target_id)

    left outer join deadline d on (d.status_id=t.status_id)

    where (t.id is not null) 

    group by r.requirement

    UNION

    select  r.requirement, (select min Deadline_date from deadline d) as DeadLineDate

    from requirements r

    left outer join target t on (t.id=r.target_id)

    where (t.id is null) 

    Been a while since I've done anything in oracle, but I'd do it more or less like this in Firebird (which is what I tend to use all the time now), but from what I remember this should work in whoracle too.... I've not tested it or anything, that's just off the top of my head.

     



  • Some test data (a few records per table) and the expected result would've been nice.

    l.
     



  • Have a Look  at asktom.oracle.com several examples that will likely cover your needs.



  • I thin there can frist select all right next Deadline date, I think you can try this:

    select a.reg_id,b.deadline_date
    from requirements a,(
    select a.id,a.reg_id,a.deadline_date
    from
    (
    select a.id,a.reg_id,a.deadline_date
    from deadline a,
        (select a.* from deadline a,target b where a.Status_ID=b.Status_ID) b
    where a.reg_id=b.reg_id and a.deadline_date>=b.deadline_date
    union
    select a.id,a.reg_id,a.deadline_date
    from deadline a
    where a.Status_ID not in (select Status_ID from target)
    ) a
    group by a.id,a.reg_id,a.deadline_date
    ) b
    where a.reg_id=b.reg_id

    good luck.

     


Log in to reply