Re: Problem with Multiple Join

Dirk,

I think you should be able to use your function in a where clause: WHERE function_name(order_id) like '%Doe,John%'

Pat.

On 28 Aug 2001 17:56:14 -0700, al_meilinger_at_bigfoot.com (Dirk) wrote:

>All,
>
> Environment: Oracle 8i
>
> I have a question concerning a problem I have been trying to solve
>for several weeks.
>
>I am trying to write a query to report on sales figures. Without
>going into extreme details, I have 4 basic tables: Orders,
>Order_items, Sales_reps, and Sales_branches.
>
>All have order_id as a key and I am trying to collect all information
>with one query. Orders is 1:1 to sales_branches, 1:M to order_items,
>and 1:M to Sales_reps.
>
>
>I have written a query that joins Orders, Order_items, and
>sales_branches which works fine. I would also like to obtain sales
>reps but when I join that table as well I of course get the product of
>items and sales_reps, in other words if an order has 2 sales reps, I
>get twice as many line items, 3 sales reps 3x the line items etc.
>
>I solved the problem by writing a function that returned a
>comma-delimited list of the sales reps for a given order as a column
>in my original query, now the problem is that I cannot search in my
>WHERE clause for sales_rep LIKE XXX as Oracle tells me it is an
>invalid column (I guess since it is derived from a function?).
>
>Am I going about this the wrong way, am I missing and easier solution?
>
>Any help greatly appreciated.
>
>Dirk Meilinger
Received on Thu Aug 30 2001 - 12:16:48 CDT