OraFAQ Forum: SQL & PL/SQL » Like with decode (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Like with decode (merged)

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

Like with decode (merged) [message #376807]

Thu, 18 December 2008 11:00

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

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

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/

Report message to a moderator

how the query is working ? using Decode [message #376862 is a reply to message #376807]

Thu, 18 December 2008 22:52

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376874 is a reply to message #376862]

Fri, 19 December 2008 00:37

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

Report message to a moderator

Re: LIKE FUNCTIONALITY IN DECODE FUNCTION [message #376875 is a reply to message #376807]

Fri, 19 December 2008 00:40

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376876 is a reply to message #376862]

Fri, 19 December 2008 00:41

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376925 is a reply to message #376876]

Fri, 19 December 2008 03:21

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376930 is a reply to message #376925]

Fri, 19 December 2008 03:38

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376944 is a reply to message #376930]

Fri, 19 December 2008 04:13

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

it will work fine in inner query how ?...

Report message to a moderator

Re: how the query is working ? using Decode [message #376949 is a reply to message #376944]

Fri, 19 December 2008 04:19

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

Report message to a moderator

Re: how the query is working ? using Decode [message #376961 is a reply to message #376949]

Fri, 19 December 2008 04:37

panyam
Messages: 146
Registered: May 2008

Senior Member

Thnks a lot .... Razz

Report message to a moderator

Previous Topic:	utl_file
Next Topic:	GROUP BY clause problem (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

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