oracle version problem (update with subselect)

From: Franz Stancl <ora_at_fsgw.ipodion.at>
Date: Tue, 05 Mar 2002 16:14:28 GMT
Message-ID: <E96h8.314760$V52.3144984@news.chello.at>

I have three tables in oracle and want to update one of them according to the content of the two others. My update statement works fine under Oracle V8.1.5, but fails under Oracle 7.3.4

The statement is:
update ip_eq ip1 set active_chk=' '
where
(select value1 from ip_eq_attr ipa1

   where ipa1.eq_name=ip1.eq_name
   and ipa1.attr='#BATCHSERVER')=
(select f1 from load_klassen_raw where rownum=1)
and typ='KLASSE';

As said, no problem under Oracle8 but it fails with ORA-00936: missing expression at the first "(select..." under Oracle7.

Anyone knows what could cause this problem? Or what Oracle8 implicitly does here and Oracle7 not?

For those who want to know what I am trying to accomplish: I have three tables:

ip_eq:

eq_name active_chk typ

---

name1      X             KLASSE
name2      X             UNIT
name3                    KLASSE

...

ip_eq_attr:

eq_name attr value1

---

name1      #BATCHSERVER  server1
name1      #ATTR2        smthg
name2      #BATCHSERVER  server1
name2      #TIME         smthg
name3      #BATCHSERVER  server2

...

load_klassen_raw:

f1 f2

---

server1 name1
server1 name3
...

(rem: f1 contains the same value for all rows)

load_klassen_raw was filled through an import via sql*loader and the working table ip_eq should be updated according to the loaded data. the first step is to set all active_chk fields in ip_eq to ' ' like this pseudo-sql:

update ip_eq set active_chk=' '
where
ip_eq_attr contains the same #batchserver for the equipment (eq_name) as in first row of load_klassen_raw

so in this example, row1 should be set to ' '.

I know, the structure of the tables could be better, but it is not possible to change that...

thanks in advance,
--franz Received on Tue Mar 05 2002 - 10:14:28 CST