on 01-24-2011 12:42 PM
Hello, experts!
We need to delete N first records from a DBF file with the following columns CODE, NAME and some other attributes.
I use Receiver JDBC Adapter with additional DBF driver.
I tried to use action SQL_DML with the following access value:
DELETE FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).
But it deletes only one record instead of deleting ten.
Could you help me to find out why is that so and how to delete first N records?
BR,
Vika
> DELETE FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).
> But it deletes only one record instead of deleting ten.
Have you tested this command directly on database with a database tool?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
A dbf "file" is in fact a dbase database.
http://en.wikipedia.org/wiki/DBase
Every databse has its own syntax, of course based on SQL.
Your SQL statement looks correct to me, and i am sure it would work for MS SQL,
but a should confirm with a database tool (I suppose there should be any) to see, if the issue is the SQl command itself, or the JDBC adapter.
When you can watch entries in this dbf file with MS Access, is it also possible to execute SQL statements?
I tried and figured out, that it is possible to perform the request in MS Access.
But MS Access Query Constructor added * after DELETE, so in MS Access my query looks like DELETE * FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).
I tried this query in PI, but got error in JDBC Communication Channel:
Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table_name' (structure 'STATEMENT'): java.sql.SQLException: [StelsDBF JDBC driver] Can't parse SQL query: [SQL Engine API] Incorrect syntax near the word 'DELETE' (position: 8). See realized SQL specification in the driver documentation. SQL query was: 'DELETE * FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name)'
So, it looks like my query is correct.
What can it be then?
BR,
Vika
User | Count |
---|---|
82 | |
10 | |
10 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.