| View previous topic :: View next topic |
| Author |
Message |
pkarthik@email.com
Beginner
Joined: 29 Mar 2005
Posts: 34
Topics: 18
Location: Canada
|
 Posted: Mon Nov 05, 2007 4:39 am Post subject: Problem in Update Query |
 |
|
Hi,
I have to update selective rows in a table after performing an innerjoin.I am getting an error saying syntax error.Please help me in this regard.I am new to DB2.
Showing error in below set statement
| Code: | SET A.ENT_QTY_CURRENT = 0
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Menu Utilities Compilers Help
sssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
BROWSE XKPN6.EXPLAIN.TEMP Line 00000022 Col 001 080
Command ===> Scroll ===> CSR
EXPLAIN PLAN SET QUERYNO=111556 FOR
UPDATE WW.BS_ENTITLEMENTS A
SET A.ENT_QTY_CURRENT = 0
WHERE A.DEAL_NO = ?
AND A.COST_VENDOR = ?
AND A.PROM_NO = ?
AND A.BONUS_ITEM = ?
AND A.LOCATION_NO BETWEEN '0000' AND '9999' ;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: =
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 166 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'000000A6' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 6
DSNE621I NUMBER OF INPUT RECORDS READ IS 13
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 45
******************************** Bottom of Data ******************************** |
_________________
For any type of complex problems there will be multiple easiest solutions |
|
| Back to top |
|
 |
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Nov 05, 2007 4:56 am Post subject: |
|
|
pkarthik@email.com,
How do you know that the error is in | Code: | | SET A.ENT_QTY_CURRENT = 0 |
I don't see any fault in the syntax.
Anyway if you have QMF, copy and paste the sql query into QMF and press F2. If there is an error , QMF points it out with line and position of the error. See if you can find any error. |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Nov 05, 2007 5:00 am Post subject: |
|
|
Try the below sql.
| Code: |
SET ENT_QTY_CURRENT = 0
WHERE DEAL_NO = ?
AND COST_VENDOR = ?
AND PROM_NO = ?
AND BONUS_ITEM = ?
AND LOCATION_NO BETWEEN '0000' AND '9999' ;
|
|
|
| Back to top |
|
|
pkarthik@email.com
Beginner
Joined: 29 Mar 2005
Posts: 34
Topics: 18
Location: Canada
|
| Posted: Mon Nov 05, 2007 5:29 am Post subject: |
|
|
Hi,
Please find my compile listig.
My code is as below.
| Code: |
1154 C4000-UPDATE-TABLES SECTION.
1155 EXEC SQL
1156 UPDATE BS_ENTITLEMENTS A
1157 SET A.ENT_QTY_CURRENT = 0
1158 ,A.ENT_VAL_CURRENT = 0
1159 WHERE A.DEAL_NO = :DCLBS-ENTITLEMENTS.DEAL-NO
1160 AND A.COST_VENDOR = :DCLBS-ENTITLEMENTS.COST-VENDOR
QL PRECOMPILER VERSION 7 REL. 1.0
1161 AND A.PROM_NO = :DCLBS-ENTITLEMENTS.PROM-NO
1162 AND A.BONUS_ITEM = :DCLBS-ENTITLEMENTS.BONUS-ITEM
1163 AND A.LOCATION_NO BETWEEN '0000' AND '9999'
1164 AND A.EFFECTIVE_DATE = ( SELECT MAX(B.EFFECTIVE_DATE)
1165 FROM BS_ENTITLEMENTS B
1166 WHERE B.DEAL_NO = :DCLBS-ENTITLEMENTS.DEAL-NO
1167 AND B.COST_VENDOR = :DCLBS-ENTITLEMENTS.COST-VENDOR
1168 AND B.PROM_NO = :DCLBS-ENTITLEMENTS.PROM-NO
1169 AND B.BONUS_ITEM = :DCLBS-ENTITLEMENTS.BONUS-ITEM
1170 AND B.LOCATION_NO = A.LOCATION_NO )
Error message:
PRECOMPILER MESSAGES
E DSNHPARS LINE 1157 COL 22 "=" REQUIRED
E DSNHPARS LINE 1157 COL 22 ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: =
PRECOMPILER STATISTICS
TATISTICS |
_________________
For any type of complex problems there will be multiple easiest solutions |
|
| Back to top |
|
|
CICS Guy
Intermediate
Joined: 30 Apr 2007
Posts: 292
Topics: 3
|
| Posted: Mon Nov 05, 2007 5:40 am Post subject: |
|
|
What is this?
1158 ,A.ENT_VAL_CURRENT = 0 |
|
| Back to top |
|
|
vkphani
Intermediate
Joined: 05 Sep 2003
Posts: 483
Topics: 48
|
| Posted: Mon Nov 05, 2007 6:00 am Post subject: |
|
|
| CICS Guy wrote: | What is this?
1158 ,A.ENT_VAL_CURRENT = 0 |
I think he is using "," as separator to update A.ENT_QTY_CURRENT and A.ENT_VAL_CURRENT to ZERO. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Mon Nov 05, 2007 7:00 am Post subject: |
|
|
pkarthik,
you are not doing an inner join for your update query.
you may think your are doing an inner join in your subselect. but i think the sub-select is coded wrong.
get rid of all the qualification in the main query and include the second table in the from clause of your sub-select.
you have been a member of this site long enough to start using bbcode to make your posts easier to read, please do so.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Mon Nov 05, 2007 9:11 am Post subject: |
|
|
Don't qualify the columns specified in the SET clause. I think everything else is fine.
| Code: |
SET ENT_QTY_CURRENT = 0
,ENT_VAL_CURRENT = 0
|
|
|
| Back to top |
|
|
pkarthik@email.com
Beginner
Joined: 29 Mar 2005
Posts: 34
Topics: 18
Location: Canada
|
| Posted: Tue Nov 06, 2007 12:27 am Post subject: |
|
|
Thanks Dick and Sharon.
| Code: | SET ENT_QTY_CURRENT = 0
,ENT_VAL_CURRENT = 0 |
worked out well.
_________________
For any type of complex problems there will be multiple easiest solutions |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
