Outer Joins

From: <rraghava_at_my-dejanews.com>
Date: Wed, 07 Apr 1999 18:31:58 GMT
Message-ID: <7eg8au$gm7$1@nnrp1.dejanews.com>

Hi Folks,

The following is the result of the query I need and it works fine.

SELECT a.prop_id,b1.tran_date,b2.xxx,c.ln_amt FROM property a,

       transaction b1,
       transaction b2,
       mortgage c

where a.prop_id = b1.prop_id(+)
and a.prop_id = b2.prop_id(+)

and    b1.tran_code(+) = 'X' /* Reference 1*/
and    b2.tran_code(+) = 'Y' /* Refrence 2*/
and    b2.tran_id = c.tran_id(+)

But, note the areas commented by Reference 1 and Reference 2. I need to determine the value 'X' and 'Y' in Reference 1 and Reference 2 through subqueries( Its actually the max(tran_code) from transaction table for 'X' and the second max(tran_code) for 'Y' from transaction.

But there may be cases where only one transaction exists and the second max will not apply. In that case I dont want the query to fail. So my query should actually look like this:

SELECT a.prop_id,b1.tran_date,b2.xxx,c.ln_amt FROM property a,

       transaction b1,
       transaction b2,
       mortgage c

where a.prop_id = b1.prop_id(+)
and a.prop_id = b2.prop_id(+)
and b1.tran_code(+) = (select max(tran_code) from transaction

                          where tran_code < b2.tran_code)
                          and prop_id = a.prop_id) /* Reference 1*/
and    b2.tran_code(+) = (select max(tran_code) from transaction
                          where prop_id = a.prop_id) /* Refrence 2*/

and b2.tran_id = c.tran_id(+)

Now this is wehere I have a problem. Outer Joins are not allowed for reference1 and reference2 as they have subqueries on the right side.

Could someone tell me how else I could write this query to get the desired results.

Thanks in advance.
Raghavan

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 07 1999 - 13:31:58 CDT