Insert into new table

[nuttyernurse]

Hello all!

I am trying to create new data in a table using a data field from another table. My IS department helped me with this following statement but it does not work and we are all scratching out heads:

INSERT INTO o_alias (alias, order_item_seq, alias_seq,is_order_name)
values ('Ultrasound', Get order_item_seq
FROM o_item
WHERE order_name like 'ULT%', alias_seq.nextval, 0)

The alias_seq is the PK for this table and needs to be sequential.

This is an example of actual data:
alias ULT Placental Local
o_item_seq 207508
alias_seq 113871
is_order_name 1

If the alias is not the same as the order name, then it has the value of 0.

Any help is appreciated!

 

[simian336]

I believe you want a select into....

INSERT INTO o_alias (alias, order_item_seq, alias_seq, is_order_name)
select 'Ultrasound', Get order_item_seq, *Need 2 more fields
FROM o_item
WHERE order_name like 'ULT%'


The number of selected fields needs to match the number of fields you are inserting them into.

Not sure about what you wanted on the where clause.

Simi

 

[ousoonerjoe]

Try something more along the lines of:

INSERT INTO o_alias (alias, order_item_seq, alias_seq,is_order_name)
    SELECT 'Ultrasound', order_item_seq
    FROM o_item
    WHERE order_name like 'ULT%', alias_seq.nextval, 0)

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[nuttyernurse]

Thank you both for you suggestions. Unfortunately neither one worked.

I think I need to re-explain what I am trying to do. I need to insert new data into the o_alias table. This data is an alias for the orginal order item in the o_item table. I need to pull the order_item_seq number from the o_item table into the o_alias table. I am trying to use the condition of order_name like ULT% to pull only those order_item_seq numbers for ultrasound orders. While creating these new enttries, the alias_seq is the primary key and needs to be sequential from the previous key.

If that aint clear as mud, I don't know what is!

Again, I appreciate any and all help.

 

[ousoonerjoe]

Best I can do with out having my coffee yet.

INSERT INTO o_alias (alias, order_item_seq, is_order_name)
    SELECT 'Ultrasound', order_item_seq, 'Add Order Name field'
    FROM o_item
    WHERE order_name like 'ULT%';

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------