Re: how do I write this insert statement?

Hi.

 Try :
  INSERT INTO table_a
   SELECT tb.run_id, sysdate,null,tc.d1, tc.d2,'Message'    FROM ( SELECT MAX(run_id) run_id FROM table_b ) tb,

        ( SELECT MIN(load_date) D1, max(load_date) D2
           FROM table_c ) TC;

 HTH. Michael.

In article <7ttd9n$n9h$1_at_gaia.ns.utk.edu>,   zlotchen_at_voyager.rtd.utk.edu (David Zlotchenko 1) wrote:
> Hi, I need to insert a row into a table
> were some fields can defined using other tables.
> I have
> table_b: run_id NUMBER PRIMARY KEY, run_date DATE NOT NULL
> table_a: run_id NUMBER PRIMARY KEY REFERENCES table_b(run_id),
> run_data_start DATE NOT NULL,
> run_date_end DATE,
> load_date_lowrange DATE NOt NULL,
> load_date_hirange DATE NOt NULL,
> status VARCHAR2(200)
> table_c: load_date DATE, some other fields
>
> I wrote this statement:
> insert into table_a values (
> (select max(run_id) from table_b),
> sysdate,
> NULL,
> (select min(load_date), max(load_date) from table_c),
> 'status_message'
> );
>
> I get error on the expression that I hoped will get me value for
> table_a.run_id, and values for load_date_lorange and
load_date_hirange.
>
> I realize that in SQL, I can constract an artificial JOIN, but
> how to that with min max operations?
>
> In fact, I want to use this update in a procedure that gets
> only the value for status message.
>
> What is the proper solution?
>
> Thanks in advance. David.
>
> --
> --
> David Zlotchenko
> E-mail: zlotchen_at_cs.utk.edu
> WWW: http://www.oars.utk.edu/~zlotchen
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 11 1999 - 16:18:19 CDT