Home » Developer & Programmer » Forms » nested decode
nested decode [message #255525] Wed, 01 August 2007 02:11 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
it is working well for ist 2 cases but gives unexpected result for last 4 why
------------------------------
select decode( substr('&&a' , 1,1) ,'.','no' , 'yes' ,
substr('&a' , 1,1) ,'@','no' , 'yes' ,
substr('&a' , -1,1) ,'.','no' , 'yes' ,
substr('&a' , -1,1) ,'@','no' , 'yes' ,
instr( '&a' , '@' ) , 0 , 'no' , 'yes6' ,
instr ( '&a' , '.' ) ,0 , 'no' , 'yes7'
) as "valid"
from dual
----------------------
Re: nested decode [message #255550 is a reply to message #255525] Wed, 01 August 2007 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is because you don't understand how DECODE works. All the testing you are trying to do can be done using DECODE, but it would be a mess. I'd suggest you to use CASE instead (both can be found in the documentation; check the link in the Sticky topic in the Newbies Forum).

However, if you check existence of '@' in a string using the INSTR function, why are you testing it using the SUBSTR function as well? I'd say that INSTR should be enough.

Here's an example; see if it can help.
WITH t AS 
  (SELECT 'this@_is_invalid_case' COL FROM dual
   UNION
   SELECT 'this is valid case' COL FROM dual
  )
   SELECT COL,
           CASE
             WHEN SUBSTR(COL,  1, 1) IN ('.', '@') THEN 'no'
             WHEN SUBSTR(COL, -1, 1) IN ('.', '@') THEN 'no'
             ELSE 'yes'
           END case_substr,
           CASE
             WHEN INSTR(COL, '@') > 0 THEN 'no'
             WHEN INSTR(COL, '.') > 0 THEN 'no'
             ELSE 'yes'
           END case_instr
   FROM t;

COL                   CASE_SUBST CASE_INSTR
--------------------- ---------- ----------
this is valid case    yes        yes
this@_is_invalid_case yes        no



thanx [message #255564 is a reply to message #255550] Wed, 01 August 2007 04:05 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
thanx for your help man
actually i was trying to do this

=============================================
select decode(
substr('&&a' , 1,1) ,'.','no' ,
decode(substr('&a' , 1,1) ,'@','no' ,
decode(substr('&a' , -1,1),'.','no',
decode(substr('&a' , -1,1) ,'@','no' ,
decode(instr( '&a' , '@' ) , 0 , 'no' ,
decode(instr( '&a' , '.' ) ,0 , 'no' , 'yes' ))))))
as "valid"
from dual
========================
its working alright
regards [message #255568 is a reply to message #255564] Wed, 01 August 2007 04:09 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
actually my requirement was to do it in decode
but no doubt your approach is much better and far simpler than mine.
Previous Topic: Object Types/XMLType tables and Forms 10gr2
Next Topic: Windows username And Password?
Goto Forum:
  


Current Time: Wed Dec 07 16:44:30 CST 2016

Total time taken to generate the page: 0.13449 seconds