how do I write this insert statement?

From: David Zlotchenko 1 <zlotchen_at_voyager.rtd.utk.edu>
Date: 11 Oct 1999 19:20:23 GMT
Message-ID: <7ttd9n$n9h$1@gaia.ns.utk.edu>

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 Received on Mon Oct 11 1999 - 14:20:23 CDT