Re: HELP - PL/SQL

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/27
Message-ID: <333a6240.1461050_at_newshost>#1/1

On Wed, 26 Mar 1997 20:53:08 -0600, "Robert J. Huber" <huberrj_at_apci.net> wrote:

>This is what I'm trying to do:
> From the first table I want to select all the values from field1. Then
>I want to pull all records which contain this value from table2. This
>is no problem; however, if the value from table_name1, field1 is not in
>table_name2 I still need to pull the data from table_name1 and then
>create null values for the fields from table_name2, or atleast still
>pull the data from table_name1. The following procedure is what I came
>up with. The IF statement doesn't work because the statement must be a
>unary operator, boolean or some declared variable, but the logic is
>there. Any help greatly appreciated. EMAIL ME!
>In SQL the first part of the IF statement would be:
>
>

Looks like an outer join to me:

  select field1, field2, field#
    from table_name1, table_name2
   where table_name1.join_column = table_name2.join_column (+)

the (+) on the join condition will cause the query to 'make up' a null row for table_name2 in the event the join condition is not satisfied. It is logically equivalent to:

select field1, field2, field#
  from table_name1, table_name2
 where table_name1.join_column = table_name2.join_column UNION ALL
select field1, field2, NULL
  from table_name1
 where table_name1.join_column NOT IN ( select join_column

                                          from table_name2 )

Just a lot faster in most cases.

You can't use sql in if/then/else statements in pl/sql like you have below. You would instead code something like:

select count(*) into n
  from dual
 where VALUE in ( select VALUE from TABLE );

if ( n > 0 ) then

   .....
else

   .....
end if;

>create procedure get_geo IS
>BEGIN
> IF (field1_table_name1 in (select field1_table_name2 from table_name2))
> THEN
> select field1, field2, field# from table_name1, table_name2;
> ELSE
> select field1, field2 from table_name1;
> END IF;
>END
>--
>Robert J. Huber
>
>huberrj_at_apci.net - home
>huberrj_at_hqamc.safb.af.mil - office

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Mar 27 1997 - 00:00:00 CET