topic Re: INSERT STATEMENT in Operating System - HP-UX

INSERT STATEMENT

Sander Derix — Wed, 02 Oct 2002 12:58:17 GMT

Hi group,

I have a question regarding an INSERT STATEMENT.

We use an insert statement
INSERT INTO MyTable(
field 1,
field 2,
...
...
field 20
VALUES(
Onhand_rec.upn,
...
...
onhand_rec.qty
...
);

However I only want records with a quanty (onhand_rec.qty)>0.

My quick and dirty solution was:
DELETE FROM MYTABLE
WHERE qty =0;

Is it possible to check the value of QTY with the above INSERT STATEMENT?

Regards,

Sander

Re: INSERT STATEMENT

harry d brown jr — Wed, 02 Oct 2002 13:44:25 GMT

WHERE qty > 0

in the insert statement??

Or use perl

live free or die
harry

Re: INSERT STATEMENT

Ian Dennison_1 — Wed, 02 Oct 2002 13:47:03 GMT

insert into mytable (select upn, ... ..., qty from onhand_rec where qty > 0);

This assumes that the columns from mytable match onhand_rec in their format, otherwise you will need to insert default values as text in the select statement, or format conversions on the fields.

My recommendation:- DBA or SQL course, as soon as!

Share and Enjoy! Ian

Re: INSERT STATEMENT

Sander Derix — Thu, 03 Oct 2002 06:35:54 GMT

Hi IAN,

My mistake i didn't say that onhand_rec is a record in a cursor called onhand_cur.

So again:
is it possible to create something like this:
INSERT INTO MyTable
...
...
qty
...
VALUES
(SELECT
...
...
onhand_rec.qty
...
FROM onhand_cur
WHERE onhand_rec.qty > 0)

Sander

Re: INSERT STATEMENT

Justo Exposito — Thu, 03 Oct 2002 06:48:32 GMT

Hi Sander,

I think that the last sentence is fine if you want to use a cursor and there are other posibity that is to use the insert as select statment.

Regards,

Justo.

Re: INSERT STATEMENT

Graham Cameron_1 — Thu, 03 Oct 2002 06:59:02 GMT

Sander
No you cannot do "
SELECT ..
FROM cursor
WHERE (...)
"

You would have to put the WHERE clause into the cursor body.

Re: INSERT STATEMENT

Jean-Luc Oudart — Thu, 03 Oct 2002 10:46:22 GMT

Sanders,

1) You have to clean your table from your bad records

2) change your table definition and add a constraint on "qty"

cf. Oracle documentation on constraint
CREATE TABLE order_detail
(CONSTRAINT pk_od PRIMARY KEY (order_id, part_no),
order_id NUMBER
CONSTRAINT fk_oid REFERENCES scott.order (order_id),
part_no NUMBER
CONSTRAINT fk_pno REFERENCES scott.part (part_no),
quantity NUMBER
CONSTRAINT nn_qty NOT NULL
CONSTRAINT check_qty_low CHECK (quantity > 0),
cost NUMBER
CONSTRAINT check_cost CHECK (cost > 0) );

Jean-Luc

Re: INSERT STATEMENT

Raynald Boucher — Thu, 03 Oct 2002 11:57:19 GMT

The answer is yes (as shown below)

SQL> spool test_insert.log
SQL>
SQL> create table test_inserts as
2 select * from hrd_index_stats
3 where blocks between 0 and 100
4 ;

Table created.

SQL>
SQL> insert into test_inserts
2 (select * from hrd_index_stats
3 where blocks > 1000)
4 ;

1339 rows created.

SQL>
SQL> Drop table test_inserts;

Table dropped.

SQL> spool off