Gossamer Forum
Home : General : Databases and SQL :

Complex Query: Oracle 8i

Quote Reply
Complex Query: Oracle 8i
I have a complicated query that is based on a series of JOINS between various parent and child tables. Basically, the part of the query that is giving me the biggest headaches is with the OUTER JOIN between two sets of tables.

Here is the query:


FROM tbl_Demo_Spec DS,
tbl_Offer O,
tbl_Offer_Demo OD,
tbl_Offer_Spec OS,
tbl_Offer_Site OST,
tbl_Person P,
tbl_Person_Demo PD,
tbl_PartnerOffers PO
WHERE (O.Offer_Page = 1)
AND (O.Offer_Hidden = 0)
AND (O.Offer_ExpireDate <= (sysdate))
AND (O.Offer_Active = 1)
AND (OS.OfferTypeID = 1)
AND (OST.SiteID = 2)
AND (P.PersonID = 245)
AND (O.OfferID = OS.OfferID)
AND (O.OfferID = OST.OfferID)
AND (O.OfferID = OD.OfferID)
AND (O.OfferID = PO.OfferID)
AND (OD.DemoID = PD.DemoID(+))
AND (OD.DemoID = DS.DemoID(+))
ORDER BY O.Offer_Page

For those not familiar with Oracle syntax, the last two conditions in the WHERE clause are OUTER JOINS denoted by (+). Also, in Oracle, JOINS use the (Tbl1.PK -> Tbl2.FK) construct.

I have tried UNION and UNION ALL functions, which didn't make any difference in the rowcount returned, other than taking much longer to process.

The basic function of this query are:

1) Pull offers based on Page #, Date, Active, Type and their associated PartnerOfferID (which is a 1->N between Offers and PartnerOffers with the FK of OfferID).

2) Check the offer demographics against person demographics, which are based on an "ID" - DemoID (superkey value of DemographicID and DemoAnswerID).

If anyone can spot any gross errors with the above query, I'd greatly appreciate...I'll keep plugging away at my PL/SQL book in the meantime.
Buh Bye!


Last edited by:

Stealth: Oct 15, 2002, 3:08 PM
Quote Reply
Re: [Stealth] Complex Query: Oracle 8i In reply to
Are you just looking for a way to improve it or is there some sort of problem with the query?
Quote Reply
Re: [Paul] Complex Query: Oracle 8i In reply to

But first get the query to work correctly, since it should return records.

But I did figure out another approach, which is the following:

1) Pull records from Person and Person_Demographics.
2) Then loop through the Offers query referring to the DemoID(s) pulled from the first query.

While more intensive, I am at least getting better results. Although now I have to figure out how to show records from the looped queries based on meeting one or all the conditions in the looped queries (not a simple matter of using OR operator in the WHERE clause).

BTW: For those Oracle developers out there, I am using the joins in the WHERE clause rather than the special join syntax that could be used in the FROM section because some of our sites use SQL Server and I am trying to keep the codes as portable as possible.

Anyway, thanks for the reply.
Buh Bye!

Quote Reply
Re: [Stealth] Complex Query: Oracle 8i In reply to
Where Querries Get Complicated Create a View And Then Perform The Required Querry On The View Makes Life Easier For Complicated Querry And Performace of complicated Joins


=_= =_= =_= =_= =_= =_=

You can chain me, you can torture me, you can even destroy this body, but you will never imprison my mind.
-Mahatma Gandhi