Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP - PL/SQL
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