Home » SQL & PL/SQL » SQL & PL/SQL » Automate Custom SQL Menu
Automate Custom SQL Menu [message #338658] Tue, 05 August 2008 10:19 Go to next message
deay
Messages: 51
Registered: August 2005
Member
I've been asked to automate the entire process below rather than
have users prompted to enter a selection# that would begin with #1 thru #15 and generate output to a log file.

the batch numbers would also filter through the entire process.
any tips/help is greatly appreciated.


set serveroutput on size 1000000 
set echo off verify off trimspool on term on linesize 1000
cl screen
prompt ***************************************************
prompt * Select Batch Verification Processing Option:
prompt *  1.  Verify Fundcode
prompt *  2.  Verify Appealcode
prompt *  3.  Verify Dollars and Count
prompt *  4.  Update Batch Status
prompt *  5.  Verify Batch Flags
prompt *  6.  Verify NULL Acknowledgement
prompt *  7.  Verify NOT LIKE Appealcode
prompt *  8.  Verify NULL GiftType
prompt *  9.  Verify T_Date
prompt * 10.  Verify Year
prompt * 11.  Verify IntCode ATL NATL 
prompt * 12.  Verify IntCode 998 
prompt * 13.  Verify Salutations
prompt * 14.  Verify Premcode
prompt * 15.  Display Premcodes
prompt * 16.  Exit
prompt ***************************************************
accept process prompt ">"
SET TERM OFF
STORE SET saved_settings REPLACE 
SET TERM ON
SET FEEDBACK OFF HEADING OFF 
SPOOL query.sql
select decode(&process,1,'START checkFundcode',2,'START checkAppealcode',
3,'START checkDollars_Count',4,'START upd_BatchStatus',5,'START check_BatchFlags',
6,'START NullAcknowledgement',7,'START checkAppealcodeNL',
8,'START checkGifttype_Null',9,'START checkT_Date', 10, 'START checkYear_Null',
11,'START checkIntcode',12,'START checkIntcode2',13,'START check_Salutations',
14,'START checkPrem',15,'START checkPremList',16,'prompt','Please Enter Correct Selection')
from dual;
SPOOL OFF
START saved_settings
START query
Re: Automate Custom SQL Menu [message #338661 is a reply to message #338658] Tue, 05 August 2008 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Write and execute the follwing script:
SPOOL log
START checkFundcode
START checkAppealcode
START checkDollars_Count
START upd_BatchStatus
START check_BatchFlags
START NullAcknowledgement
START checkAppealcodeNL
START checkGifttype_Null
START checkT_Date
START checkYear_Null
START checkIntcode
START checkIntcode2
START check_Salutations
START checkPrem
START checkPremList
SPOOL off

Regards
Michel
icon14.gif  Re: Automate Custom SQL Menu [message #338665 is a reply to message #338661] Tue, 05 August 2008 10:34 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
thanks Michel

duh!..makes logical sense, just replace the "prompt" command with
"Start"....so simple.

thanks again!



Re: Automate Custom SQL Menu [message #338702 is a reply to message #338658] Tue, 05 August 2008 13:41 Go to previous messageGo to next message
deay
Messages: 51
Registered: August 2005
Member
I'm running into a problem with ACCEPT on a second variable
where the script(s) call for batch numbers between batch1 and batch2. I keep getting an invalid identifier on batch2 on test runs.

here's the call script:
set serveroutput on size 1000000 
set echo off verify off trimspool on term on linesize 1000
cl screen
SET FEEDBACK OFF HEADING OFF 
SPOOL BatchVerify.log
SET ECHO ON
ACCEPT batch1 NUMBER format '00000000' -
Prompt 'Enter Batch1:  '
ACCEPT batch2 NUMBER format '00000000' -
Prompt 'Enter Batch2:  '
START acheckFundcode
SPOOL OFF


output:(I'm using @@ to call the other .sql scripts)
SQL> ACCEPT batch1 NUMBER format '00000000' -
> Prompt 'Enter Batch1:  '
Enter Batch1:  40806762
SQL> ACCEPT batch2 NUMBER format '00000000' -
> Prompt 'Enter Batch2:  '
Enter Batch2:  40806763
SQL> START acheckFundcode
SQL> SET TERM ON
SQL> select sum(b.payamount),b.fundcode
  2  from batchpay b
  3  where b.batchno between batch1 and batch2
  4  group by b.fundcode;
where b.batchno between batch1 and batch2
                                   *
ERROR at line 3:
ORA-00904: "BATCH2": invalid identifier


SQL> @@acheckAppealcode.sql
SQL> SET TERM ON
SQL> select bd.idnumber,b.batchno,b.appealcode
  2  from batchdetail bd,batchpay b
  3  where b.appealcode like 'D____'
  4  and b.batchno between batch1 and batch2
  5  and bd.transnum=b.bd_id;
and b.batchno between batch1 and batch2
                                 *
ERROR at line 4:
ORA-00904: "BATCH2": invalid identifier


SQL> @@acheckAppealcodeNL.sql
SQL> SET TERM ON
SQL> select bd.idnumber,b.batchno,b.appealcode
  2  from batchdetail bd,batchpay b
  3  where b.appealcode NOT LIKE (upper('&aplcode'))
  4  and b.batchno between batch1 and batch2
  5  and bd.transnum=b.bd_id;
Enter value for aplcode: 
Re: Automate Custom SQL Menu [message #338707 is a reply to message #338702] Tue, 05 August 2008 14:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
between &batch1 and &batch2

Regards
Michel
Previous Topic: column to rows
Next Topic: Analytic Query
Goto Forum:
  


Current Time: Sat Dec 03 05:50:03 CST 2016

Total time taken to generate the page: 0.07971 seconds