All Forums Tools
mehaboob 10 posts Joined 12/09
18 Jan 2010
how to insert no of rows effected by a sql stmt in bteq script into a table with in the same script

hican any one tell me how to insert the number of rows effected by a sql statement in a bteq script in to a table..can i activitycount for this....i want to insert the number of records inserted with in the same script...thanksmehaboob

ravi.videla 21 posts Joined 09/09
02 Feb 2010

Did some research on this. Could not find out any way to do this in BTEQ. activitycount indicates the actual number of rows affected by the request. But we can not use that in any SQL. It can just be used in "IF" statement to verify its value. May be someone can suggest if we have any other alternative to solve this problem.

ravi.videla 21 posts Joined 09/09
02 Feb 2010

Consulted with one TD expert i know. He also confirmed it can not be done using BTEQ.However it can done using a stored procedure. Below is sample code if its useful to you.create multiset table test (sno int) ;insert test(1) ;insert test(1) ;insert test(2) ;insert test(31) ;insert test(14 );replace procedure testproc (IN inval int, out msg varchar(100))p1:begin update test set sno = 100 where sno = :InVal; if activity_Count > 0 then set msg = 'number of records affected' || activity_count; else set msg = 'no records affected'; end if; end; BTEQ -- Enter your DBC/SQL request or BTEQ command: call testproc (31, msg);call testproc (31, msg); *** Procedure has been executed. *** Total elapsed time was 1 second.msg---------------------------------------------------------------------------number of records affected 1.

You must sign in to leave a comment.