Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with a script/procedure

Re: Need help with a script/procedure

From: David Fitzjarrell <oratune_at_aol.com>
Date: Fri, 08 Dec 2000 20:39:47 GMT
Message-ID: <90rgui$46p$1@nnrp1.deja.com>

In our last gripping episode Yikes <Yikes_at_yahoo.com> wrote:
> I've been asked to write a script and I must admit that my sql/plsql
> skills
> are a little on the light side being a pure operations type DBA :-)
>

That's an interesting "specialisation"...

> Basically the gist of it is the following:
>
> Need to pull data from two tables: customers, profile
>
> The customer table has a customer_id and a parent_id
> The profile has a customer_id
>
> I don't want data pulled if the customer.parent_id is null for that
> particular customer.customer_id
>
> If the customer_id is NULL in the profile table, I need to return a
> message indicating
> that there is No Profile for that Customer.
>

You are saying, then, that you have profile information that is not associated with a customer_id value...interesting...

> Sorry if this is unclear, but help would be appreciated.
>
> TIA
>
>

This could be implemented as either a PL/SQL script or as a SQL*Plus script. With SQL*Plus (please remember I am taking liberties with the column names as you did not specify any but the 'controlling' columns):

select c.customer_id, nvl(p.profile, 'No Profile Available') profile from customer c, profile p
where c.parent_id is not null
and p.customer_id (+) = c.customer_id;

With PL/SQL you could make it as complicated as you would like. As I haven't time to outline all possible solutions via PL/SQL I shall leave you with the above query, which works quite well.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Dec 08 2000 - 14:39:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US