Home » SQL & PL/SQL » SQL & PL/SQL » using nvl in decode function
using nvl in decode function [message #387387] Thu, 19 February 2009 01:45 Go to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
hi
im using decode fn and nvl in order by clause.
but im not getting correct o/p if i give the query like this.
if i give order by as custmer name,num n status im getting correct o/p.
if i dont select any thing as my query it should print accourding to cust name but its not printing like that so please help me

ORDER BY DECODE(nvl(UPPER('&&order_by'),party.party_name),'CUSTOMER NAME',PARTY.PARTY_NAME,'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,'STATUS',LOOKUP.MEANING)
Re: using nvl in decode function [message #387404 is a reply to message #387387] Thu, 19 February 2009 02:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd help if you formatted your decode statement.

I just did and got this:

1 ORDER BY DECODE(nvl(UPPER('&&order_by'),party.party_name),
2                 'CUSTOMER NAME',
3                 PARTY.PARTY_NAME,
4                 'CUSTOMER NUMBER',
5                 CUST_ACC.ACCOUNT_NUMBER,
6                 'STATUS',
7                 LOOKUP.MEANING) 


Looking at it like that tells me your DECODE is missing a default value.

In future when posting can you please follow the posting guidelines found in the orafaq forum guide: http://www.orafaq.com/forum/t/88153/0/

Other than that - if you're on Oracle 9i or higher I strongly recommend you use the CASE statement for this rather than DECODE, it's a lot easier to use.

EDIT: typo

[Updated on: Thu, 19 February 2009 02:12]

Report message to a moderator

Re: using nvl in decode function [message #387405 is a reply to message #387387] Thu, 19 February 2009 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"fn"? "o/p"? don't use IM speak.

Regards
Michel
Re: using nvl in decode function [message #387423 is a reply to message #387405] Thu, 19 February 2009 03:45 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
im formatting my decode statement:

ORDER BY
DECODE(nvl(UPPER('&&order_by'),party.party_name),
'CUSTOMER NAME',PARTY.PARTY_NAME,
'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,
'STATUS',LOOKUP.MEANING))

ITS WORKING CORRECTLY WHEN I SELECT CUSTNAME,NUMBER OR STATUS BUT IF I DONT SELECT ANY OPTION IT MUST PRINT ACCORDING TO CUSTNAME.
THE NVL FUNCTION IS NOT WORKING CORRECTLY please HELP ME IS THERE ANY WRONG IN MY DECODE STATEMENT.
Re: using nvl in decode function [message #387429 is a reply to message #387387] Thu, 19 February 2009 03:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:
Looking at it like that tells me your DECODE is missing a default value.




And use code tags!
And don't post in caps!!

[Updated on: Thu, 19 February 2009 04:00]

Report message to a moderator

Re: using nvl in decode function [message #387432 is a reply to message #387423] Thu, 19 February 2009 04:02 Go to previous messageGo to next message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
I HAD SOLVED IT.
THE CORRECT STATEMENT IS:
ORDER BY
DECODE(UPPER('&&order_by'),'CUSTOMERNAME',PARTY.PARTY_NAME,
'CUSTOMER NUMBER',CUST_ACC.ACCOUNT_NUMBER,
'STATUS',LOOKUP.MEANING, PARTY.PARTY_NAME)
Re: using nvl in decode function [message #387434 is a reply to message #387387] Thu, 19 February 2009 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Glad to here it.
Now can you Please, PLEASE read the orafaq forum guide and follow it's instructions for next time you post:

http://www.orafaq.com/forum/t/88153/0/

Posting in capital letters is considered shouting. Don't do it, it annoys people.
Re: using nvl in decode function [message #387546 is a reply to message #387434] Thu, 19 February 2009 19:50 Go to previous message
radhavijaym
Messages: 65
Registered: December 2008
Location: singapore
Member
sorry ,
i didnt see your reply and i posted my reply.
next time i wont use caps.
Previous Topic: Storage capacity of NUMBER data-type
Next Topic: Creating External table Using remote system folder
Goto Forum:
  


Current Time: Thu Mar 28 16:19:57 CDT 2024