Re: how do I write this insert statement?

David Zlotchenko 1 schrieb:

> 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.

insert into table_a
select

    tab_a.max_run_id,
    sysdate,
    NULL,
    tab_c.min_load_date,
    tab_c.max_load_date,
    'status_message'
from

    (select max(run_id) max_run_id from table_b) tab_b,     (select min(load_date) min_load_date, max(load_date) max_load_date from table_c) tab_c;

I hope this is what you need. Let me know!

Regards, Stephan

--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------