|
View previous topic :: View next topic
|
| Author |
Message |
pjnithin
Active User
Joined: 22 Dec 2005
Posts: 116
|
|
|
|
I have an insert query like this:
INSERT INTO tbl1
(EMAILID,
EMPNO)
VALUES ( 'ID1',
(SELECT SUBSTR(MAX(EMPNO),1,1) ||
SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),2,11)
AS INTEGER) + 1),3,8)
FROM tbl1
WHERE EMPNO LIKE 'C%'))
Basically I need to get the empno dynamically created while doing the insert. But this query fails with an sqlcode -4700. Please let me know whether there is any other method of doing this. |
|
| Back to top |
|
 |
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
This might help you...
| Quote: |
-4700 ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
Explanation: Functions that this release of DB2 introduces cannot be used before new function mode has been enabled. An attempt was made to execute one of these functions. In addition, support for extending the length of a VARCHAR (supported in V7) is restricted in V8 until new function mode has been enabled. To use the new functions that have been introduced in this release of DB2, new-function mode must first be enabled. This error code will be given after an attempt to use a new function before new-function mode has been enabled. Additionally, the following statements are restricted in DB2 for z/OS Version 8 until new-function mode is enabled:
1. Altering a table to increase the length of a VARCHAR column
2. Altering a catalog table to DATA CAPTURE CHANGES
System action: The statement cannot be executed.
Programmer response: Either delay running your program until new-function mode has been enabled, or restrict your program to functions that are allowed before new-function mode is enabled.
|
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
pjnithin,
I have been experimenting on vsn 9.??? and my response is SQLCODE -290, SQLSTATE=55039
Table space access is not allowed.
A process attempted to access a table space which is in an
invalid state for which the intended access is not allowed.
If the table space is in a quiesced state, .... quiesced is not the problem here
If the table space is in any other state, only the process
which is performing the action specified is allowed access to
the table space.
I interpret the above to mean that during an INSERT,
I can not derive a value based on a subselect from the same table.
Every system,
that I have ever worked on,
derived the 'new number/code' based on a control table for new numbers/codes.
Perhaps reason there was always another table involved,
was due to the sqlstate that I received for the INSERT with the subselect. |
|
| Back to top |
|
|
pjnithin
Active User
Joined: 22 Dec 2005
Posts: 116
|
|
|
|
| When I try to do an update using a subquery it does; but only insert fails. None of the explanations are very clear to me; can someone enlighten me on the real reason. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi pjnithin
Your query will work in INSERT also.
What is the datatype of EMPNO? Is it CHAR or INT ? Since you are using a SUBSTR it must be a CHAR.
While specifying the arguments in the SUBSTR, you should take care for the length of the EMPNO column.
I have an insert query for a table. Here EMPNO is CHAR(6).
| Code: |
INSERT INTO EMPLOYEE1(EMPNO) VALUES(
SELECT SUBSTR(MAX(EMPNO),1,1) CONCAT
SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),4,3) AS INT) + 1),6,5)
FROM EMPLOYEE1); |
It is working fine.
Try it out.
Regards
Raghu[/quote] |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Raghu,
does your query work when you have the where clause
WHERE EMPNO LIKE 'C%
in the subselect? |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Yes dbzTHEdinosauer
In my case EMPNO starts with '0' (zero).
I used the following query
| Code: |
---------+---------+---------+---------+---------+---------+-----
INSERT INTO EMPLOYEE1(EMPNO) VALUES(
SELECT SUBSTR(MAX(EMPNO),1,1) CONCAT
SUBSTR(DIGITS(CAST(SUBSTR(MAX(EMPNO),4,3) AS INT) + 1),6,5)
FROM EMPLOYEE1 WHERE EMPNO LIKE '0%');
---------+---------+---------+---------+---------+---------+-----
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 |
Regards
Raghu |
|
| Back to top |
|
|
|
|
