Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL help needed
A copy of this was sent to "Thomas Klinger" <t.klinger_at_mobilkom.at>
(if that email address didn't require changing)
On Mon, 31 May 1999 11:56:45 +0200, you wrote:
>Hi there!
>
>I've written this script:
>
>begin
> select bts.name_bsc BSC,
> bts.name_bts BTS,
> bts.addr_bts ADDRESS,
> bts.ci CI,
> bts.trx1 TRX1,
> bts.frt1 FRT1,
> trx2.trx2 TRX2,
> trx2.frt2 FRT2,
> trx3.trx3 TRX3,
> trx3.frt3 FRT3,
> trx4.trx4 TRX4,
> trx4.frt4 FRT4,
> trx5.trx5 TRX5,
> trx5.frt5 FRT5,
> trx6.trx6 TRX6,
> trx6.frt6 FRT6
> from BTS_TRX_TEMP bts,
> TRX_FRT_2_TEMP trx2,
> TRX_FRT_3_TEMP trx3,
> TRX_FRT_4_TEMP trx4,
> TRX_FRT_5_TEMP trx5,
> TRX_FRT_6_TEMP trx6
> where bts.bts_id in (select bts2.bts_id from BTS_TRX_TEMP bts2)
> and bts.bts_id = trx2.bts_id
> and bts.bts_id = trx3.bts_id
> and bts.bts_id = trx4.bts_id
> and bts.bts_id = trx5.bts_id
> and bts.bts_id = trx6.bts_id
> order by bts.name_bts;
>
> if bts.bts_id not in (select trx21.bts_id from TRX_FRT_2_TEMP trx21) then
> trx2.trx2 := '-';
> trx2.frt2 := '-';
> end if;
>end;
>
>Q1: is this acceptable (executeable)?
nope
>Q2: I can't start it. I type in "@myscript.sql" but comes up with the next
>line numbers even if I type in a semicolon -> next line. Why?
>
need a SLASH "/" at the end to run plsql blocks in sqlplus.. for example:
SQL> begin
2 dbms_output.put_line( 'Hello World' );
3 end;
4 /
Hello World
PL/SQL procedure successfully completed.
I'm not sure exactly what you are trying to do in the above. You have a "SELECT" but you don't select it into anything, nor are you using a cursor (so it has no place to go). Also, you are refering to bts.bts_id in an IF statement -- but bts is a table alias, not a plsql variable. You also assign to trx2.trx2 and frt2 -- but again trx2 is a table, not a variable.
It *looks* like you might want to update every row in trx2 that satisfies the query AND bts.bts_id is not in that other table? If so, and you wanted to do it in plsql, it could look like this:
begin
for c1 in ( select bts.name_bsc BSC, bts.name_bts BTS, bts.addr_bts ADDRESS, bts.ci CI, ... (your other columns here) trx6.frt6 FRT6, TRX2.ROWID RID -- <<< new column here from BTS_TRX_TEMP bts, TRX_FRT_2_TEMP trx2, TRX_FRT_3_TEMP trx3, TRX_FRT_4_TEMP trx4, TRX_FRT_5_TEMP trx5, TRX_FRT_6_TEMP trx6 where bts.bts_id in (select bts2.bts_id from BTS_TRX_TEMP bts2) and bts.bts_id = trx2.bts_id and bts.bts_id = trx3.bts_id and bts.bts_id = trx4.bts_id and bts.bts_id = trx5.bts_id and bts.bts_id = trx6.bts_id order by bts.name_bts ) loop select count(*) into l_cnt from dual where NOT EXISTS ( select NULL from trx_frt_2_temp trx21 where trx21.bts_id = C1.btx_id ); if ( l_cnt = 1 ) then update trx_frt_2_temp set trx2 = '-', frt2 = '-' where rowid = C1.RID; end if;
end;
or something similar.
The above
You might consider breezing through the plsql manual -- especially the control structures sections, to get a feeling for plsql and such.
>This script should select from 5 different tables where not all values
>bts.bts_id are in each from the other tables. So there I tried to do a
>PL/SQL script do prevent that only those rows are selected where bts.bts_id
>is in all tables.
>Or is there any other possiblilty?
>
>Thanx in forward.
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon May 31 1999 - 15:35:24 CDT
![]() |
![]() |