Hi,
I need to update the database based on the list of Product details.
My Table is defined as below
Filed Name : PRD_ID_DESC
Length : 50
The field comprises of Product ID (LENGTH - 6 CHAR) , some spaces, and Product desc.
I have the list of Product ID (LENGTH - 6 CHAR) and I need to delete from table.
Please find my DELETE Query:
DELETE FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' , '%409038%' ) ;
COMMIT ;
BUT my SQL query is failing with -104 error.
Kindly advise me in the right direction.
Many Thanks in advance !!!!
Query using DELETE
Moderators: Kalicharan, Moderator Group
SQLCODE -104, LIKE, IN keyword
Instead of LIKE keyword use IN keyword.
Thanks Krishna !!!!
I have changed LIKE to IN and the database have not been updated.
Instead I tried to SELECT the database with IN and the query resultant is '0' rows, but the table has records.
If I perform the same with single PRD_ID_DESC and 2 rows been fetched from my resultant query.
Please assist me in this !!!!
I have changed LIKE to IN and the database have not been updated.
Instead I tried to SELECT the database with IN and the query resultant is '0' rows, but the table has records.
If I perform the same with single PRD_ID_DESC and 2 rows been fetched from my resultant query.
Please assist me in this !!!!
Krishna please find the SQL query.
I tried to SELECT the database to check whether any qualified rows are available in the DB, and can see few rows fetched. When I changed SELECT to DELETE the SQL is throwing +100 SQL error.
Delete Query :
SQL Code = +100
Select Query :
--------------
SQL Code = 2 Rows.
Kindly help me in this !!!!
I tried to SELECT the database to check whether any qualified rows are available in the DB, and can see few rows fetched. When I changed SELECT to DELETE the SQL is throwing +100 SQL error.
Delete Query :
Code: Select all
DELETE FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' , '%409038%' ) ;
Select Query :
--------------
Code: Select all
SELECT * FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' ) ;
Kindly help me in this !!!!
Following code might work.
when you are using IN keyword, symbol % may not work.
Code: Select all
DELETE FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC IN
('481032' , '409038' ) ;
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
you may have more luck with:
and the sql could easily be built with a REXX macro, when you have many, many PRD_ID_DESC
and Krishna is correct, the substitution symbols are not valid for IN-list contents.
Code: Select all
DELETE FROM table
WHERE POSSTR('481032',PRD_ID_DESC) > 0
OR POSSTR('409038',PRD_ID_DESC) > 0
and Krishna is correct, the substitution symbols are not valid for IN-list contents.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page