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: Yikes <Yikes_at_yahoo.com>
Date: Fri, 08 Dec 2000 13:13:38 -0800
Message-ID: <3A314F02.E03BA561@yahoo.com>

Thank you for your time.
Yes, interesting, but of course there are a lot of those types of interesting
examples here :-).

David Fitzjarrell wrote:

> 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 - 15:13:38 CST

Original text of this message

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