Home » SQL & PL/SQL » SQL & PL/SQL » Like with decode (merged)
Like with decode (merged) [message #376807] Thu, 18 December 2008 11:00 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
I want to implement the LIKE functionality in single DECODE function.


I have one table t1 :

create table t1 (val varchar2(20))


insert into t1 values ('ORACLE')
/
insert into t1 values ('ASHOK')
/



select * from t1
-----------------
val
------
ORACLE
ASHOK

i want to implement the like functionality in single decode function as

if val like 'OR%' then
  'F'
else 
 'S'
end if 


can anyone help me to sort this its very urgent

[Updated on: Fri, 19 December 2008 00:42] by Moderator

Report message to a moderator

Re: LIKE FUNCTIONALITY IN DECODE FUNCTION [message #376819 is a reply to message #376807] Thu, 18 December 2008 12:38 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Use CASE


and don't bore us with your lack of planning-capabilities.

Oh, and post SQL questions in the SQL-Forum. So much more chance of getting an answer..

[Updated on: Thu, 18 December 2008 12:39]

Report message to a moderator

Re: LIKE FUNCTIONALITY IN DECODE FUNCTION [message #376857 is a reply to message #376819] Thu, 18 December 2008 22:44 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

I hope Yo will find this link usefull:

http://oraqa.com/2006/04/12/how-to-use-like-operator-in-decode-function/

how the query is working ? using Decode [message #376862 is a reply to message #376807] Thu, 18 December 2008 22:52 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi All,

  1  select decode(val,(select val from  dual  where val like 'OR%') ,'F','S')
  2*  from t1
SQL> /

D
-
F
S




here my doubt is how the query

select val from  dual  where val like 'OR%'
will work ?..i am not getting it .. Confused
Re: how the query is working ? using Decode [message #376874 is a reply to message #376862] Fri, 19 December 2008 00:37 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
Hi Panyam,

You Query will run only when

select val from  dual  where val like 'OR%'


Return No Rows or 1 Rows Returned, Here what ever is the output it will always give 'S'. because val will returned with starting string with OR%

Thanks
Trivendra
Re: LIKE FUNCTIONALITY IN DECODE FUNCTION [message #376875 is a reply to message #376807] Fri, 19 December 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Once more read Forum guide and don't use urgent (we don't care and everyone thinks his problem is the most important and urgent one) and don't post your title in UPPER CASE.

Regards
Michel
Re: how the query is working ? using Decode [message #376876 is a reply to message #376862] Fri, 19 December 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have the same exercise than ORAGENASHOK?

Regards
Michel
Re: how the query is working ? using Decode [message #376925 is a reply to message #376876] Fri, 19 December 2008 03:21 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Michel,

I did nt have this excercise...I saw ORAGENASHOK post and wen i searching a solution .. I get this doubt.

1  select decode(val,(select val from  dual  where val like 'OR%') ,'F','S')
  2*  from t1
SQL> /

D
-
F
S


can u pls let me know how

select val from  dual  where val like 'OR%')


is working??...how can we select a clolumn val from dual ??,..really i consfused Confused
Re: how the query is working ? using Decode [message #376930 is a reply to message #376925] Fri, 19 December 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
did nt have this excercise...I saw ORAGENASHOK post and wen i searching a solution .. I get this doubt.

Why then did you start a new topic? Anyway I merged both.

Quote:
select val from dual where val like 'OR%')

What point don't you understand in this query?
It returns VAL if VAL is like 'OR%' and no row (that is NULL in scalar subquery) if not.
SQL> set null <null>
SQL> select val, (select val from  dual  where val like 'OR%') v from t1;
VAL                  V
-------------------- --------------------
ORACLE               ORACLE
ASHOK                <null>

2 rows selected.

But this is NOT the most efficient way to answer the question.

Regards
Michel
Re: how the query is working ? using Decode [message #376944 is a reply to message #376930] Fri, 19 December 2008 04:13 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Thnks for the reply.

How can we select a column of some table from Dual ?...

SQL> select val from  dual  where val like 'OR%';
select val from  dual  where val like 'OR%'
                             *
ERROR at line 1:
ORA-00904: "VAL": invalid identifier


still Confused

it will work fine in inner query how ?...
Re: how the query is working ? using Decode [message #376949 is a reply to message #376944] Fri, 19 December 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Because val is a column of t1. In this case, it is like a constant for the scalar query, val is replaced by its value in the current row.

Regards
Michel
Re: how the query is working ? using Decode [message #376961 is a reply to message #376949] Fri, 19 December 2008 04:37 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thnks a lot .... Razz Razz
Previous Topic: utl_file
Next Topic: GROUP BY clause problem (merged 3)
Goto Forum:
  


Current Time: Thu Dec 12 04:17:06 CST 2024