Create process to truncate table and reload data

Scriptcase 9 General
#1

I am trying to create a button or something to be able to truncate a table and reload the data.
i.e. refreshing…

I tried using a blank application…
though I dont like that option, would like to get feedbak to user that process completed.

I can run the process in a the sql workbench and it reloads fine.
But when I added it to a sc_exec_sql macro it fails…
Anyone have an idea why or another solution…
sql I am running is below:

sc_exec_sql("truncate table materialized_view_all_claims;
			 insert into materialized_view_all_claims
			(con_name,bat_invoice,clm_claimid,clm_type,tb_2,tb_1,bat_date,charges_total,medicare_allowable_total,
			 billable_total,payable_total)
			(select con_name,bat_invoice,clm_claimid,clm_type,tb_2,tb_1,DATE_FORMAT(`bat_date`,'%Y-%m-%d'),
			 sum(ifnull(clm_charges,0) + ifnull(cla_charges,0)),
  			 sum(ifnull(clm_allowable,0) + ifnull(cla_allowable,0)),
			 sum(ifnull(clm_billable,0) + ifnull(cld_billable,0) + ifnull(cla_billable,0)) as billable,
    		 sum(ifnull(clm_payable,0) + ifnull(cld_payable,0) + ifnull(cla_payable,0)) as payable
			from imsprism.v_prism_reporting_all_claims
			group by con_name,bat_invoice,clm_type,tb_2,tb_1,clm_claimid)");
#2

I would take a look at the generated source. I suspect that there is a " problem.

There is also the possibility that the database function that is called cannot handle multiple statements in one call (you have a truncate and an insert).

What error message are you getting, and what database?

Nick

#3

Why do you use a blanc application? You don’t have a database connection there. I would recommend using a control application. You need to declare your connection and I think that the code will work then.

#4

stupid me…

2 separate sc_exec_sql
Works… created in a control app…(thanks albert) Wish I could control the OK when it completes…
Looks kinda lame Huge screen with an ok button…

any solution?

#5

Use a sc_redir afterwards then the ok button is not displayed. I.e. sc_redir to the same application: fill a global variable with some text like ‘records processed’ and set this in a formfield at the onload. Then after hitting the button the records are processed and afterwards the same screen refreshes containing the message.