db - 10g
I have a data that I am inserting from one table to another. One of the columns that I am inserting has null or no value for some records. For these records I want to assign an arbitrary number to the record, and would like to use a conditional statement with a sequence to assign the arbitrary number.
I have opened a cursor and I am attempting to use an insert statement in the following way;
INSERT INTO psp_trees
( plot_id
, tree_name
, species_code)
VALUES
( get_plot_id
, (CASE WHEN c1.treenum IS NULL THEN (tree_arbitrary_name_seq.nextval) ELSE c1.treenum END)
, c1.species
) RETURNING tree_id INTO get_tree_id;
This throws the following error, I think because it sees the text after the THEN statement as a string;
Error report:
ORA-06550: line 76, column 89:
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
ORA-06550: line 70, column 13:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Is it possible to do something like this?
Ben