Home » SQL & PL/SQL » SQL & PL/SQL » DECODE help!
DECODE help! [message #8239] Tue, 05 August 2003 13:55 Go to next message
denni
Messages: 24
Registered: August 2002
Junior Member
I am trying to design a SQLPlus User Input Menu
for a batch verification process.
below is the code for processing the Menu Selection
screen then DECODE is used to call the script that
is assigned to the Menu number selected by the user.

I can't get DECODE to run the script associated with
the number selection.

thanks for any help.
======================================================

set serveroutput on size 1000000
set echo off;
set verify off;
set trimspool on;
set linesize 1000
prompt ***************************************************
prompt * Select batch verification processing option:
prompt * 1. Verify Client
prompt * 2. Verify Appealcode
prompt * 3. Verify Dollars and Count
prompt * 4. Update Batch Status
prompt * 5. Verify Batch Flags
prompt ***************************************************
accept process prompt ">"
select decode(&process,1,'START batchclnt',2,'START batchapl',null)
from dual;

=====================================================================

SQL> @batchverify
***************************************************
* Select batch verification processing option:
* 1. Verify Client
* 2. Verify Appealcode
* 3. Verify Dollars and Count
* 4. Update Batch Status
* 5. Verify Batch Flags
***************************************************
>1

DECODE(1,1,'STA
---------------
START batchclnt
Re: DECODE help! [message #8245 is a reply to message #8239] Tue, 05 August 2003 16:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You haven't spooled the result of the select with the decode to a file or run that file. Also, you need to make sure that you set feedback off and heading off before you spool. You will probably want to save and replace your previous set commands also. You can put your set commands together on one line and you don't need a semicolon after them. Please see the corrected code below:

set serveroutput on size 1000000
set echo off verify off trimspool on linesize 1000
prompt ***************************************************
prompt * Select batch verification processing option:
prompt * 1. Verify Client
prompt * 2. Verify Appealcode
prompt * 3. Verify Dollars and Count
prompt * 4. Update Batch Status
prompt * 5. Verify Batch Flags
prompt ***************************************************
accept process prompt ">"
STORE SET saved_settings REPLACE
SET FEEDBACK OFF HEADING OFF
SPOOL query.sql
select decode(&process,1,'START batchclnt',2,'START batchapl',null)
from dual;
SPOOL OFF
START saved_settings
START query
Barbara THANKS!!! Re: DECODE help! [message #8255 is a reply to message #8245] Wed, 06 August 2003 06:44 Go to previous message
denni
Messages: 24
Registered: August 2002
Junior Member
it works perfectly!

thanks for your help and instructions.
I learned a great deal about using 'DECODE'
from your examples and explanations.

thanks for the other issue that you also helped
to 'decode' for me(no pun intended!).
Previous Topic: Select distinct
Next Topic: UTL_FILE - Invalid Path
Goto Forum:
  


Current Time: Fri Mar 29 08:07:33 CDT 2024