Menu
I want to create a template (via SQL) that can be copied straight into MS Excel.
Generally this went pretty well, however, I've run into a problem with the field that is supposed to store an Excel formula. This field is supposed to be used to create INSERT statements in the Excel file, based on the user input in one of the columns of the Excel sheet.
Here's my code:
SELECT
-- some other fields here
[...],
(SELECT '="INSERT INTO MyTable VALUES(" &C' || CREATE_INSERTS_TEMP_SEQ.nextval || '&",''THIS COLUMN IS TO BE FILLED BY USER IN EXCEL'',''"&D' || CREATE_INSERTS_TEMP_SEQ.currval || '&"'',14,sysdate);"' FROM DUAL) As SQL_Statement
FROM myTable;
I am getting the error message ORA-02287: sequence number not allowed here.
Why would it not be allowed in my subquery? Any idea how to fix this?
I must have access to these values in order to create a functioning Excel formula, as these numbers are the reference to the respective Excel rows...
816
You can use NEXTVAL (or CURRVAL) in the SET clause of the UPDATE statement, as the following example shows: UPDATE tab1 SET col2 = seq_2. NEXTVAL WHERE col1 = 1; In the previous example, the incremented value of the seq_2 sequence, which is 2 , replaces the value in col2 where col1 is equal to 1 .
ORDER BY command cannot be used in a Subquery.
The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value.
Order by clause does not works inside a Sub-Query.No use of giving ORDER BY clause inside the sub query. Subquery gives values to the outer query and outer query only orders the value based on the order by clause.
The documentation includes restrictions, including:
CURRVAL and NEXTVAL cannot be used in these places:
- A subquery
- ...
But you don't need a subquery here:
SELECT
-- some other fields here
[...],
'="INSERT INTO MyTable VALUES(" &C' || CREATE_INSERTS_TEMP_SEQ.nextval
|| '&",''THIS COLUMN IS TO BE FILLED BY USER IN EXCEL'',''"&D'
|| CREATE_INSERTS_TEMP_SEQ.currval || '&"'',14,sysdate);"' As SQL_Statement
FROM myTable;
This form doesn't generate the error.
I suspect once reason it isn't allowed in a subquery is because it isn't clear, or deterministic, how many times the subquery would be executed. If it had worked in this scenario then you might have got the same insert statement for every row in your table, which presumably isn't your intention.
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
