Home » SQL & PL/SQL » SQL & PL/SQL » Decode in the where clause
Decode in the where clause [message #300565] Sat, 16 February 2008 02:03 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,

I am stuck in a query

In the following query
I have a table hardware where i have a field 'predecessor' which is a self link to the Primary key 'ID' of hardware.
Here i display the hardware ID, the ID of the predecessor & the current user.
This returns me nothing if a hardware does not have a predecessor i.e that predecessor
is null.

SELECT hw1.ID, hw1.predecessor, u.Name
FROM hardware hw1, hardware hw2, users u
WHERE hw1.predecessor = hw2.ID
AND hw1.currentuser = u.ID


Can i write a DECODE statement which makes sure that this condition
hw1.predecessor = hw2.ID
is not checked if the hardware does not have predecessor & checks the same when there is a predecessor..

Please help me out in this....

Is there any other way other than decode...
Re: Decode in the where clause [message #300567 is a reply to message #300565] Sat, 16 February 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not to use decode (which can be used in where clause) but to use outer join.

Why newbies do not post in newbie forum instead of expert one?
Why newbies do not read the OraFAQ Forum Guide?

Regards
Michel

[Updated on: Sat, 16 February 2008 09:25]

Report message to a moderator

Re: Decode in the where clause [message #300577 is a reply to message #300567] Sat, 16 February 2008 04:21 Go to previous message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Thanks Michel,

Also Sorry at the same time, for posting the query a the expert forum,

I have used Left Outer join and it has given me the right result.
I'll also go to the ORAFAQ Forum Guide & get back to you,
Smile

Previous Topic: Using DBMS_REDEFINITION.START_REDEF_TABLE I get SP2-0027: Input is too long
Next Topic: Grant
Goto Forum:
  


Current Time: Sat Dec 10 05:02:07 CST 2016

Total time taken to generate the page: 0.04757 seconds