Home » SQL & PL/SQL » SQL & PL/SQL » Outer join on a decode value (10g)
Outer join on a decode value [message #402425] Sun, 10 May 2009 04:47 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Im trying to do an outer join but getting the following error

ORA-00936: missing expression


Below is my test case

create table dim_warranty (
   id number,
   name varchar2(100),
   description varchar2 (100))
   
insert into dim_warranty values (1,'Y','Warranty still applicable');
insert into dim_warranty values (2,'N','Warranty not applicable');
insert into dim_warranty values (3,'U','Unknown');


create table source_item(
  id number,
  item varchar2(100),
  warranty varchar2(100));
  
insert into source_item VALUES (1,'M20190','Yes');
insert into source_item VALUES (2,'M20191','No');
insert into source_item VALUES (3,'M20192','Yes');
insert into source_item VALUES (4,'M20193','Yes');


COMMIT;


When i issue the following statement, im getting the error above

SELECT a.id, a.item, b.id FROM 
       source_item a, dim_warranty b
       where a.WARRANTY = decode (b.name,'Y','Yes','N','No',NULL) (+)



Actually im trying to form a query to get id of warranty field from dim_warranty. But since the dimension table stores 'Y' or 'N' and source is storing 'Yes' or 'No', i have to decode to get the id,

But if i change my query to this, it works, im not sure why.


SELECT a.id, a.item, b.id FROM 
       source_item a, dim_warranty b
       where decode(a.WARRANTY,'Yes','Y','No','N',NULL) = b.name (+)




And sometimes i do have problems also where source system stores values in upper case and the dimension tables stores values in lowercase. So when im doing an outer join, i need to make sure both are in same case, so when i issue following command i get same error

SELECT a.id, a.item, b.id FROM 
       source_item a, dim_warranty b
       where upper(a.WARRANTY) = upper(b.name) (+)

[Updated on: Sun, 10 May 2009 05:04]

Report message to a moderator

Re: Outer join on a decode value [message #402426 is a reply to message #402425] Sun, 10 May 2009 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(+) should be close the column name.
SELECT a.id, a.item, b.id FROM 
       source_item a, dim_warranty b
       where a.WARRANTY = decode (b.name(+),'Y','Yes','N','No',NULL)


Regards
Michel
Re: Outer join on a decode value [message #402436 is a reply to message #402426] Sun, 10 May 2009 07:35 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Thank you michel, appreciate it
Re: Outer join on a decode value [message #402441 is a reply to message #402436] Sun, 10 May 2009 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for providing the test case (don't forget ';' on create table), this has allowed me to check that my answer was correct.

Regards
Michel
Re: Outer join on a decode value [message #402449 is a reply to message #402441] Sun, 10 May 2009 11:06 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
yeah missed that out. good day
Previous Topic: Getting a letter after a space
Next Topic: parameter for creating materialized view
Goto Forum:
  


Current Time: Sun Dec 04 10:39:34 CST 2016

Total time taken to generate the page: 0.07985 seconds