UPDATE A TABLE INCASE OF JOINS



IBM's flagship relational database management system

UPDATE A TABLE INCASE OF JOINS

Postby djprakash1ml » Wed Jun 29, 2011 3:09 pm

Could you please help me with the following errors got in the following 2 cases?

Case1: BIND error

While using the following SQL statement in a cursor and trying to BIND the COBOL code


SQL Statement:-
EXEC SQL
DECLARE CURSORNAME CURSOR WITH HOLD FOR
SELECT T1.FIELDNAME1
,T1.FIELDNAME2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.FIELDNAME1=T2.FIELDNAME1
AND T2.FIELDNAME2 = 'A'
FOR UPDATE OF FIELDNAME2

END-EXEC

Error:-
SQLCODE=-203 for FIELDNAME2
RDS CODE=-500


Case2:- Compile error

While using the same SQL statement in a cursor and trying to COMPILE the COBOL code with a qualifier.

SQL Statement:-
EXEC SQL
DECLARE CURSORNAME CURSOR WITH HOLD FOR
SELECT T1.FIELDNAME1
,T1.FIELDNAME2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.FIELDNAME1=T2.FIELDNAME1
AND T2.FIELDNAME2 = 'A'
FOR UPDATE OF T1.FIELDNAME2

END-EXEC



Error:-
'ILLEGAL SYMBOL ".".'

ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: QUERYNO SKIP OPTIMIZE

UPDATE OF T1.
djprakash1ml
 
Posts: 17
Joined: Mon Jun 28, 2010 6:33 pm
Has thanked: 0 time
Been thanked: 0 time

BIND/COMPILE ERROR IN SELECT QUERY IN CURSOR

Postby djprakash1ml » Thu Jun 30, 2011 9:00 am

*******Please note the change in subject.


djprakash1ml wrote:Could you please help me with the following errors got in the following 2 cases?

Case1: BIND error

While using the following SQL statement in a cursor and trying to BIND the COBOL code


SQL Statement:-
EXEC SQL
DECLARE CURSORNAME CURSOR WITH HOLD FOR
SELECT T1.FIELDNAME1
,T1.FIELDNAME2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.FIELDNAME1=T2.FIELDNAME1
AND T2.FIELDNAME2 = 'A'
FOR UPDATE OF FIELDNAME2

END-EXEC

Error:-
SQLCODE=-203 for FIELDNAME2
RDS CODE=-500


Case2:- Compile error

While using the same SQL statement in a cursor and trying to COMPILE the COBOL code with a qualifier.

SQL Statement:-
EXEC SQL
DECLARE CURSORNAME CURSOR WITH HOLD FOR
SELECT T1.FIELDNAME1
,T1.FIELDNAME2
FROM TABLE1 T1, TABLE2 T2
WHERE T1.FIELDNAME1=T2.FIELDNAME1
AND T2.FIELDNAME2 = 'A'
FOR UPDATE OF T1.FIELDNAME2

END-EXEC



Error:-
'ILLEGAL SYMBOL ".".'

ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: QUERYNO SKIP OPTIMIZE

UPDATE OF T1.
djprakash1ml
 
Posts: 17
Joined: Mon Jun 28, 2010 6:33 pm
Has thanked: 0 time
Been thanked: 0 time

Re: UPDATE A TABLE INCASE OF JOINS

Postby GuyC » Thu Jun 30, 2011 12:56 pm

Read-only result table: Some result tables cannot be updated—for example, the result of joining two or more tables. The defining characteristics of a read-only result tables are described in greater detail in the discussion of DECLARE CURSOR in DB2 SQL.


rewrite yyour query as :
DECLARE CURSORNAME CURSOR WITH HOLD FOR
SELECT T1.FIELDNAME1 ,T1.FIELDNAME2
FROM TABLE1 T1
where exists (select * from TABLE2 T2
WHERE T1.FIELDNAME1=T2.FIELDNAME1
AND T2.FIELDNAME2 = 'A' )
FOR UPDATE OF FIELDNAME2
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post