Re: how do I write this insert statement?

I assume by your query that you are trying to insert one row into table a, which gets data from one row in table b (max rowid) and data from one result in table c (min and max load dates)... If this is true, then what you want is the cartessian join of two result tables which each contain one result.
You may use inline tables for this:
Select
  MaxRun,Sysdate,NULL,MinLoad,MaxLoad,'MyStatusMessage' From
  (select

     max(run_id) MaxRun
   from table_b) AA,
  (Select

     min(load_date) MinLoad,
     max(load_date) MaxLoad
   From Table_c) DateTbl

   MAXRUN SYSDATE N MINLOAD MAXLOAD STATUS --------- --------- - --------- --------- ---------------

      999 11-OCT-99 14-DEC-93 23-FEB-99 MyStatusMessage

The insert statement becomes:
Insert Into Table_a
  (run_id, run_date_start, run_date_end,    load_date_lowrange, load_date_hirange,    status)
(
Select
  MaxRun,Sysdate,NULL,MinLoad,MaxLoad,'MyStatusMessage' From
  (select

     max(run_id) MaxRun
   from table_b) AA,
  (Select

     min(load_date) MinLoad,
     max(load_date) MaxLoad
   From Table_c) DateTbl

Notice you do not use VALUES when a Select statement is used.

Robert Proffitt
Beckman Coulter
Brea California
RTProffitt "AT" beckman "DOT" com

    (replace words with symbols)

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:33:45 CDT