sql code to delete records

April 18, 2006, 11:52 AM

Kerry

Hi Jim,

Has this issue been resolved? Have you got a chance to try Kamesh's suggestion?

I did some search on online documents and came across an idea, in addtion to authorization/privilage as newtofocus suggested. Just wondering, could there be problems with the adapter that connects the Oracle database and the WebFOCUS server? If you run the SQL query from the database side (not within a fex), will you be able to delete the records?

As always, you can contact Information Builders' Customer Support Services and open a case for assistance at any time. The phone number is 1-800-736-6130, or you may access the online system InfoResponse.

Hope this helps.

Cheers,

Kerry

Kerry Zhan
Focal Point Moderator
Information Builders, Inc.

July 06, 2007, 05:31 PM

dballest

I can't seem to delete all the rows in the Oracle table.

-*Call the Oracle Stored Procedure
SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER efactory_dev_dev045
-RUN

SQL SQLORA
delete AdjustmentsRpt_Answer;
END

-EXIT

I tried the suggestion below and it still did not delete them.

SQL SQLORA
delete from TableName
where field1=1;
END

Since my 1st field is a date, I tried sysdate or 1. Did not work. I even tried "where 1=1"

Thanks,
Dan

Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7

Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo

Scott

July 09, 2007, 12:05 PM

Jason K.

a 0 delete means there is no record found to delete.

You would recieve a permissions error if that was the issue. Look in your ODBC setup and see what user IBI's software is using and you can talk to your DBA about what's happening.

If you don't know how to log on to your oracle database using a tool like sqlplus or iSqlplus, then you'll probably want to get with your DBA and see if he can run your Delete statement from a machine that has the Oracle Client software installed (that's if you can't shell into a Linux server or something, depending on your server setup).

Whatever is returned in a sqlPlus session is your actual error, but more than likely it's a simple "no row found for delete". Also, to delete all records from a table using SQL, the answer is simpler than you think... try a "delete from schema.table;" and remember, this is a DML action so you will have to commit; the transaction when you finish if your database does not have autocommit turned on. (it will not have autocommit more than likely). The commit; would have to be done in the same ODBC connection in order for the changes to be preserved. Also, IIRC you may be able to set the commit level of each transaction in your ODBC setup, but I have not tried this method and it is a bad practice in my opinion.

Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.

July 09, 2007, 12:08 PM

Jason K.

another note.... you'll probably want to start specifying the schema in which your table exists.

If your DBA set up a synonym for a schema that has a WORK_ORDER_DETAIL table, it may not be the same as the one you're querying to find WHERE STATUS = 'C' records.

Also, try a TRIM(STATUS) = 'C'

Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.

July 09, 2007, 01:30 PM

dballest

Thanks for all your suggestions. There's nothing wrong with the access priv. The COMMIT did it for us.

Dan