OraFAQ Forum: SQL & PL/SQL » Barbara-DECODE Function from your code

Home » SQL & PL/SQL » SQL & PL/SQL » Barbara-DECODE Function from your code

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Barbara-DECODE Function from your code [message #8237]

Tue, 05 August 2003 09:18

denni
Messages: 24
Registered: August 2002

Junior Member

Barbara

I inserted your code at the end of the client.sql
script.

How I need DECODE to work is:

If &client is ACGA then &batchno must begin with '6%'
(example ACGA, 60010168)

If &client is ACLJ then &batchno must begin with '2%'

when I ran the .sql with your code the 'no rows selected' was not being trapped by the "Client and
Batch Number Do Not Match" Prompt.

Attached is the entire code of the client.sql:
(I made some modifications)

select p.idnumber,p.usercode1,p.usercode2,b.batchno
from prospect p, batchdonation b
where p.usercode1=(upper('&client'))
and b.batchno=&batchno
and b.idnumber=p.idnumber;

STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGES 0 VERIFY OFF
SPOOL query.sql
SELECT DECODE('&client','ACGA','&batchno','6%','ACLJ','&batchno','2%','START appealcode',
'ACCEPT restart PROMPT "Client and Batch Number Do Not Match"'
||CHAR(10)
||'START client')
FROM DUAL
/
SPOOL OFF
START saved_settings
START query

thanks for knowledge and help.

Report message to a moderator

Re: Barbara-DECODE Function from your code [message #8244 is a reply to message #8237]

Tue, 05 August 2003 16:35

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Your modification does not use the decode function properly. The way that decode works is it evaluates the first parameter. Then it compares the value of the first parameter to the first value in the following sets of pairs. If the item to be evaluated is the same as the first value of the pair, then it uses the corresponding second value of that pair in the select statement. After all the pairs, there is a default value that is used when the item to be evaluated does not match any of the first values of any of the pairs. You can also think of the decode in terms of if then elsif then elsif then else endif. Here is the general syntax:

DECODE 
  (item_to_evaluate,          -- frist parameter
   if_this   , then_this,     -- first pair
   elsif_this, then_use_this, -- second pair
                              -- additional pairs
               else_use_this) -- default

Also, you can't use the % wildcard as you would in a like clause, you need to take a substring. For example SUBSTR(value,1,1) will give you the first character of value.

To do what you want, you will need to nest your decode functions. The example below evalutes the value of &client. If &client is ACGA, it evaluates the value of &batchno. If the first character of &batchno is 6, then it starts appealcode. If &client is ACLJ, if evalutes the value of &batchno. If the first character of &batcho is 2, then it starts appealcode. In all other cases, it prompts that the client and batch number don't match, waits for a keystroke, then re-starts client.

The reason that you were getting "No rows selected" is that you provided an erroneous query with only pairs and no default. Your query was saying if &client = 'ACGA' then use &batchno, else if &client = '6%' then use 'ACLJ', else if &client = &batchno then use 2%.

Here is the correct method:

STORE SET saved_settings REPLACE
SET ECHO OFF FEEDBACK OFF HEADING OFF PAGES 0 VERIFY OFF
SPOOL query.sql
SELECT DECODE ('&client', 
               'ACGA'   , DECODE (SUBSTR ('&batchno', 1, 1),
                                  '6'                      , 'START appealcode'),
               'ACLJ'   , DECODE (SUBSTR ('&batchno', 1, 1),
                                  '2'                      , 'START appealcode'),
                          'ACCEPT restart PROMPT ''Client and Batch Number do not Match'''
                          || CHR (10)
                          || 'START client') 
FROM   DUAL
/
SPOOL OFF
START saved_settings
START query

Report message to a moderator

Previous Topic:	Formatting output - a query
Next Topic:	Loading ASCII or CSV file to table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:11:44 CDT 2024