View previous topic :: View next topic
|
Author |
Message |
tsantosh
New User
Joined: 03 Aug 2006 Posts: 8
|
|
|
|
Can I declare a cursor with hold for the following query
SELECT DISTINCT S.STOV_ID, S.SPCL_TKT_CD
FROM QUAL.STOV P, QUAL.STOV_SPCL S
WHERE P.STOV_ID = S.STOV_ID
AND S.CANC_DT = '99/99/9999'
AND S.ETA_IND = 'Y'
AND S.PRI_CD = 'P'
AND P.CANC_DT = '99/99/9999'
AND S.STOV_ID NOT IN
( SELECT SPC.STOV_ID
FROM QUAL.STOV_PDRF_CONTR SPC
WHERE SPC.CANC_DT = '99/99/9999'
AND SPC.CONTR_TYP_CD = 'H'
AND SPC.TKT_NBR BETWEEN 01000 AND 60000
AND S.STOV_ID = SPC.STOV_ID)
I need to update data only in table STOV_SPCL .. |
|
Back to top |
|
|
Johnson_Jiang
New User
Joined: 05 Apr 2007 Posts: 11 Location: China,ShenZhen
|
|
|
|
Quote: |
Can I declare a cursor with hold for the following query
Code: |
SELECT DISTINCT S.STOV_ID, S.SPCL_TKT_CD
FROM QUAL.STOV P, QUAL.STOV_SPCL S
WHERE P.STOV_ID = S.STOV_ID
AND S.CANC_DT = '99/99/9999'
AND S.ETA_IND = 'Y'
AND S.PRI_CD = 'P'
AND P.CANC_DT = '99/99/9999'
AND S.STOV_ID NOT IN
( SELECT SPC.STOV_ID
FROM QUAL.STOV_PDRF_CONTR SPC
WHERE SPC.CANC_DT = '99/99/9999'
AND SPC.CONTR_TYP_CD = 'H'
AND SPC.TKT_NBR BETWEEN 01000 AND 60000
AND S.STOV_ID = SPC.STOV_ID) |
I need to update data only in table STOV_SPCL ..
|
I think it doesn't work! In the sub SQL, you should also involve the table QUAL.STOV_SPCL, the join condition is incorrect. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
Hello,
I didnt have a chance to look at the syntax of the sql query. The bottom line is, if you have JOIN in the cursor then it happens to be READ ONLY CURSOR. There are some more restrictions like, if a query in cursor contains GROUP BY or ORDER BY or column/aggregate fucntions it happens to be READ ONLY. please refer to manual for more information. |
|
Back to top |
|
|
|