Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Converting from Ingres Reports to Oracle

Re: Converting from Ingres Reports to Oracle

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: Wed, 19 Jan 2000 15:11:30 -0000
Message-ID: <864k9s$j1k$1@kermit.esat.net>


Your problem is the difference in syntax between ingres and Oracle. You are using the Ingres syntax to update 1 table from another table. So, you nee dto translate the ingres update syntax to the Oracle update syntax. Also, you are not referencing the vehicles table anywhere. so in ingres your statement would be:

update load108_temp l
from vehicles
set vehicle_sname = vehicles.short_name where l.vehicle = vehicles.key_number

So, what you would need is this in oracle syntax:

Update load108_temp l
set l.vehicle_sname = (select v.shortname

                                            from vehicles v
                                           where l.vehicle = v.key_number)

apply this technique to the other update statements that are causing you a problem. Just make sure that this does exactly what you want before including it in your scripts. Technical wrote in message <8645q5$bc3$1_at_lure.pipex.net>...
>Dear All.
>
>I am an Oxford Brookes computing student on his placement year trying to
get
>to grips with Oracle. My job involves me converting Ingres reports over to
>Oracle using PL\SQL and SQLPLUS. I have a problem I was wondering you
could
>help me with.
>
>My problem is that the updates for the table in the script below do not
>work. They fail on the last line saying that personnel.key_number and
>vehicles.key_number are invalid column names (ORA-0903). I have checked
>everything I can think of, the columns exist, are spelt correctly, have the
>same datatypes of the same length, and it makes no difference if the
columns
>are null or not null. This might me a very simple error to someone with a
>little more experience than me. I would appreciate any help you can give
>me. This error is repeated across several scripts I have converted. None
>of the documentation I have has been very helpful.
>
>The worrying thing is that I am the person with the most Oracle experience
>in the office.
>
>Thanks for any help.
>
>From
>
>
>Lloyd Graney.
>
>(Script below)
>
>create table load108_temp
>(loadno number(11),
>day_of_use date,
>status number(6),
>status_text varchar2(80),
>vehicle number(6),
>vehicle_sname varchar2(8),
>driver number(6),
>driver_sname varchar2(8));
>
>(the table is created seperately from the insert/update commands)
>
>truncate table load108_temp;
>
>insert into load108_temp
>(loadno, day_of_use, status, status_text, vehicle, driver)
>select l.load_number,
> l.day_of_use,
> l.status,
> ll.text,
> l.vehicle,
> l.driver
>from language_text ll, load_states ls, loads l
>where ll.usage_code = 'LS'
>and ll.language_code = 'EN'
>and l.status = ls.key_number
>and ls.key_number = ll.key_text
>and upper(l.alt_flag) in ('Y', 'P');
>
>update load108_temp l
>set vehicle_sname = vehicles.short_name
>where l.vehicle = vehicles.key_number;
>
>update load108_temp l
>set driver_sname = personnel.short_name
>where l.driver = personnel.key_number;
>
>
>
Received on Wed Jan 19 2000 - 09:11:30 CST

Original text of this message

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