Announcement

Collapse
No announcement yet.

Create process to truncate table and reload data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create process to truncate table and reload data

    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:

    Code:
    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

    Comment


    • #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.
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #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?

        Comment


        • #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.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment

          Working...
          X