Home » SQL & PL/SQL » SQL & PL/SQL » decode with like operator (oracle 11g)
decode with like operator [message #589973] Fri, 12 July 2013 16:37 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.

select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id
from it a

given error as:
ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"




How to write this.

Thanks
Re: decode with like operator [message #589974 is a reply to message #589973] Fri, 12 July 2013 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: decode with like operator [message #589975 is a reply to message #589974] Fri, 12 July 2013 17:35 Go to previous messageGo to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
I already posted ddl for following query.I need to get create_user_id starting with 'IS%' for sale_location_id=1 because this will be having different user_id's.ALso the sale_location_id will be passed as a parameter later in from procedure ,so i had included
a.create_user_id like 'U%' in the decode stmt for other sale_location_id's
my output should be
--------------------

sale_location_id create_user_id
---------------- ----------------
1 ISRA
2 USFA

insert into it values(1,'FGPS')
1 rows inserted.


Thanks.
Re: decode with like operator [message #589976 is a reply to message #589975] Fri, 12 July 2013 20:47 Go to previous messageGo to next message
manubatham20
Messages: 478
Registered: September 2010
Location: Champaign, IL
Senior Member

Kidding me?

Have you ever heard usage of where clause?

Seriously tell us, how many hours before you learnt Oracle sql, we will help.

I need to get create_user_id starting with 'IS%' for sale_location_id=1
WHERE create_user_id like 'IS%' and sale_location_id=1

[Updated on: Fri, 12 July 2013 20:49]

Report message to a moderator

Re: decode with like operator [message #590000 is a reply to message #589976] Sat, 13 July 2013 04:57 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

You can do by using case statement.
WITH temp AS (SELECT 1 sale_location_id ,'ISRA' create_user_id FROM dual UNION ALL 
SELECT 2 sale_location_id ,'USFA' create_user_id FROM dual union all
SELECT 1 sale_location_id ,'FGPS' create_user_id FROM dual )
SELECT A.sale_location_id, CASE WHEN create_user_id LIKE 'IS%'
OR create_user_id LIKE 'U%' THEN create_user_id ELSE NULL END create_user_id
FROM temp A;
Re: decode with like operator [message #590042 is a reply to message #590000] Sat, 13 July 2013 13:08 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
If you don't know how to correctly format a query then learn it using SQL Formatter.

In addition, this is most likely wrong, see the previous answer.

Regards
Michel
Previous Topic: Query on invalid objects
Next Topic: Select updated rows from one table
Goto Forum:
  


Current Time: Mon Dec 22 14:04:41 CST 2014

Total time taken to generate the page: 0.14645 seconds